|
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... |
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 |
| All times are GMT +1. The time now is 03:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com