Filtering unique combinations of cells

[John T Kennedy]

I have a spreadhseet (10K+ rows) that contains
Column D: First Name
Coulmn E: Last Name
Column F: Company Name

How can I setup a filter to show only each unique combination of columns D,
E &F?

Thanks in advance for sharing your expertise.

 

[Jacob Skaria]

Make sue you have headers for these columns.
From menu Data>Filter>Advanced Filter
Select 'Copy to another location'
In listrange select the data range including headers.
In Copy to select a unused column say cell M1
Check Unique record only
Click OK


If this post helps click Yes

 

[Mike H]

Hi,

Put this formula in g1 and drag down

=D1&E1&F1

Put this formula in H1 and drag down

=COUNTIF($G$1:$G$20,G1)

The second formula will return a 1 for unique items so filter by 1 on column
G and you have a unique list.

Mike

 

[Max]

Another play using autofilter on a helper col

In G2:
=IF(COUNTA(D2:F2)<3,"",IF(SUMPRODUCT((D$22=D2)*(E$2:E2=E2)*(F$2:F2=F2))>1,"","x"))
Copy G2 down to cover the max expected extent of data. Col G will flag
unique combinations with "x". Apply autofilter on col G, choose: x to filter
out the lot, on demand.

Success? Punch it here, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[John T Kennedy]

This method gives me a list with Names & Companies, but doesn't filter the
rest of the list (ie: I want to be able to filter my whole database based on
the unique combinations of E, D & F).

 

[John T Kennedy]

Your method tells me how many times each combination appears. I must have
asked the question in the wrong way.

 

[John T Kennedy]

Showed me the unique combinations. You answered my question, and also made
me realize that I asked the wrong question. Thanks, though, for pointing me
in the right direction.

 

[Max]

You're welcome, John
Thanks for the feedback
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik