sort 2 or more occurrences

[akullen]

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,COUNTIF($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]

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.

 

[Herbert Seidenberg]

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