Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to sort AlphaNumeric Data sequentially with spaces
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to sort AlphaNumeric Data sequentially with spaces
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 "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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to sort AlphaNumeric Data sequentially with spaces
On Feb 1, 1:28 pm, Tom Ogilvy
wrote: 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 "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 - 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to sort AlphaNumeric Data sequentially with spaces
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 "Dave09" wrote: On Feb 1, 1:28 pm, Tom Ogilvy wrote: 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 "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 - 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to sort AlphaNumeric Data sequentially with spaces
On Feb 1, 3:34 pm, Tom Ogilvy
wrote: 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 "Dave09" wrote: On Feb 1, 1:28 pm, Tom Ogilvy wrote: 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 "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 - 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- Hide quoted text - - Show quoted text - Awesome. This is exactly what I needed. Works great! thank you much saved me MANY hours. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to sort AlphaNumeric Data sequentially with spaces
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to sort AlphaNumeric Data sequentially with spaces | Excel Discussion (Misc queries) | |||
alphanumeric sort | Excel Discussion (Misc queries) | |||
sort alphanumeric data | New Users to Excel | |||
Sort - alphanumeric. | New Users to Excel | |||
Alphanumeric Sort | Excel Discussion (Misc queries) |