gh_user
asked on
Excel Table - Filtering on Unique Values in specific column
Hi
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?
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?
you could use for all unique values non empty
<>""
or as alternative you could use Data / Data Tools / Remove Duplicates
<>""
or as alternative you could use Data / Data Tools / Remove Duplicates
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
https://support.microsoft.com/en-us/kb/2720580
LMK if you want to work on a sample of your own
AdvFilterExample.xlsx
Check the example I've attached. Hope this helps!
Good explanation here
https://support.microsoft.com/en-us/kb/2720580
LMK if you want to work on a sample of your own
AdvFilterExample.xlsx
ASKER
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.
Thanks
Lookup-table.xlsx
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.
Thanks
Lookup-table.xlsx
Hi,
pls try
my example added column for first unique Col D value and formulas for lookup
Regards
Lookup-tableV1.xlsx
pls try
my example added column for first unique Col D value and formulas for lookup
Regards
Lookup-tableV1.xlsx
ASKER
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.
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.
ASKER
Hi. Andy's suggestion to remove duplicates worked for me on the example I uploaded.
Thanks for everyone suggestions.
Thanks for everyone suggestions.
then use as formula
=IF(COUNTIF($D$4:D4,4)>1,F ALSE,TRUE)
=IF(COUNTIF($D$4:D4,4)>1,F
ASKER
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.
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.
ASKER
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.
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.
Could you send a dummy?
Regards