08-17-2020 09:50 AM - last edited on 08-18-2020 10:26 AM by Dylan Snodgrass
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?
08-17-2020 01:49 PM
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;
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.
08-17-2020 02:31 PM - edited 08-17-2020 03:03 PM
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
08-17-2020 02:49 PMSolution
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.
08-19-2020 08:59 AM