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?

  • Do you ant to copy a row or rows by number, or do some sort of search? That second sentence threw me off. – krs013 Oct 7 '14 at 15:48
  • Any row that has the phrase "john doe" in column G. – Josh Oct 7 '14 at 16:02

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

  • I can't seem to get VLOOKUP to do what I want, but it's really close. But the filter is a fantastic suggestion, it just doesn't contain the automation like you mentioned. If I can't find a good alternative, this'll be what I have to do. – Josh Oct 7 '14 at 18:23
  • It seemed like it might work, but it can't handle multiple matches. If you figure it out, definitely post up an answer of your own—I'm curious how it would work. – krs013 Oct 7 '14 at 18:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .