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))}
Bookmarks