How to sort AlphaNumeric Data sequentially with spaces
Maybe something like this, which will actually put the missing items in the
list.
using your example:
Here are the steps.....
1)Create a column of the existing numbers (without the alpha prefix)
2)Use a formula to list the missing values
3)Use another formula to convert the missing values to valid items
4)Copy/Paste_Special_Values the converted items to below the origina list
5)Sort the list
Assuming your list is in A1:A12 (as you posted)
1)B1: =--MID(A1,2,255)
copy that formula down thru B12
(the series will start with 18, 19, 23....thru 50)
2)Enter this ARRAY FORMULA in cell
C1:
=INDEX(ROW($A$1:INDEX(B:B,MAX(B:B))),SMALL(IF(COUN TIF($A$1:INDEX(B:B,MAX(B:B)),ROW($A$1:INDEX(B:B,MA X(B:B)))+MIN(B:B)-1)=0,ROW($A$1:INDEX(B:B,MAX(B:B)))+MIN(B:B)-1),ROWS($1:1))-MIN(B:B))+MIN(B:B)
copy C1 and paste into C2 and down as far as you need
Note: For array formulas,
hold down [Ctrl] and [Shift] when you press [Enter],
instead of just pressing [Enter].
3)D1: =TEXT(C1,"\D00000")
copy that formula down as far as you need (in this case thru D22)
4)Copy D1:D22 and Paste_Special.Values into A13
5)Sort Col_A
(The missing items will sort into the list)
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
"Dave09" wrote:
Hi all,
I am in a situation where if I could figure out how to correct
sort AlphaNumeric Data sequentially with adding empty cells where the
sequence skips I could save myself Hours upon HOURS of work. Here is
what I need help with....
I have 1 columns of data for example ( the < before the data is the
cell number)....
<A1 D00018
<A2 D00019
<A3 D00023
<A4 D00030
<A5 D00031
<A6 D00033
<A7 D00034
<A8 D00042
<A9 D00045
<A10 D00046
<A11 D00049
<A12 D00051
What I wish for is a macro or some 'sort' function to do is include
blank cells where the numbers skip. I.E. the output to look like
this....
<A1 D00018
<A2 D00019
<A3
<A4
<A5
<A6 D00023
<A7 D00024
<A8 D00025
<A9
<A10 D00027
<A11
<A12 D00029
<A13
<A14 D00031
<A15 D00032
<A16
<A17 D00034
Only alternative I have is going through and manually putting blank
cells in where the sequence skips. I have to do this for over 10000
entries =/ IS there a link to a solution for this already as im new to
the forums? Or anyone have a step by step way of completing this task?
ANYTHING would be GREATLY appreciated!! Thanks much!!
|