Solved

Excel Table - Filtering on Unique Values in specific column

Posted on 2016-08-04
12
70 Views
Last Modified: 2016-08-08
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?
0
Comment
Question by:gh_user
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41743727
Hi,

Could you send a dummy?

Regards
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41743734
you could use for all unique values non empty

<>""

or as alternative you could use Data / Data Tools / Remove Duplicates
0
 
LVL 2

Accepted Solution

by:
Andy Cownie earned 500 total points
ID: 41743744
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:xtermie
ID: 41743795
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
0
 

Author Comment

by:gh_user
ID: 41746126
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
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41746665
Hi,

pls try

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

Regards
Lookup-tableV1.xlsx
0
 

Author Comment

by:gh_user
ID: 41746685
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.
0
 

Author Comment

by:gh_user
ID: 41746686
Hi.  Andy's suggestion to remove duplicates worked for me on the example I uploaded.
Thanks for everyone suggestions.
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41746687
then use as formula

=IF(COUNTIF($D$4:D4,4)>1,FALSE,TRUE)
1
 

Author Closing Comment

by:gh_user
ID: 41746689
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.
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41746690
0
 

Author Comment

by:gh_user
ID: 41746754
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.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question