filter a table based on a list [UPDATED]

[JC Reardon]

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]

[#2]

@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:

[#3]

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.

[#4]

@JC Reardon 

Would it be OK to return the filtered records to another range?

[#5]

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.

[#6]

You could use the provided solution in conditional formatting and then filter by color. The helper column wouldn't be needed.

[#7]

@Patrick2788 

Also a good idea.  Is there a way to allow a conditional statement like that dynamically adjust for a shorter or longer list?  

A formula that relatively references each list item would solve that issue, but the best I can think of is a VLOOKUP, but that's just going to return one data value per record, when I want to get all of them.

[#8]

@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.

[#9]

@JC Reardon 

If you have Microsoft 365 or Office 2021, the formula would be

=FILTER(A2:Z6,ISNUMBER(MATCH(A2:A6,A9:A11,0)))

You only need to enter this in one cell; it will spill to as many rows and columns as needed.

[#10]

That's what I'm looking for, beautiful! Now to see if all my coworkers have UTD versions of Office...

[#11]

Exactly what I had in mind, thanks. Will this work with older / non-365 versions of Office?

[#12]

@JC Reardon 

Attached are the filter in place solutions. The TAKE workbook is for 365 and the OFFSET for legacy. Looks like you have some good options from everyone.

[#13]

@JC Reardon 

You are welcome. I have Excel 2013 and Power Query works with this version.

[#14]

I think I'm missing a step in the middle somewhere, I can see you're calculating the 'Data' range via Name Manager, but when I look at the Conditional Formatting through the Rules Manager, the "applies to" field is an absolute reference to the data range as it currently exists (=$A$7:$Z$12).

I've had problems in the past trying to use relative or named references in Conditional Formatting; sometimes addition/insertion/movement of data results in duplicated rules with absolute references to subsets of the data, sometimes one rule will be applied to a long list of smaller ranges with gaps in the middle that don't necessarily correspond with the recently introduced data. What's the secret to getting Conditional Formatting to respect a named range/cell/table, or does it only work once, and therefore the CF needs to be applied as the very last step?

[#15]

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!