Excel Table - Filtering on Unique Values in specific column

I have a table with 5 columns (A to E).
I wish to filter based on criteria of unique values in Column D (which is by the way a concatenation of Columns A & B)
Im trying to use Advanced Filter with little success.
I assume the List range is the whole table, and I select the box unique records only.  But what do I put in criteria range?
Or should I be doing it differently?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Could you send a dummy?

you could use for all unique values non empty


or as alternative you could use Data / Data Tools / Remove Duplicates
Andy CownieImplementation SpecialistCommented:
If you just want a unique list of the values, you can use remove duplicates, or for more detail I'd use a pivot table.

Just click within your range of data, and click on "Insert Pivot table" and click yes on the prompts. Then once your pivot-table appears, drag column D's heading to the bottom left box (of the 4 boxes in the pivot table menu that pops up on the right of your screen) and you will see a unique list of values. You can then also count how many times this value appears, do calculations on other columns (sum/average etc) and much more.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Based on what you say, in order to have criteria for a specific table, add a couple of rows above your table to make the criteria range.  Note that if you want an AND or OR be sure to have the values on separate rows (for an OR) and repeat criteria for AND in all rows
Check the example I've attached.  Hope this helps!

Good explanation here

LMK if you want to work on a sample of your own
gh_userAuthor Commented:
Hi.  I thought I would provide some more information.
Firstly, I am wishing to filter my table so it can be used as lookup table
Secondly, I have attached an example of the original table and what I wish it to be after filtering so only unique values in column D.

If an answer already given is still relevant, let me know. (as it wasnt clear to me)
Or if a new answer is more suited, also let me know.


pls try

my example  added column for first unique Col D value and formulas for lookup

gh_userAuthor Commented:
Thanks Rgonzo
I noticed your formula in Column F relied on comparing a cell in Column D with the one above it.
So if the cell value was the same as a cell more than 1 cell above it,  it said it was unique, which is not true.
gh_userAuthor Commented:
Hi.  Andy's suggestion to remove duplicates worked for me on the example I uploaded.
Thanks for everyone suggestions.
then use as formula

gh_userAuthor Commented:
Thanks Andy
The suggestion to remove duplicates worked well.  I didnt realise at first it would remove rows belonging to duplicates. Thats why I took awhile to award.
gh_userAuthor Commented:
Sorry Rgonzo
as it was it was in your 2nd alternative I missed the significant of it.
Then when I went back to review answers saw it as Andy's first suggestion.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.