Auto sort text as it is entered
 

I have a list of "numbers" that I would like to have sorted
automatically in a separate column as I enter them. I say "numbers"
because some of them begin with a zero and I have the entire column
formatted as text so that it won't remove the zero from the beginning
after I hit Enter. I would like the second column to provide the same
list of sorted "numbers" (including the zeros) so I can check for
repeat entries. There has to be a formula or something that I can
apply to the second column so I don't have to highlight the range and
manually sort it. Any help would be greatly appreciated.


--
sledge97
------------------------------------------------------------------------
sledge97's Profile: http://www.excelforum.com/member.php...o&userid=19392
View this thread: http://www.excelforum.com/showthread...hreadid=374150

Try this:

First, set the format for BOTH columns (data entry and the 'sort'
column) as CUSTOM 000000 (use as many zeros as the maximum number of
digits in your your data)

Assuming you are entering data in the range H1:H24 in I1 enter this
formula and copy down to I24:

=SMALL(($H$1:$H$24),ROW())

Rows in column I that don't have data in H will show #NUM!. Your first
entry (in H1) will appear in I1. Your second entry will appear in
column I in either row 1 or 2 depending on its size compared to H1....
and so on.

NOTE: It will not be necessary to enter leading zeros, they will
automatically be pre-pended to any entry of fewer digits than the
CUSTOM format calls for. If you enter more digits than formatted,
leading zeros will be lost. ex: if you format for six zeros (000000)
and enter 0123456, only 123456 will display. However, if you enter
12345, 012345 will be displayed.

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=374150

I formatted the columns as CUSTOM 00000 and entered the formula as you
suggested in the sort column and I got #NUM! all the way down. I
clicked on one of the cells that I formatted to make sure it was CUSTOM
00000 and it had changed to SPECIAL - ZIP CODE. I don't know if that is
the problem, but it never stays as CUSTOM 00000.

Also, my inventory numbers vary from one to five digits and I can't
have a resulting sort with all numbers having five digits. For
example, I have one number of 041 representing one piece of equipment
and 0041 representing another. I believe that if the formula you gave
me would have worked for me, both would have sorted as 00041.


--
sledge97
------------------------------------------------------------------------
sledge97's Profile: http://www.excelforum.com/member.php...o&userid=19392
View this thread: http://www.excelforum.com/showthread...hreadid=374150

My solution won't work for the types of entries you are talking about.
Excel treats 0041 and 000041 as the same when ranking or sorting as
numbers. I wasn't aware of this 'twist' when suggesting this solution.
Sorry

This will probably require some VBA code to accomplish. Maybe someone
else has another idea to help you with this.

NOTE: I believe CUSTOM 00000 is the same as SPECIAL: ZIP CODE as both
will force display of leading zeros in a 5 digit number.

PS: how confusing is it to have 041 and 0041 be different items?
:eek:

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=374150