How to sort AlphaNumeric Data sequentially with spaces

D

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

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

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


Ron Coderre said:
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 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 said:
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!!
 
D

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



Ron Coderre said:
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 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:

- 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.
 
R

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


Dave09 said:
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



Ron Coderre said:
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 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!!- 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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top