Sorting Question

**1jklange** · 07-02-2012, 04:07 PM

Hi,

For my company I am going through data that involves click fraud and get reports that are unorganized and messy. Is it possible to take the data from the reports and plug them into a custom template and the template organizes it. Each report is sorted by IP address and then has cells containing other information about what the IP was doing. So basically I want the report to show me only IP's that appear multiple times. The original reports are not all the same size because it is clicked-based so for every click on the website the report gathers the IP that clicked. So the template would have to be able to adjust to different sizes of data. Can anyone help out?

I can't supply a report because it will contain sensitive information but if someone would like to see what the report looks like I can remove the information and fill it with example numbers.

Thanks

**mack zilla** · 07-02-2012, 04:40 PM

I would select the report, sort it by the ip address then select conditional formatting/highlight duplicate values from the home tab. That seems like a task so uncomplicated I wouldn't attempt much more.

Zack

**martindwilson** · 07-02-2012, 04:47 PM

sorting ip is not straight forward (mind you finding duplicates is) you might find the following useful anyway

10.10.10.100
10.10.10.9
10.10.10.200
10.5.10.15
sorts as

10.10.10.100
10.10.10.200
10.10.10.9
10.5.10.15

you need to pad out the octets to 3 digits or convert to real value one way is to put this in an adjacent column then sort assuming ip starts in a1
=((VALUE(LEFT(A1, FIND(".",A1)-1)))*256^3)+((VALUE(MID(A1, FIND(".",A1)+1, FIND(".",A1, FIND( ".",A1)+1)- FIND(".",A1)-1)))*256^2)+((VALUE(MID(A1, FIND(".",A1, FIND( ".",A1)+1)+1, FIND(".",A1, FIND( ".",A1, FIND( ".",A1)+1)+1)-FIND(".",A1, FIND( ".",A1)+1)-1)))*256)+(VALUE(RIGHT(A1, LEN(A1)-FIND(".",A1, FIND( ".",A1, FIND( ".",A1)+1)+1))))
you then get

10.5.10.15
10.10.10.9
10.10.10.100
10.10.10.200
or try this udf put in new module then us as =ipsort(a1) this pads out the octets then you can sort
eg
10.5.10.15 becomes 010.005.010.015

Please Login or Register  to view this content.

code from DMcRitch http://www.office-archive.com/4-exce...e8631f02bf.htm

**1jklange** · 07-02-2012, 05:16 PM

The report does come with the IP's sorted but it is over 1000 IP's. I need a way to take the report and put it into a template that will somehow only show the IP's that appear more than "X" times. Is something like this possible? Not every report has the same number of IP's though.