How do I exclude non-duplicates from an excel file?

[Guest]

Hi

I need to exclude non-duplicate entries from an excel file (yes,
non-duplicates, not duplicates). Any suggestions?

Thanks

 

[Frank Kabel]

Hi
lets assume your source list is in A1:A10
now enter the following formulas:
B1: =A1
B2: enter the array formula (committed with cTRL+sHIFT+ENTER):
=INDEX($A$1:$A$10,MATCH(1,COUNTIF($A$1:$A$10,$A$1:$A$10)
*(COUNTIF(B$1:B1,$A$1:$A$10)=0),0))

and copy this down unless you get an error message

 

[Tushar Mehta]

I am not sure what Frank's formula is meant to do, but here's what I
would do to identify the non-duplicates. Suppose your list is in
column A. Further suppose A1 is a header cell and the data start in
A2. Then, in B1 enter some kind of a header, say Unique. In B2, enter
the formula =COUNTIF(A:A,A2). Copy it as far down B as you have data
in A.

Any cell that has a 1 in column B represents an unique entry in A. You
can use XL's autofilter capability to identify all the unique entries.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Frank Kabel]

Hi Tushar
my formula should (and it least it worked in my test scenario) extract
only the values which occur once in A1:A10. I only make one mistake
with the formula in B1
B1 should contain the array formula:
=INDEX($A$1:$A$10,MATCH(1,COUNTIF($A$1:$A$10,$A$1:$A$10),0))

So for the example set of data
1
1
2
3
4
4
5
6
6
7

my formulas would return
2
3
5
7


--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag