Leading zeros

[JC]

How does one force Excel 2003 to retain leading zeros? This is related to my
earlier messages on "Sorting problem".

One option is to use a number to binary conversion. Each binary number has 8
bits, either 1's of 0's, and all bits are significant so leading 0's must be
retained. The conversion will simply be a lookup table of number and associated
binary equivalent and use the number as a cell reference to get the binary
equivalent.

Another option is to insert leading 0's in numbers that have only 1 or 2 digits
to convert all numbers to 3 digit numbers.

The only way I have thought of is to define the cell as being text rather than a
number.

 

[Guest]

How does one force Excel 2003 to retain leading zeros? This is related to my
earlier messages on "Sorting problem".

One option is to use a number to binary conversion. Each binary number has 8
bits, either 1's of 0's, and all bits are significant so leading 0's must be
retained. The conversion will simply be a lookup table of number and associated
binary equivalent and use the number as a cell reference to get the binary
equivalent.

Another option is to insert leading 0's in numbers that have only 1 or 2 digits
to convert all numbers to 3 digit numbers.

The only way I have thought of is to define the cell as being text rather than a
number.

---------------------

Internally Excel carries numbers as .... well, as numbers. No number
has leading zeros as you are asking. The formatted *presentation* of a
number can have leading zeros, but that's just Excel humoring you by
pasting them on the front for a poor dumb human to look at. In it's
heart Excel knows that numbers don't have leading zeros -- as a
mathematician also knows the same thing. Therefore it's not about to
use leading zeros in a sort (though I can't quite see why you'd want it
to anyhow as it wouldn't affect the outcome).

If you want to force leading zeros to be carried internally then you're
right, you need to store text not a number.

Bill

 

[JC]

---------------------

Internally Excel carries numbers as .... well, as numbers. No number
has leading zeros as you are asking. The formatted *presentation* of a
number can have leading zeros, but that's just Excel humoring you by
pasting them on the front for a poor dumb human to look at. In it's
heart Excel knows that numbers don't have leading zeros -- as a
mathematician also knows the same thing. Therefore it's not about to
use leading zeros in a sort (though I can't quite see why you'd want it
to anyhow as it wouldn't affect the outcome).

If you want to force leading zeros to be carried internally then you're
right, you need to store text not a number.

Bill

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.

 

[Guest]

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

 

[JC]

----------------

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.

 

[Guest]

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.

-----------------

If you want to use a macro to do the 4 (or 5) sorts, the easiest thing
is to just record it from the keyboard/mouse.

1) Click on Tools>Macro>RecordNewMacro which will open up a window
asking what you want to name the macro. Enter the name and a letter
(e.g. "s" for sort) then click "ok". At that point a tiny window opens
which you can temporarily ignore.

2) Just go through the manual steps to sort the data all 4 or 5 times to
get the final result you want.

3) Go back to that tiny window and click "stop" or if it's not visible,
go back to Tools>Macro>StopRecording.

At this point you can execute the macro and sort everything just by
entering Ctrl-s. Note that upper and lower case "s" will be treated as
different macros.

If you want to see the macro and/or go in to edit it and change things
about, then click Tools>Macro>Macros and select the one you want then
click "edit". As far as I know the easiest way to work with macros is
with this VBA editor. You can apparently import/export files into the
editor but I've always just used the built in capability since it is a
development system that also flags incorrect statements, lets you step
through code, etc. You'll see the code looks pretty much like simple
BASIC, but with a little different syntax in spots. You can open up
macros from the example *.XLS files that came with Excel to see other
examples of how it works. There's also a reasonably good help system
built into that VBA editor that supplies some assistance with the
commands, etc.

I've found I can bumble my way through this VBA macro language just with
some prior BASIC experience and the help system built into the editor.
If you want to get into more heavy duty VBA programming, then I'm happy
enough with Walenbach's book "Excel 2003 Power Programming with VBA".

Good luck...

Bill

 

[JC]

-----------------

If you want to use a macro to do the 4 (or 5) sorts, the easiest thing
is to just record it from the keyboard/mouse.

1) Click on Tools>Macro>RecordNewMacro which will open up a window
asking what you want to name the macro. Enter the name and a letter
(e.g. "s" for sort) then click "ok". At that point a tiny window opens
which you can temporarily ignore.

2) Just go through the manual steps to sort the data all 4 or 5 times to
get the final result you want.

3) Go back to that tiny window and click "stop" or if it's not visible,
go back to Tools>Macro>StopRecording.

At this point you can execute the macro and sort everything just by
entering Ctrl-s. Note that upper and lower case "s" will be treated as
different macros.

If you want to see the macro and/or go in to edit it and change things
about, then click Tools>Macro>Macros and select the one you want then
click "edit". As far as I know the easiest way to work with macros is
with this VBA editor. You can apparently import/export files into the
editor but I've always just used the built in capability since it is a
development system that also flags incorrect statements, lets you step
through code, etc. You'll see the code looks pretty much like simple
BASIC, but with a little different syntax in spots. You can open up
macros from the example *.XLS files that came with Excel to see other
examples of how it works. There's also a reasonably good help system
built into that VBA editor that supplies some assistance with the
commands, etc.

I've found I can bumble my way through this VBA macro language just with
some prior BASIC experience and the help system built into the editor.
If you want to get into more heavy duty VBA programming, then I'm happy
enough with Walenbach's book "Excel 2003 Power Programming with VBA".

Good luck...

Bill

I recorded a 3 column sort macro and then followed the pattern to add keys 4 and
5 to the sort. This causes an Run-time error 1004 - Application-defined or
Object-defined error.

Other changes I made was to change Range("A538").Activate to Range("A1).Activate
and Range("A538").Select to Range("A1").Select as these positions will only
apply to this test file while A1 will apply whatever the size of the file.

The debug editor is highlighting the part starting with Selection.Sort..... and
ending with DataOption5:.....

I can't see anything in the code that looks wrong to me. This begs the
question - will Excel allow a 5 layer sort when its built in system only allows
nesting 3 layers?

Sub Sort()
'
' Sort Macro
' Macro recorded 1/02/2005 by John Clarke
'
' Keyboard Shortcut: Ctrl+s
'
Range("A13678").Select
Range("A1").Activate
Selection.Sort Key1:=Range("H1"), Order1:=xlAscending, Key2:=Range("I1") _
, Order2:=xlAscending, Key3:=Range("J1"), Order3:=xlAscending, _
Key4:=Range("K1"), Order4:=xlAscending, Key5:=Range("A1"), _
Order5:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortTextAsNumbers, _
DataOption3:=xlSortTextAsNumbers, DataOption4:=xlSortTextAsNumbers, _
DataOption5:=xlSortTextAsNumbers
Range("A1").Select
End Sub

 

[Guest]

I recorded a 3 column sort macro and then followed the pattern to add keys 4 and
5 to the sort. This causes an Run-time error 1004 - Application-defined or
Object-defined error.

Other changes I made was to change Range("A538").Activate to Range("A1).Activate
and Range("A538").Select to Range("A1").Select as these positions will only
apply to this test file while A1 will apply whatever the size of the file.

The debug editor is highlighting the part starting with Selection.Sort..... and
ending with DataOption5:.....

I can't see anything in the code that looks wrong to me. This begs the
question - will Excel allow a 5 layer sort when its built in system only allows
nesting 3 layers?

Sub Sort()
'
' Sort Macro
' Macro recorded 1/02/2005 by John Clarke
'
' Keyboard Shortcut: Ctrl+s
'
Range("A13678").Select
Range("A1").Activate
Selection.Sort Key1:=Range("H1"), Order1:=xlAscending, Key2:=Range("I1") _
, Order2:=xlAscending, Key3:=Range("J1"), Order3:=xlAscending, _
Key4:=Range("K1"), Order4:=xlAscending, Key5:=Range("A1"), _
Order5:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortTextAsNumbers, _
DataOption3:=xlSortTextAsNumbers, DataOption4:=xlSortTextAsNumbers, _
DataOption5:=xlSortTextAsNumbers
Range("A1").Select
End Sub

------------------

I don't know if what you've done is legal or not -- I've never tried it.
I suspect it's not legal since the manual version only allows three
levels.

Open up the VBA editor, put the cursor on the sort command and hit F1.
That brings up a help screen which implies only three levels can be done.

What I would do is simply what I said before. Do the sort twice -- once
with three levels and once with two. Just record it that way and then
go in to look at the code and see how they handled it.

It works. Try it.

Bill

 

[JC]

------------------

I don't know if what you've done is legal or not -- I've never tried it.
I suspect it's not legal since the manual version only allows three
levels.

Open up the VBA editor, put the cursor on the sort command and hit F1.
That brings up a help screen which implies only three levels can be done.

What I would do is simply what I said before. Do the sort twice -- once
with three levels and once with two. Just record it that way and then
go in to look at the code and see how they handled it.

It works. Try it.

Bill

I think you are correct in that Excel will only allow a 3 layer sort. If you
run a macro with 3 sort keys and then click on the Sort option the 3 keys used
in the macro are selected.

The rows must remain intact. What I am trying to do is sort the order in which
the complete rows appear so that like entries appear together. Two independent
sorts won't necessarily achieve that.

I have got around the 3 level sort restriction by changing the sort helpers from
4 helpers H=a, I=b, J=c and K=d to 2 helpers H=1000*a+b and I=1000*c+d where
a.b.c.d is the IP address. This automatically inserts leading 0's in the b and
d components as required and allows me to sort on the IP address and date.

Thank you for your help with this problem. You've given me the clues to sort
through the coding and end up with the result I wanted. Take a bow, Sir!

 

[Guest]

I think you are correct in that Excel will only allow a 3 layer sort. If you
run a macro with 3 sort keys and then click on the Sort option the 3 keys used
in the macro are selected.

The rows must remain intact. What I am trying to do is sort the order in which
the complete rows appear so that like entries appear together. Two independent
sorts won't necessarily achieve that.

I have got around the 3 level sort restriction by changing the sort helpers from
4 helpers H=a, I=b, J=c and K=d to 2 helpers H=1000*a+b and I=1000*c+d where
a.b.c.d is the IP address. This automatically inserts leading 0's in the b and
d components as required and allows me to sort on the IP address and date.

Thank you for your help with this problem. You've given me the clues to sort
through the coding and end up with the result I wanted. Take a bow, Sir!

-------------

Two separate sorts will do it very well. Just try it -- you'll see.
That's the technique that's been taught in computer science for the last
30 years at least.

The rows remain intact if you tell Excel to sort the entire rows based
on the keys in the one column of interest.

Even when Excel is told to do the three level sort, I suspect under the
covers it's just doing the simple one column sort independently three
times. That would be the simplest way to code it and possibly the fastest.

Bill