Forum Discussion
filter a table based on a list [UPDATED]
I have a table of data. Each row/record has a unique identifier.
I have a separate list of unique identifiers that is a subset of those in the full table.
I want to see the data for only the records on my list.
All the data, i.e. every column, regardless of how many columns there are.
Here's an oversimplified example:
I have this table of data:
| Column A | ColB | ColC | [...] | ColZ |
| Christopher | dataB2 | dataC2 | [...] | dataZ2 |
| Bob | dataB3 | dataC3 | [...] | dataZ3 |
| Alice | dataB4 | dataC4 | [...] | dataZ4 |
| David | dataB5 | dataC5 | [...] | dataZ5 |
| Evelyn | dataB6 | dataC6 | [...] | dataZ6 |
I have this list:
| David |
| Bob |
Evelyn |
Desired end result is:
| Column A | ColB | ColC | [...] | ColZ |
| Bob | dataB3 | dataC3 | [...] | dataZ3 |
| David | dataB5 | dataC5 | [...] | dataZ5 |
| Evelyn | dataB6 | dataC6 | [...] | dataZ6 |
Except I need a couple hundred records out of a few thousand. Essentially, out of these zillions of records, show me only the ones on this list.
Current approach is manual brute force; add the target list as new rows to the original table, apply conditional formatting to that column to highlight those identifiers that are now duplicated, filtering that column on that format, and then filtering on a data column to exclude blank cells. Not particularly elegant.
Other hackey solutions we've come up with:
- manually choosing the records I want from the drop-down filter menu on the column header - not practical with a lot of records.
- creating a helper column to filter has the same problem with high volumes - each record has to be located and interacted with directly.
- Doing a vlookup to specify each column individually; inefficient with lots of columns, plus it can't adapt to a change in the number of columns in the table.
What obvious answer am I overlooking?
[Updated to add example / for clarity]
14 Replies
I'd add a helper column with formulas:
The formula in AA2 is =ISNUMBER(MATCH(A2,$A$9:$A$11,0)) where A9:A11 is the list of unique identifiers.
You can then filter for TRUE:
- Helper columns were also considered, it just seems... kludgly. It's a pretty basic data query - find the records that match and display that data. There's got to be a way to return an undefined amount of data (columns) aside from manually choosing the values in the filter dropdown.
An alternative could be Power Query. In the attached file you can add data in the large blue dynamic table. In the small blue dynamic table you can enter the names you want to filter. Then click in any cell of the green table and right-click with the mouse. Then select refresh.
The layout in the screenshot is for illustration. The small blue table and the green table can be dragged to columns AB and AD:BC for example. The large blue table can then be expanded to thousands of rows.
