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

 

[Tom Ogilvy]

this worked with your data:

Sub insertRows()
Dim lastrow As Long, i As Long
Dim ii As Long, j As Long
Dim iadd As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 2 Step -1
If Not IsEmpty(Cells(i, 1)) Then
ii = CLng(Right(Cells(i, 1), _
Len(Cells(i, 1)) - 1))
Else
Debug.Print i, iadd
ii = iadd
End If

jj = CLng(Right(Cells(i - 1, 1), _
Len(Cells(i - 1, 1)) - 1))
iadd = ii
Do While jj <> iadd - 1
Rows(i).Insert
iadd = iadd - 1
Loop
Next
End Sub

it assumes that the numbers increase as you go down the rows (it doesn't
account for:

D00099
D00100
E00001
E00003

goes from 100 down to 1)

as the numbers in your example do.

 

[Dave09]

this worked with your data:

Sub insertRows()
Dim lastrow As Long, i As Long
Dim ii As Long, j As Long
Dim iadd As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 2 Step -1
If Not IsEmpty(Cells(i, 1)) Then
ii = CLng(Right(Cells(i, 1), _
Len(Cells(i, 1)) - 1))
Else
Debug.Print i, iadd
ii = iadd
End If

jj = CLng(Right(Cells(i - 1, 1), _
Len(Cells(i - 1, 1)) - 1))
iadd = ii
Do While jj <> iadd - 1
Rows(i).Insert
iadd = iadd - 1
Loop
Next
End Sub

it assumes that the numbers increase as you go down the rows (it doesn't
account for:

D00099
D00100
E00001
E00003

goes from 100 down to 1)

as the numbers in your example do.

--
Regards,
Tom Ogilvy










- Show quoted text -

Where would I put this script? do I goto the VB editor and bind it to
the current worksheet im in? like Tools--> Macro--> VB editor? If you
could post where I put this information and save it and how to run it
would be great appreciated. Im not the excel guru like most hehe

 

[Tom Ogilvy]

With only your workbook open,

Alt + F11 to go to the VBE (Visual basic editor)

in the editor, make sure your workbook or one of its subordinate elements is
selected in the project explorer. Then do Insert=>Module. Paste the code
into that module.

No do Alt + F11 to get back to excel. Save your workbook.

To run the macro, make the sheet with the data active, Go to
Tools=>Macro=>Macros and select the macro. Hit the Run button.

Do all this on a copy of your workbook till you are sure it does what you
want.

 

[Dave09]

With only your workbook open,

Alt + F11 to go to the VBE (Visual basic editor)

in the editor, make sure your workbook or one of its subordinate elements is
selected in the project explorer. Then do Insert=>Module. Paste the code
into that module.

No do Alt + F11 to get back to excel. Save your workbook.

To run the macro, make the sheet with the data active, Go to
Tools=>Macro=>Macros and select the macro. Hit the Run button.

Do all this on a copy of your workbook till you are sure it does what you
want.

--
Regards,
Tom Ogilvy






- Show quoted text -

Awesome. This is exactly what I needed. Works great! thank you much
saved me MANY hours.

 

[Bernd]

Hello,

Tom already gave you a VBA solution.

An approach with worksheet functions:
Write into B1:
=IF(ISERROR(MATCH("D"&TEXT(ROW(),"00000"),$A$1:$A
$9999,)),"",VLOOKUP("D"&TEXT(ROW(),"00000"),$A$1:$A$9999,1,))

and copy down.

Regards,
Bernd