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

[sledge97]

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.

[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