Collapse rows with duplicates

[mlcrane]

Hoping someone out there will be able to help me with this...

I have a sheet of records, IP addresses and a count of how many time
each IP appear in the list, that looks something like:


Code
-------------------
192.168.0.1 1
192.168.0.2 1
192.168.0.2 2
192.168.0.2 3
192.168.0.3 1
192.168.0.3
-------------------


and so on. I have Excel (2003) sorting the IP addresses for me and th
count of duplicates all by itself, but the one thing I don't know ho
to make it do is remove all the duplicates, leaving the duplicate wit
the highest count.

Any suggestions

 

[Bernie Deitrick]

mlcrane,

For your example data, if it starts in cell A1, in cell C1, use the formula

=COUNTIF(A:A,A1)=B1

and copy down to match.

Then sort the table based on column C, and delete all the rows where C is FALSE.

Or forget about column B and C altogether, and use a pivot table based only on column A. Drop that
field into both the row and data fields, and Excel will produce a table of IP addresses along with
their counts.

HTH,
Bernie
MS Excel MVP