How to set Numbers to automatically copy a row of data from one sheet to another sheet

iworknumbers

I have a Numbers spreadsheet that has years worth of data in it. I'd like to call out specific rows in the spreadsheet and copy just those rows into a new sheet in the same file where I'd like the data collected. The rows I want called out all contain two specific words in the same column.

So, ideally, I'd like a function that I can match a search for "john doe" in sheet 1-table 1-column G, select the whole row of data, copy it, and paste it into sheet 2.

What function, or several functions, can use to accomplish this?

Best Answer

I don't know if it's possible to do this with functions, but it might be. There is a function called VLOOKUP that seems like it would do what you want, but I couldn't get it to quite work in my experimenting with it—a function that exists within one cell has a hard time returning multiple values for multiple cells.

What I think does work, though perhaps not as convenient as you might want, is the filter view. On the right side of the window, if you select the view that says "Sort & Filter" and the Filter tab, you can "Add a Filter" that will match a value in a specified column. Toggling that filter using the checkbox in the corner of that Filter's box will hide all of the rows that don't match that criteria, then it's a simple matter of highlighting all of the cells, copying and pasting to another sheet.

Unfortunately, this won't continue to add rows as you add rows in the first sheet, so if that functionality is something you need, this won't answer your question. If you only need a one-time copy, though, I think that this is the easiest option (and the only one that I know of, but I'm not extremely familiar with Numbers).

Related Solutions

How to sort a Numbers’ table using data from another table

Thanks to your sample file, I think I was able to do what you wished.

Using an additional column to bring in the numbers corresponding to the codes (16AA, 1; 16AE, 2...) sorting by that column then hiding it the desired result was accomplished. To refresh the sort after adding data, unhide the column and sort again.

Here are the steps:

Create new column to the left of the "Code" column.
Add a formula to extract the proper number for each code and copy down.
Formula: =INDEX (Group::c, MATCH (B, Group::A))

(Using INDEX(MATCH) is a good way to get column data with out worrying about extra columns being added as is the case with VLOOKUP.)

Copy the formula down, sort the column and hide it if desired.

Result:

Getting the First Cell NOT Containing Certain Text in Numbers

As it turns out, I've figured it out. It did require Applescript. What I did was loop through the rows and selected the values in the row where "not" was not present in column B. Here's the Applescript I used via Automator:

on run {input, parameters}

    tell application "Numbers"
        tell table "Calculator 1" of sheet 2 of document of front window
            set aRange to "A2:A22"
            set bRange to "B2:B22"
            set aRows to the rows of range aRange
            set bRows to the rows of range bRange

            set correctRow to -1
            set correctCol to -1

            repeat with i from 1 to count of bRows
                set aCell to "A" & (i + 1)
                set bCell to "B" & (i + 1)

                if value of (cell bCell) is not "not" then
                    set correctRow to value of (cell aCell)
                    set correctCol to value of (cell bCell)
                end if
            end repeat

            set the value of (cell "C2") to correctRow
            set the value of (cell "C4") to correctCol
        end tell

        display dialog "Updated!"
    end tell

    return input
end run

Keep in mind that the cells I used are specific to my spreadsheet, but generally this method could be applied for any 2D MATCH() function.

Create a table has the name of the each row in each row of the header column, and use the desired MATCH() function per row in this way (see the picture of the table in my question).
Use this Applescript to loop through the MATCH() functions and find the correct row and column. Change "C2" to whichever cell will be your Correct Row and "C4" to whichever cell will be your Correct Column.
Use the INDEX() function to get the value of the cell and put it into another cell, using the coordinates the Applescript calculated for you.

Best Answer

Related Solutions

How to sort a Numbers’ table using data from another table

Getting the First Cell NOT Containing Certain Text in Numbers

Related Question