Filtering multiple columns

[RajGrewal]

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?

[#2]

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

[#3]

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

[#4]

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

[#5]

Yes that’s very helpful! I really appreciate it. I’m new and my boss gave me this so I am struggling. I would like a good way to design and set it up too. I will include the file in this with the names also if you don’t mind showing me how do that. I really appreciate the help in helping me learn. That’s the full actual sheet

[#6 · Accepted solution]

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

[#7]

Okay thanks, sorry will do

[#8]

@RajGrewal 

I've added a column for the names --- you can just copy and paste them in on the data base. The formula has been adjusted to accommodate the additional data.

[#9]

@mathetes thank you so much! really appreciate the help

[#10]

Hello,

I've removed all attachments due to raised security & GDPR concerns. Thank you.

[#11]

Just found at that at my workplace they are still using excel 2007.. smh so that filter function didn’t work there. Thanks for the help either way, appreciate it.