Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
How do I find only the singular rows in a sheet with duplicates? | Excel Discussion (Misc queries) | |||
How do I combine rows, combining duplicates as well as concatenati | Excel Worksheet Functions | |||
Adding Rows to Master Sheet | New Users to Excel | |||
How do you use the explode and collapse panel to hide rows? | Excel Worksheet Functions |