COUNTIF forumla help

Z

Zeke009

I'll do my best on explaining this, it might be a little complicated...

I have a spreadsheet of 21,000 entries for machine names with a certain
application installed and all reported versions of this application. I've
used the following formula to show me duplicate occurances of machine names
where the system has multiple versions loaded. COUNTIF($A$2:$A2,A2)>1

Now that formula works fine for showing me the 2nd, 3rd or more occurance of
a machine name. But now I need to provide a list with all machine names that
appear on the list more than once. I am not sure how I can get the COUNTIF
statement to identify all occurances, especially the 1st occurance of a
machine name. I really don't want to scroll through the list to delete
non-duplicate entries and this will be a weekly task for the next few weeks.

If I was not clear in my question, please let me now and I'll do my best to
explain it more thoroughly.

Thanks,

Zeke
 
B

Bob Phillips

=COUNTIF(A:A,A2)>1

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
H

Herbert Seidenberg

Assume your data at A1 looks like this:
ListMa
L
C
J
I
B
F
H
D
J
B
B
J
D
H
B
J
D
C
and you want to generate this list:
ListMa
C
J
B
H
D
Create these 2 cells at B1
XYZZ
FALSE
B1 can be any text but ListMa, or leave blank.
B2 has the formula
=COUNTIF($A$2:$A$19,A2)>1
Data > Filter > Advanced Filter > Copy to another location
List Range: $A$1:$A$19
Criteria Range: $B$1:$B$2
Copy to: $C$1
Check Unique Records only
There are many ways to make mistakes here.
The safer, more flexible, updatable way is with Pivot Table.
 
Top