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

Question

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?

Answer 1

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).

Answer 2

Obviously, this is an old question but it showed up for me and it's something I've occasionally wanted to do so I thought I'd post a belated answer.

It anticipates that document 1 will have two tables on sheet 1, and that table 2's size can encompass the copied data. If the key column is of a special format (e.g. date) then it won't work as dates require special handling so change its format to text. And obviously, it has no error handling.

tell application "Numbers"
    tell table 1 of sheet 1 of document 1
        -- because of the indecent and desperate need for Numbers to wrap text
        set text wrap of cell range to false 

        set kc to column "C" -- key column
        set rp to (cells of kc whose value contains "September") -- matching rows
        set hr to {} -- Get headings to copy to table 2
        copy value of cells of row 1 to hr
        set db to {} -- get cell values from matching rows
        repeat with rs in rp
            copy value of cells of row of rs to end of db
        end repeat
        -- each row's data will appear like this: {3.0, "Sun", "September 24", "W", "Denver Broncos"}
    end tell
        -- set db to rest of db -- If heading row contains match then uncomment

    tell table 2 of sheet 1 of document 1
        clear cell range
        set rc to count of db -- row count
        set cc to count of item 1 of db -- column count
        repeat with y4 from 1 to rc -- for every row
            repeat with x8 from 1 to cc -- for every column
                set value of cell x8 of row (y4 + 1) to item x8 of item y4 of db
            end repeat
        end repeat
        tell row 1 -- Set table headings to match table 1
            repeat with j from 1 to count of columns
                set value of cell j to item j of hr
            end repeat
        end tell
        set text wrap of cell range to false -- see above
    end tell
end tell

---

This is not part of the answer but loads sample data into table 1 (min 5x5)

tell application "Numbers"
    tell table 1 of sheet 1 of document 1
        set column count to 5
        set row count to 5
        set ev to {{"Week", "Day", "Date", "WL", "Opponent"}, {2.0, "Sun", "September 17", "L", "Carolina Panthers"}, {3.0, "Sun", "September 24", "W", "Denver Broncos"}, {4.0, "Sun", "October 1", "W", "Atlanta Falcons"}, {5.0, "Sun", "October 8", "L", "Cincinnati Bengals"}}
        set evy to count of ev
        set evx to count of item evy of ev
        repeat with y from 1 to (evy)
            repeat with x from 1 to evx
                set value of cell x of row y to item x of item (y) of ev
            end repeat
        end repeat
    set format of column "C" to text -- date format requires special handling
    set text wrap of cell range to false
    end tell
end tell

Note on date format: You can't compare a string to the month of a date object. You could probably manufacture a method by breaking down the date cells: set x to value of cell "C2", set y to month of x, set z to x as text but it should be easier to convert to text, run the script normally and if necessary, convert back to date.