Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
move cells based on group levels
I have a grouped data that looks like this:
Column A 02131 SYN.NON-NRC.INJ DALGAN AZN 90/07 FCD DISP SYR REG IM/IV/SC 15MG/M 5MG/ML 10MG/M FDD VIAL REGULAR IM/IV/SC 15MG/M 10MG/M 5MG/ML So line 1 is group level 1, and second indentation is group level 2, and third indentation is group level 3, and fourth indentation is group level 4. They are all in the same column. Is there a way to move second indentation to column B, third indentation to column C and fourth indentation to column D, so that it looks like the following: A B C D 02131 SYN.NON-NRC.INJ DALGAN AZN 90/07 FCD DISP SYR REG IM/IV/SC 15MG/M 5MG/ML 10MG/M FDD VIAL REGULAR IM/IV/SC 15MG/M 10MG/M 5MG/ML The data is massive. And it seems impossible to do it by hand. Thanks a lot. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
move cells based on group levels
Maybe...
From your post, it looks like your data has 0 leading spaces in the first level, 2 in the second level, 4 in the next, 6 in the next... With the data in A1:A###... I used these formulas: In B1: =IF(LEFT($A1,1)<" ",TRIM($A1),NA()) In C1: =IF(ISTEXT(B1),"done",IF(LEFT($A1,3)<REPT(" ",3),TRIM($A1),NA())) In D1: =IF(ISTEXT(C1),"done",IF(LEFT($A1,5)<REPT(" ",5),TRIM($A1),NA())) In E1: =IF(ISTEXT(D1),"done",IF(LEFT($A1,7)<REPT(" ",7),TRIM($A1),NA())) Then I dragged them down the columns. I waited for the recalc and converted to values (edit|copy, edit|paste special values) Then I used a couple of edit|replaces to get rid of #N/A's and done's (I replaced them with nothing.) research-assistant wrote: I have a grouped data that looks like this: Column A 02131 SYN.NON-NRC.INJ DALGAN AZN 90/07 FCD DISP SYR REG IM/IV/SC 15MG/M 5MG/ML 10MG/M FDD VIAL REGULAR IM/IV/SC 15MG/M 10MG/M 5MG/ML So line 1 is group level 1, and second indentation is group level 2, and third indentation is group level 3, and fourth indentation is group level 4. They are all in the same column. Is there a way to move second indentation to column B, third indentation to column C and fourth indentation to column D, so that it looks like the following: A B C D 02131 SYN.NON-NRC.INJ DALGAN AZN 90/07 FCD DISP SYR REG IM/IV/SC 15MG/M 5MG/ML 10MG/M FDD VIAL REGULAR IM/IV/SC 15MG/M 10MG/M 5MG/ML The data is massive. And it seems impossible to do it by hand. Thanks a lot. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Data Group and Outline Group" feature Limited to 8 levels | Excel Discussion (Misc queries) | |||
WHEN I GROUP IT ERRORS WITH 'CANNOT MOVE CELLS OFF WORKSHEET?? | Excel Worksheet Functions | |||
Have VBA delete a group of cells, move information over, then add | Excel Discussion (Misc queries) | |||
Wherever I try and move my curser to it highlights that group of c | Excel Discussion (Misc queries) | |||
Subtract a group of cells from a total based on ending date | Excel Discussion (Misc queries) |