How to sort AlphaNumeric Data sequentially with spaces

[Dave09]

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!!

 

[Ron Coderre]

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(COUNTIF($A$1:INDEX(B:B,MAX(B:B)),ROW($A$1:INDEX(B:B,MAX(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 D122 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

 

[Ron Coderre]

Actually, in this particular case, you could use this variation of a shorter
ARRAY FORMULA which I first saw posted by BIff

C1: =SMALL(IF(ISNA(MATCH(ROW($18:$51),B$1:B$51,0)),ROW($18:$51)),ROW(A1))


Does that help?
***********
Regards,
Ron

XL2002, WinXP

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(COUNTIF($A$1:INDEX(B:B,MAX(B:B)),ROW($A$1:INDEX(B:B,MAX(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 D122 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

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!!

 

[Dave09]

Actually, in this particular case, you could use this variation of a shorter
ARRAY FORMULA which I first saw posted by BIff

C1: =SMALL(IF(ISNA(MATCH(ROW($18:$51),B$1:B$51,0)),ROW($18:$51)),ROW(A1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Maybe something like this, which will actually put the missing items inthe
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(COUNTIF($A$1:INDEX(B:B,MAX(B:­B)),ROW($A$1:INDEX(B:B,MAX(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 D122 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:

- Show quoted text -

2 questions....

1) is the C1: formula you posted second to be replaced by the one in
your original post?
2) will the end result put empty cells where a number is missing?
thats what I want.

 

[Ron Coderre]

Question 1: Does the new formula replace the previous one?
Answer: Yes

Question 2: Does my posted process insert blanks in the series
Answer: No....but, you could either copy your original list to another
column or user formulas...then put the missing items below them, sort that
column, then delete it. That would put the spaces you need.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Actually, in this particular case, you could use this variation of a shorter
ARRAY FORMULA which I first saw posted by BIff

C1: =SMALL(IF(ISNA(MATCH(ROW($18:$51),B$1:B$51,0)),ROW($18:$51)),ROW(A1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

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(COUNTIF($A$1:INDEX(B:B,MAX(B:­B)),ROW($A$1:INDEX(B:B,MAX(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 D122 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!!- Hide quoted text -

- Show quoted text -

2 questions....

1) is the C1: formula you posted second to be replaced by the one in
your original post?
2) will the end result put empty cells where a number is missing?
thats what I want.