Auto sort text as it is entered

[sledge97]

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.

 

[swatsp0p]

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]

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?


Good Luck