Filtering unique combinations of cells
 

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.

Answer: Filtering unique combinations of cells
 

Steps to filter for unique combinations of cells in Excel:

1. Select the entire range of data that you want to filter. In your case, that would be columns D, E, and F.
2. Click on the "Data" tab in the ribbon at the top of the screen.
3. Click on the "Advanced" button in the "Sort & Filter" section.
4. In the "Advanced Filter" dialog box, select the "Copy to another location" option.
5. In the "Copy to" field, select a cell where you want the filtered data to be copied. This can be on the same sheet or a different sheet.
6. Check the "Unique records only" box.
7. In the "List range" field, enter the range of cells that you want to filter. This should be the same range that you selected in step 1.
8. In the "Criteria range" field, enter the range of cells that contains the criteria for the filter. In your case, you can leave this field blank since you want to filter for unique combinations of cells.
9. Click the "OK" button to apply the filter.

Excel will now filter the data based on the unique combinations of cells in columns D, E, and F, and copy the filtered data to the location that you specified in step 5.

Filtering unique combinations of cells
 

Make sue you have headers for these columns.
From menu DataFilterAdvanced 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
---------------
Jacob Skaria


"John T Kennedy" wrote:

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.

Filtering unique combinations of cells
 

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
"John T Kennedy" wrote:

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.

Filtering unique combinations of cells
 

Another play using autofilter on a helper col

In G2:
=IF(COUNTA(D2:F2)<3,"",IF(SUMPRODUCT((D$2:D2=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" wrote:
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.

Filtering unique combinations of cells
 

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).

"Jacob Skaria" wrote:

Make sue you have headers for these columns.
From menu DataFilterAdvanced 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
---------------
Jacob Skaria


"John T Kennedy" wrote:

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.

Filtering unique combinations of cells
 

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

"Mike H" wrote:

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
"John T Kennedy" wrote:

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.

Filtering unique combinations of cells
 

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" wrote:

Another play using autofilter on a helper col

In G2:
=IF(COUNTA(D2:F2)<3,"",IF(SUMPRODUCT((D$2:D2=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" wrote:
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.

Filtering unique combinations of cells
 

You're welcome, John
Thanks for the feedback
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"John T Kennedy" wrote in message
...
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.