Excluding specific cell values (names) from a list
Hello Experts,
In the following sheet (Google Sheet - but the solution is likely the same for Excel), I am sorting a list of unique names (column H) that are extracted from 3 columns (B, N, and P).
But I'd like to have the ability to exclude specific names that are listed in column (J), the exclude list. How can I accomplish that?
Please see the tab (Auto-populate from list):
https://docs.google.com/sp
Thank you. :)
In the following sheet (Google Sheet - but the solution is likely the same for Excel), I am sorting a list of unique names (column H) that are extracted from 3 columns (B, N, and P).
But I'd like to have the ability to exclude specific names that are listed in column (J), the exclude list. How can I accomplish that?
Please see the tab (Auto-populate from list):
https://docs.google.com/sp
Thank you. :)
Have you tried replacing H3:H50 in your new formula with the original formula?
That might work, and if it does you wouldn't need the helper column but you would end up with a pretty complicated formula.
That might work, and if it does you wouldn't need the helper column but you would end up with a pretty complicated formula.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Norie,
> Have you tried replacing H3:H50 in your new formula with the original formula?
I tried that but I couldn't make it work. :( I don't mind complicated formulas... they are preferred over helper columns. :)
Hi Tom,
>Look at column G
Your formula is just another way to arrive at the same results as my column L. But I like your simpler formula, even if you are still depending on column H.
*BUT*
Then I realized that I could embed my column H formula with your formula. Thus eliminating column H, as I requested. Yes, I am grateful! Kudos to you!
So...
=filter(Sort(UNIQUE({B3:B22;O3:O18;Q3:Q7}),1,true),countif(I3:I,UNIQUE({B3:B22;O3:O18;Q3:Q7}))=0)
Works.
> Have you tried replacing H3:H50 in your new formula with the original formula?
I tried that but I couldn't make it work. :( I don't mind complicated formulas... they are preferred over helper columns. :)
Hi Tom,
>Look at column G
Your formula is just another way to arrive at the same results as my column L. But I like your simpler formula, even if you are still depending on column H.
*BUT*
Then I realized that I could embed my column H formula with your formula. Thus eliminating column H, as I requested. Yes, I am grateful! Kudos to you!
So...
=filter(Sort(UNIQUE({B3:B22;O3:O18;Q3:Q7}),1,true),countif(I3:I,UNIQUE({B3:B22;O3:O18;Q3:Q7}))=0)
Works.
Nice build, Doug....
ASKER
I solved my own problem... but not an ideal solution.
By making column (H) a helper column, I created a new final result column (L) with the formula:
=query(H3:H50," select * where NOT H matches '"&JOIN("|", J3:J22)&"' ")
This works! :)
But if someone more clever than me can find a way to accomplish the same results without a helper column, I will be grateful!
To recap, the helper column (H) combines, sorts, and lists all the unique cells in columns (B, P, R) using the formula:
=Sort(UNIQUE({B3:B22;P3:P18;R3:R7}),1,true)
Then column (L), compares column (H) against an exclusion list and lists out only names not included in the exclude list column (J).
Thanks