| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How would I sort the following?
In an Excel sheet, I have a column that looks like the one below.
*** Insert 18-00-00 2X4 No.2 *** T,"12647",1,1GT1,2X4,No.2,"1T1",158.1664,1,1,112.6 ,112.6,0,0,0,0 W,"12647",1,1H2,2X4,No.2,"W6",55.0022,2,1,149.6,82 .2,82.2,0,0,0 *** Insert 12-00-00 2X4 No.2 *** B,"12647",1,1GT1,2X4,No.2,"B2",19.8329,1,1,67.4,90 ,0,0,0,0 B,"12647",1,1H3,2X4,No.2,"B5",122.4992,1,1,90,90,0 ,0,0,0 *** Insert 18-00-00 2X4 No.2 *** T,"12647",1,1GT1,2X4,No.2,"1T1",158.1664,1,1,112.6 ,112.6,0,0,0,0 W,"12647",1,1T1,2X4,No.2,"W1",53.0621,2,1,112.6,67 .4,90,0,0,0 *** Insert 12-00-00 2X4 No.2 *** B,"12647",1,1GT1,2X4,No.2,"B2",19.8329,1,1,67.4,90 ,0,0,0,0 B,"12647",1,1H3,2X4,No.2,"B5",122.4992,1,1,90,90,0 ,0,0,0 *** Insert 18-00-00 2X4 No.2 *** B,"12647",1,1T1,2X4,No.2,"B3",131.9992,1,1,90,90,0 ,0,0,0 W,"12647",1,1H2,2X4,No.2,"W10",11.6247,1,1,67.4,90 ,0,0,0,0 W,"12647",1,1H1,2X4,No.2,"W2",65.7763,2,2,115.2,25 .2,115.2,25.2,0,0 It has a *** Insert ... and below the *** Insert ... there is associated data belonging to the *** Insert ... header. The data following the *** Insert ... header can contain several rows of data belonging to the header before the next *** Insert .... header. What I need to do is to sort the column by the *** Insert ... header but with its associated data below it. Please see example... Data Before Sort: *** Insert 18-00-00 2X4 No.2 *** T,"12647",1,1GT1,2X4,No.2,"1T1",158.1664,1,1,112.6 ,112.6,0,0,0,0 W,"12647",1,1H2,2X4,No.2,"W6",55.0022,2,1,149.6,82 .2,82.2,0,0,0 *** Insert 12-00-00 2X4 No.2 *** B,"12647",1,1GT1,2X4,No.2,"B2",19.8329,1,1,67.4,90 ,0,0,0,0 B,"12647",1,1H3,2X4,No.2,"B5",122.4992,1,1,90,90,0 ,0,0,0 *** Insert 18-00-00 2X4 No.2 *** T,"12647",1,1GT1,2X4,No.2,"1T1",158.1664,1,1,112.6 ,112.6,0,0,0,0 W,"12647",1,1T1,2X4,No.2,"W1",53.0621,2,1,112.6,67 .4,90,0,0,0 *** Insert 12-00-00 2X4 No.2 *** B,"12647",1,1GT1,2X4,No.2,"B2",19.8329,1,1,67.4,90 ,0,0,0,0 B,"12647",1,1H3,2X4,No.2,"B5",122.4992,1,1,90,90,0 ,0,0,0 *** Insert 18-00-00 2X4 No.2 *** B,"12647",1,1T1,2X4,No.2,"B3",131.9992,1,1,90,90,0 ,0,0,0 W,"12647",1,1H2,2X4,No.2,"W10",11.6247,1,1,67.4,90 ,0,0,0,0 W,"12647",1,1H1,2X4,No.2,"W2",65.7763,2,2,115.2,25 .2,115.2,25.2,0,0 Data After Sort: *** Insert 18-00-00 2X4 No.2 *** T,"12647",1,1GT1,2X4,No.2,"1T1",158.1664,1,1,112.6 ,112.6,0,0,0,0 W,"12647",1,1H2,2X4,No.2,"W6",55.0022,2,1,149.6,82 .2,82.2,0,0,0 *** Insert 18-00-00 2X4 No.2 *** T,"12647",1,1GT1,2X4,No.2,"1T1",158.1664,1,1,112.6 ,112.6,0,0,0,0 W,"12647",1,1T1,2X4,No.2,"W1",53.0621,2,1,112.6,67 .4,90,0,0,0 *** Insert 18-00-00 2X4 No.2 *** B,"12647",1,1T1,2X4,No.2,"B3",131.9992,1,1,90,90,0 ,0,0,0 W,"12647",1,1H2,2X4,No.2,"W10",11.6247,1,1,67.4,90 ,0,0,0,0 W,"12647",1,1H1,2X4,No.2,"W2",65.7763,2,2,115.2,25 .2,115.2,25.2,0,0 *** Insert 12-00-00 2X4 No.2 *** B,"12647",1,1GT1,2X4,No.2,"B2",19.8329,1,1,67.4,90 ,0,0,0,0 B,"12647",1,1H3,2X4,No.2,"B5",122.4992,1,1,90,90,0 ,0,0,0 *** Insert 12-00-00 2X4 No.2 *** B,"12647",1,1GT1,2X4,No.2,"B2",19.8329,1,1,67.4,90 ,0,0,0,0 B,"12647",1,1H3,2X4,No.2,"B5",122.4992,1,1,90,90,0 ,0,0,0 Is there a way to do this with Excel? Thanks! Joe... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How would I sort the following?
"Joe Leon" wrote in message
... In an Excel sheet, I have a column that looks like the one below. *** Insert 18-00-00 2X4 No.2 *** T,"12647",1,1GT1,2X4,No.2,"1T1",158.1664,1,1,112.6 ,112.6,0,0,0,0 W,"12647",1,1H2,2X4,No.2,"W6",55.0022,2,1,149.6,82 .2,82.2,0,0,0 *** Insert 12-00-00 2X4 No.2 *** B,"12647",1,1GT1,2X4,No.2,"B2",19.8329,1,1,67.4,90 ,0,0,0,0 B,"12647",1,1H3,2X4,No.2,"B5",122.4992,1,1,90,90,0 ,0,0,0 *** Insert 18-00-00 2X4 No.2 *** T,"12647",1,1GT1,2X4,No.2,"1T1",158.1664,1,1,112.6 ,112.6,0,0,0,0 W,"12647",1,1T1,2X4,No.2,"W1",53.0621,2,1,112.6,67 .4,90,0,0,0 *** Insert 12-00-00 2X4 No.2 *** B,"12647",1,1GT1,2X4,No.2,"B2",19.8329,1,1,67.4,90 ,0,0,0,0 B,"12647",1,1H3,2X4,No.2,"B5",122.4992,1,1,90,90,0 ,0,0,0 *** Insert 18-00-00 2X4 No.2 *** B,"12647",1,1T1,2X4,No.2,"B3",131.9992,1,1,90,90,0 ,0,0,0 W,"12647",1,1H2,2X4,No.2,"W10",11.6247,1,1,67.4,90 ,0,0,0,0 W,"12647",1,1H1,2X4,No.2,"W2",65.7763,2,2,115.2,25 .2,115.2,25.2,0,0 It has a *** Insert ... and below the *** Insert ... there is associated data belonging to the *** Insert ... header. The data following the *** Insert ... header can contain several rows of data belonging to the header before the next *** Insert .... header. What I need to do is to sort the column by the *** Insert ... header but with its associated data below it. Please see example... Data Before Sort: *** Insert 18-00-00 2X4 No.2 *** T,"12647",1,1GT1,2X4,No.2,"1T1",158.1664,1,1,112.6 ,112.6,0,0,0,0 W,"12647",1,1H2,2X4,No.2,"W6",55.0022,2,1,149.6,82 .2,82.2,0,0,0 *** Insert 12-00-00 2X4 No.2 *** B,"12647",1,1GT1,2X4,No.2,"B2",19.8329,1,1,67.4,90 ,0,0,0,0 B,"12647",1,1H3,2X4,No.2,"B5",122.4992,1,1,90,90,0 ,0,0,0 *** Insert 18-00-00 2X4 No.2 *** T,"12647",1,1GT1,2X4,No.2,"1T1",158.1664,1,1,112.6 ,112.6,0,0,0,0 W,"12647",1,1T1,2X4,No.2,"W1",53.0621,2,1,112.6,67 .4,90,0,0,0 *** Insert 12-00-00 2X4 No.2 *** B,"12647",1,1GT1,2X4,No.2,"B2",19.8329,1,1,67.4,90 ,0,0,0,0 B,"12647",1,1H3,2X4,No.2,"B5",122.4992,1,1,90,90,0 ,0,0,0 *** Insert 18-00-00 2X4 No.2 *** B,"12647",1,1T1,2X4,No.2,"B3",131.9992,1,1,90,90,0 ,0,0,0 W,"12647",1,1H2,2X4,No.2,"W10",11.6247,1,1,67.4,90 ,0,0,0,0 W,"12647",1,1H1,2X4,No.2,"W2",65.7763,2,2,115.2,25 .2,115.2,25.2,0,0 Data After Sort: *** Insert 18-00-00 2X4 No.2 *** T,"12647",1,1GT1,2X4,No.2,"1T1",158.1664,1,1,112.6 ,112.6,0,0,0,0 W,"12647",1,1H2,2X4,No.2,"W6",55.0022,2,1,149.6,82 .2,82.2,0,0,0 *** Insert 18-00-00 2X4 No.2 *** T,"12647",1,1GT1,2X4,No.2,"1T1",158.1664,1,1,112.6 ,112.6,0,0,0,0 W,"12647",1,1T1,2X4,No.2,"W1",53.0621,2,1,112.6,67 .4,90,0,0,0 *** Insert 18-00-00 2X4 No.2 *** B,"12647",1,1T1,2X4,No.2,"B3",131.9992,1,1,90,90,0 ,0,0,0 W,"12647",1,1H2,2X4,No.2,"W10",11.6247,1,1,67.4,90 ,0,0,0,0 W,"12647",1,1H1,2X4,No.2,"W2",65.7763,2,2,115.2,25 .2,115.2,25.2,0,0 *** Insert 12-00-00 2X4 No.2 *** B,"12647",1,1GT1,2X4,No.2,"B2",19.8329,1,1,67.4,90 ,0,0,0,0 B,"12647",1,1H3,2X4,No.2,"B5",122.4992,1,1,90,90,0 ,0,0,0 *** Insert 12-00-00 2X4 No.2 *** B,"12647",1,1GT1,2X4,No.2,"B2",19.8329,1,1,67.4,90 ,0,0,0,0 B,"12647",1,1H3,2X4,No.2,"B5",122.4992,1,1,90,90,0 ,0,0,0 Is there a way to do this with Excel? Thanks! Joe... Joe I don't think there's a simple way to do this. If it were me, assuming your data starts in cell A1, I'd write a macro to move the data in A2 to B1, and A3 to C1. Then leave A4 where it is (starts with *** good clear identifier) and move A5 to B4 etc. You'll need to be careful to write a macro that allows for different numbers of data rows. Then simply sort the resultant data by Col A. If you need to reformat the data back to the original layout, then another macro will be needed. Hope this helps. V |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Updating workbook with an alpha sort sheet and a numeric sort shee | Excel Discussion (Misc queries) | |||
| Sort sheet based on particuilar sort order | Excel Worksheet Functions | |||
| sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
| Ascending Sort formula, change to neg #: descending sort.. | Excel Discussion (Misc queries) | |||
| Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
Hybrid Mode
