How would I sort the following?

J

Joe Leon

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...
 
V

Victor Delta

Joe Leon said:
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...
Click to expand...

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
 
Top