Collapse rows with duplicates
 

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 times
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 2
--------------------


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

Any suggestions?


--
mlcrane
------------------------------------------------------------------------
mlcrane's Profile: http://www.excelforum.com/member.php...o&userid=26670
View this thread: http://www.excelforum.com/showthread...hreadid=399437

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


"mlcrane" wrote in message
...

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 times
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 2
--------------------


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

Any suggestions?


--
mlcrane
------------------------------------------------------------------------
mlcrane's Profile: http://www.excelforum.com/member.php...o&userid=26670
View this thread: http://www.excelforum.com/showthread...hreadid=399437