Okay so I have a question. Pretty much if you look at the file attached, what I am trying to accomplish is that when you select a certain job position to filter, I want that same job position that’s also in other columns to appear too and not just the one in the first selected column. Anyway I can make it work? What kinda code can I use if there is one?

Okay so I have a question. Pretty much if you look at the file attached, what I am trying to accomplish is that when you select a certain job position to filter, I want that same job position that’s also in other columns to appear too and not just the one in the first selected column. Anyway I can make it work? What kinda code can I use if there is one?

RajGrewal 

Is it possible for you to post the actual spreadsheet rather than an image? Otherwise you're asking us to create our own....just remove any names (if those are what's in Column A) or any other confidential info.

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

RajGrewal 

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.

RajGrewal 

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.