On Sun, 30 Jan 2005 21:53:27 -0500, "Bill Martin -- (Remove NOSPAM from
address)" wrote:
The problem I have is sorting on IP addresses for which each of the a.b.c.d
numbers is in the range 0 - 255. I get the following occuring:-
Source:211.177.154.134, 1030, WAN -
Source:211.19.215.70, 1051, WAN -
Source:211.19.215.70, 1677, WAN -
Source:211.190.195.138, 2876, WAN -
when I would prefer to get this:-
Source:211.19.215.70, 1051, WAN -
Source:211.19.215.70, 1677, WAN -
Source:211.177.154.134, 1030, WAN -
Source:211.190.195.138, 2876, WAN -
Since Excel appears to sort on character position left to right adding in
leading 0's would make 019 a lower number than 177 and should result in the sort
running correctly.
Another option that I may have to resort to is to convert the numbers to the 8
bit binary equivalent using a lookup table in another worksheet and then sort on
that. Again the problem is that leading 0's are important and need to be
retained - i.e. 19 =00010011.
Perhaps this all may be simplified if I split up the a.b.c.d into 4 helper
columns and sort based on them. Would this sort correctly? I would need a
macro to sort on 4 criteria but that may be simple to write.
----------------
The number 19 is already lower than 177 even without a leading zero. If
you've broken the IP address into four cells then it sorts correctly.
Try it. Manually take the little 4 IP address example you gave and
divide it into four cells for each address. Then sort them as I said,
starting from the fourth cell as the key, then do it again with the
third, then do it again with the second, then do it again with the
first. It works. Try it. You don't need any macros or binary or anything.
If you want to do it with custom macros then you can add the leading
zeros and do it with one sort rather than four. It's a lot of
programming effort though to end up at the same place as the built in
functions already get you to.
Bill,
I set up a test file comprising the original data and then added 9 helper
columns to insert the 4 IP address components in columns H - K. I then sorted
on col H, then col I and then col J which resulted in a file which almost is
perfect. You're right - leading 0's are not needed.
I really need to sort on cols H, I, J, K and A (date and time) to get things in
the right order which will take a macro as Excel only allows for a 3 layer sort.
Interestingly, The file has 2843 rows of data at the moment but I put in code to
calculate the 9 helper columns to row 3000 (which should cater for most months)
and the file size jumped from 570Kb to 2.93Mb. All of the helper columns
follow the pattern =if($a1="","",MID($C1,$L1+1,$M1-$L1-1)).
Any help on how to set up the macro for a 5 column sort would be appreciated?
You've been a huge help already!
One wrinkle that the macro will need to cater for is that the data is being
derived from a text string so the numbers in the cols H - K and A are all text .
When I set up the sort on cols H, I and J Excel asked if I wanted the text
numbers treated as numbers - the macro will need to cater for this as well.
I assume that a macro can be written in say Notepad and then somehow associated
with the excel spreadsheet. The only macro I have written so far was one in
which I saved the keystrokes and then edited from within Excel with the
associated spreadsheet open.
I can see that I'll have to get myself a good book on Excel macros and do some
reading on the topic.
--
Cheers . . . JC
|