Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.misc
|
|||
|
|||
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!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort AlphaNumeric Data sequentially with spaces
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" wrote: 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!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort AlphaNumeric Data sequentially with spaces
On Feb 1, 12:38 pm, Ron Coderre
wrote: 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" wrote: 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,M AX(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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort AlphaNumeric Data sequentially with spaces
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" wrote: On Feb 1, 12:38 pm, Ron Coderre wrote: 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" wrote: 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, 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
Macro question | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Sort corrupts mass data intermittently? | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) |