2

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?

  • Please be careful with terminology and when tagging. Excel is not Google Spreadsheets. They are similar, but are two different applications. Therefore, the answers will likely be different. – CharlieRB Dec 16 '14 at 12:39
  • The way I read it he needs a solution that works in both Microsoft Excel and Google Sheets. The Excel file is automatically copied to Google Sheets, but he also works on it locally using Excel. – freekvd Dec 17 '14 at 8:17
2

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.

  • Hi this works great thank you, however it seems to pull in all the rows with the No Answer outcome except the very first instance. So the first time No Answer is found its ignored, and only subsequent instances are duplicated. – Tim Wilkinson Dec 18 '14 at 10:24
  • Scrap that comment, the duplicate sheet started a row lower than the source sheet. Deleted the extra row and works perfectly thanks a lot! – Tim Wilkinson Dec 18 '14 at 10:54
  • 1
    A cleaner alternative would be to replace ROW()-1 with ROW(A2)-ROW(A$1) where A2 is the current cell and A$1 is the column header. – freekvd Dec 18 '14 at 11:05

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.