Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sort 2 or more occurrences
Hi I have an excel sheet that I use as a log for my firewall. I one column I have all the IP addresses that have been log by the firewall. What i want to do is to, in a new column, sort the IP's based on number of occurrences and only listing those with 2 or more occurrences. The closest I have come is in the attachment. The only problem with that “solution” is that it cannot handle different IP’s with the same number of occurrences. I tried adding the IP’s as decimals to the occurrences but since an IP can have 12 figures and more that 100 occurrences, i.e. 3 more figures, and I had to add a trailing 0 to cope with blank cells I broke the 15 figures limit. Any help would be great. The excel file looks like this A1:A12 123.123.123.123 123.123.123.123 123.123.123.123 123.321.321.321 321.321.321.321 123.123.321.321 123.123.321.321 321.321.321.321 123.123.123.321 1.1.1.5 Paste as arrayformula in B2 and drag to B12 {=LARGE(IF(COUNTIF($A$2:$A$12,$A$2:$A$12)=2,COUNT IF($A$2:$A$12,$A$2:$A$12)),SUM(B$1:B1)+1)} Paste as arrayformula in C2 and drag to C12 {=INDEX($A$2:$A$12,MATCH(B2,COUNTIF($A$2:$A$12,$A$ 2:$A$12),0))} +-------------------------------------------------------------------+ |Filename: sort 2 or more ocurr.zip | |Download: http://www.excelforum.com/attachment.php?postid=4607 | +-------------------------------------------------------------------+ -- akullen ------------------------------------------------------------------------ akullen's Profile: http://www.excelforum.com/member.php...o&userid=32513 View this thread: http://www.excelforum.com/showthread...hreadid=531218 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sort 2 or more occurrences
I forgott to sat that the error occurres in C4 which lists: 321.321.321.321 insteed of: 123.123.321.321 since they have the same number of occurrences Thanks. -- akullen ------------------------------------------------------------------------ akullen's Profile: http://www.excelforum.com/member.php...o&userid=32513 View this thread: http://www.excelforum.com/showthread...hreadid=531218 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sort 2 or more occurrences
Convert the addresses (modified for readability)
into numbers and add headers as shown: bin2 bin3 111222333444 333222333444 111222333444 111222444555 111222333555 111222333555 111222444555 111222333444 333222333444 0 0 123123321321 0 111222444555 0 333222333444 0 111222333555 0 1115 0 Name the columns bin2 and bin3. The header <bin2 is located at A1. In the first cell of bin3, enter this array formula =MAX(bin2*(COUNTIF(bin2,bin2)1)) In the second cell of bin 3, enter this array formula =MAX(IF((bin2<bin3 2:2)*(COUNTIF(bin2,bin2)1),bin2,0)) Then drag the fill handle until zeros appear. Note: Each cell gets its own CTRL+SHIFT+ENTER Credit: Harlan Grove |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select rows and sort based on type | Excel Discussion (Misc queries) | |||
data sort is not including all columns in sort | Excel Discussion (Misc queries) | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
"-" ignored in sort | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |