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 recor...
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.
- Exactly what I had in mind, thanks. Will this work with older / non-365 versions of Office?
Would it be OK to return the filtered records to another range?
- A new range would be optimal, yes. Your solution with the helper rows is certainly much better than what we are doing now but it would be great if we could achieve the same result without having to mess with the original table.
