I am trying to create a new worksheet in a Google Sheets doc where I need to filter a Master data set based on values in a specific column.
I originally did a VLOOKUP in each specific column that I wanted returned but it brough back blank rows for the corresponding rows that did not match, and eventually it broke for some reason and stopped returning the correct data.
I have been trying to find a new way to do it and have most recently been trying XLOOKUP, but it returns duplicate rows of each correct match.
The original datasheet is mostly populated itself by VLOOKUPS, drawing in information from other trackers so I feel this must be why it is falling over.
The dataset looks roughly like this:
| ID | Y/N | Last Name | First Name | col | col1 | col2 | etc |
|---|---|---|---|---|---|---|---|
| 1 | Y | SMITH | A | Cell 1 | Cell 2 | Cell 1 | Cell 2 |
| 2 | N | JONES | B | Cell 1 | Cell 2 | Cell 1 | Cell 2 |
There are 40 columns in the tracker but I basically want to have a new sheet where I filter and return only the 'Y' values in the Y/N column, and return columns A:N (including the ID column) without leaving blank rows between the matches, and without returning dupllicates.
I have tried everything I can think of but I just cannot make it work, so any help on the matter would be GREATLY appreciated.
Thanks for reading.
CodePudding user response：
try:
=FILTER(Sheet1!A:N; Sheet1!B:B="Y")
CodePudding user response：
You can also try along with player0 answer, if you want also to import the headers:
=Query(Sheet1!A:N,"Select * Where B = 'Y'"),TRUE)