Filtering One Condition to Multiple Columns on a Drop Down List

pineapple61

New Member
Joined
Jan 31, 2023
Messages
7
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
  2. MacOS
Hi everyone,

I have three columns A, B, and C and I want to filter the rows that pertain to one of the drop-down list selections that I created, what would be the formula for this?

For example, I have listed the first, second and third job preferences of a number of people. I would like to see the names of the people that want to be a "doctor", no matter if it's their first, second or third preference. I have named my table "Work" to make it easier to filter and used the formula: =FILTER(Work, Work[First Job]=G1) - See image.

Now that I have filtered this and by using the selection list that I have, I can see the name of those that have Doctor as their first preference but I don't know how to add everyone else that has "Doctor" in their second and third job preference. What would be the formula for this?

I tried to do this next but it did not work for me, =FILTER(Work, Work[First Job]=G1)*(Work[Second Job]=G1)
 

Attachments

  • Screen Shot 2023-01-31 at 8.06.31 PM.png
    Screen Shot 2023-01-31 at 8.06.31 PM.png
    32.5 KB · Views: 14

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Thank you for including your version details in your profile and welcome to the Forum.

See if this works for you.

20230201 VStack Filter pineapple61.xlsx
ABCDEFGHI
1NameFirst JobSecond JobThird JobDoctor
2JohnDoctorPoliceMechanicNameFirst JobSecond JobThird Job
3MarcusFiremanSalesmanWaiterJohnDoctorPoliceMechanic
4KyleDoctorEngineerWaiterKyleDoctorEngineerWaiter
5AlexaEngineerDoctorDoctorBritneyDoctorMechanicWaitress
6BritneyDoctorMechanicWaitressAlexaEngineerDoctorDoctor
7FergusonPoliceDoctorEngineerFergusonPoliceDoctorEngineer
Data
Cell Formulas
RangeFormula
F3:I7F3=UNIQUE(VSTACK(FILTER($A$2:$D$7,$B$2:$B$7=G1,""),FILTER($A$2:$D$7,$C$2:$C$7=G1,""),FILTER($A$2:$D$7,$D$2:$D$7=G1,"")))
Dynamic array formulas.
 
Last edited:
Upvote 0
Solution
Thank you for including your version details in your profile and welcome to the Forum.

See if this works for you.

20230201 VStack Filter pineapple61.xlsx
ABCDEFGHI
1NameFirst JobSecond JobThird JobDoctor
2JohnDoctorPoliceMechanicNameFirst JobSecond JobThird Job
3MarcusFiremanSalesmanWaiterJohnDoctorPoliceMechanic
4KyleDoctorEngineerWaiterKyleDoctorEngineerWaiter
5AlexaEngineerDoctorDoctorBritneyDoctorMechanicWaitress
6BritneyDoctorMechanicWaitressAlexaEngineerDoctorDoctor
7FergusonPoliceDoctorEngineerFergusonPoliceDoctorEngineer
Data
Cell Formulas
RangeFormula
F3:I7F3=UNIQUE(VSTACK(FILTER($A$2:$D$7,$B$2:$B$7=G1,""),FILTER($A$2:$D$7,$C$2:$C$7=G1,""),FILTER($A$2:$D$7,$D$2:$D$7=G1,"")))
Dynamic array formulas.

Thank you for including your version details in your profile and welcome to the Forum.

See if this works for you.

20230201 VStack Filter pineapple61.xlsx
ABCDEFGHI
1NameFirst JobSecond JobThird JobDoctor
2JohnDoctorPoliceMechanicNameFirst JobSecond JobThird Job
3MarcusFiremanSalesmanWaiterJohnDoctorPoliceMechanic
4KyleDoctorEngineerWaiterKyleDoctorEngineerWaiter
5AlexaEngineerDoctorDoctorBritneyDoctorMechanicWaitress
6BritneyDoctorMechanicWaitressAlexaEngineerDoctorDoctor
7FergusonPoliceDoctorEngineerFergusonPoliceDoctorEngineer
Data
Cell Formulas
RangeFormula
F3:I7F3=UNIQUE(VSTACK(FILTER($A$2:$D$7,$B$2:$B$7=G1,""),FILTER($A$2:$D$7,$C$2:$C$7=G1,""),FILTER($A$2:$D$7,$D$2:$D$7=G1,"")))
Dynamic array formulas.
Wow, thanks! This is amazing, I've been struggling so much to get here. I have a quick question. If I select another job, e.g., "Mechanic", why are there cells that appear #N/A?
 

Attachments

  • Screen Shot 2023-02-01 at 1.18.24 PM.png
    Screen Shot 2023-02-01 at 1.18.24 PM.png
    31.2 KB · Views: 9
Upvote 0
You can get rid of the NAs by including the IFNA or IFERROR function but unfortunately that left you with a blank row.
So it morphed into the below which should work as long as you don't put any numbers in Column 1 / A.

Excel Formula:
=LET(
fltrResult1,FILTER($A$2:$D$7, $B$2:$B$7=G1, 1),
fltrResult2,FILTER($A$2:$D$7, $C$2:$C$7=G1, 1),
fltrResult3,FILTER($A$2:$D$7, $D$2:$D$7=G1, 1),
vsResult,UNIQUE(VSTACK( fltrResult1, fltrResult2, fltrResult3 )),
vsResultCntThe1s,COUNT(CHOOSECOLS( vsResult, 1 )),
SORT(DROP( vsResult,  vsResultCntThe1s ), 1))
 
Upvote 1
Awesome, it worked! One last question, I promise. This has helped me a lot, what would be the formula for the Microsoft Office Professional Plus 2016 version? Apparently, the FILTER option is not valid for that version, I think?
 
Upvote 0
Can you ask that in a new thread. I don't know how to do it without using the dynamic array functions and you are more likely that someone fresh will look at it in a new thread. Just make it clear that it is question about converting the above to 2016 in the subject line and question, so that it is not viewed as being the same question duplicated.

Can you instal XL2BB ? You will more likely get a response if you provide an XL2BB so that helpers don't need to manually set up test data.
If you can't either paste some data in table form into your question or if need be provide a link to my post #2

XL2BB
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Thanks for all the info, you have no idea how much I appreciate it!
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top