Forum Discussion
Filtering multiple columns
Take that sheet with actual names down. It's a violation of the rules here to post real info...privacy rules.
All you need to do to use what I gave you is modify the array that gets filtered, along with the columns specifying the criteria. I can't do it now, but will look later this evening.
Yes here is the actual file without names, if there is any other way to set it up, I would appreciate that too. Thanks for looking into it
Here's the result. You do need to have the most recent release of Excel in order for this to function. It makes use of the FILTER function, with a long set of criteria, given the number of columns you have. The key to accomplishing what you want is the "+" sign between each of the criterion statements. It translates to Excel as "or".
Attached is an example of how you can work with the data arrayed as you have it currently. There's a single formula in cell A7 of the newly created "Filter" worksheet. On the "DataValTable" sheet, I created a list of unique position titles, to use in the selection drop down on the Filter worksheet. You select the position title you want to search for in cell A3, and it becomes the basis for looking at every column that contains position titles. The FILTER function is a long one;
=FILTER(Database!A2:AP140,
(Database!A2:A140=$A$3)
+(Database!G2:G140=$A$3)
+(Database!M2:M140=$A$3)
+(Database!S2:S140=$A$3)
+(Database!Y2:Y140=$A$3)
+(Database!AE2:AE140=$A$3)
+(Database!AK2:AK140=$A$3)
)
I'm not sure whether indeed names were in your original, and if you use the yellow rows to differentiate between individuals. There would be better ways to do this, from the point of view of database design. You have a lot of empty cells, meaning a lot of wasted space.
So depending on where you're going with this, we could talk about better design.