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...
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?
You are welcome. I have Excel 2013 and Power Query works with this version.
- Good to know! I haven't played with PQ as much as I'd like, mainly because I hadn't realized it was compatible that far back. That certainly opens some possibilities for putting it to use!