How do I copy rows based on single cell value in Google Sheets?

Question

I have two sheets in Excel, which is duplicated on Google Sheets. Sheet 1 is a list of names with various bits of information for example

Name                  Number     Outcome
Colin Smith           12345      Booked
Brian Davis           12346      No answer
Jimbo Jones           12347      Booked
Weyland Smithers      12348      Booked
Meg Griffin           12349      No answer

Sheet 2 is a duplicate of all names with the outcome 'No answer',

Name                  Number     Outcome
Brian Davis           12346      No answer
Meg Griffin           12349      No answer

At the moment I have to regularly filter Sheet 1 to find all the 'No answer' results and copy them manually. Is there a way to set up Sheet2 so any 'No answer' rows are automatically added, then also removed if changed.

I am trying to do this without using macros as this document is duplicated in Google Docs.

How can I accomplish this?

Answer 1

I tried a few things and ended up with a working solution, but it requires a helper column in your first sheet. I'm going to assume that your example data is in columns A:C of Sheet1, and they have to go into columns A:C in Sheet2.

The helper column goes into column D of Sheet1, and merely counts the occurrences of the "No answer" values. So from D2 and onwards:

=COUNTIF(C$2:C2,"No answer")

For your example data, this will show values 0, 1, 1, 1, 2 across the first 5 rows of data.

Then in Sheet2 you can look up the values based on new values in the helper column. Put this in A2 of Sheet2 and copy it across all columns and as many rows as you need:

=IFERROR(INDEX(Sheet1!A:A,MATCH(ROW()-1,Sheet1!$D:$D,0)),"")

Test it without the IFERROR first, since this catches all errors and not just NA's.