Cutting down a list and providing a number of duplicates.

[mdurkin]

Hi There,

I'm pulling data from my servers (alarms) and have roughly 10 alarm points, reporting 9000 alarms. I've been able to pull them off the server on an excel file, have been able to sort them, but would like to venture into the hope that there is some sort of formula that would take the duplicate alarms(cell a1-a9000), delete them, and report how many duplicates were removed from the list in cell b1-b10

xyz
xyz
xyz
xyz
123
123
zzz

---

Hoping somehow it could be turned into
"xyz 4"
"123 2"
"zzz 1"

[etaf]

you could put all the unique entries in B and then count them in c

=INDEX($A$2:$A$400, MATCH(0, COUNTIF($B1:B$1, $A$2:$A$400), 0)) - enter as an array using control+shift+enter
abd then do a
Countif($A$2:$A$400, B2)

[hansaaa]

yep, either that, or, use a macro!

[Kevin UK]

Hi mdurkin

Or a pivot table. See the attached.

etaf, the index function in your post doesn't need to be entered as a array formula.