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...
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.
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.
- That's what I'm looking for, beautiful! Now to see if all my coworkers have UTD versions of Office...
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.
- 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?
