Forum Discussion

JC Reardon's avatar
JC Reardon
Brass Contributor
Dec 14, 2022

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 AColBColC[...]ColZ
ChristopherdataB2dataC2[...]dataZ2
Bob

dataB3

dataC3[...]dataZ3
AlicedataB4dataC4[...]dataZ4
DaviddataB5dataC5[...]dataZ5
EvelyndataB6dataC6[...]dataZ6

 

 

I have this list:

David
Bob

Evelyn

 

 

Desired end result is:

Column AColBColC[...]ColZ
Bob

dataB3

dataC3[...]dataZ3
DaviddataB5dataC5[...]dataZ5
EvelyndataB6dataC6[...]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]

 

  • JC Reardon 

    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:

    • JC Reardon's avatar
      JC Reardon
      Brass Contributor
      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.
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        JC Reardon 

        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.

Resources