Sorting a single column text file
Posted by Tim on February 08, 2001 1:12 PM
I have a text file over 4000 rows long when imported
into excel. each 'section' of the text file starts
with a @ and ends with a /, but each 'section' varies
in the number of rows. I need to sort the data into
columns.
I can mark the start and end of a 'section' using
nested IF function using SEARCH, but I can't sort the
data.
Please help me!
Tim
Posted by Mark W. on February 08, 2001 1:20 PM
Care to provide some representative, sample data and
the desired appearance of same data once sorted?
Posted by Tim on February 08, 2001 1:35 PM
Wow, that was quick! Data is below.
Thanks, Tim
Raw Data
sy:41482xb_XCe.obc[01-SEP-00
@01-SEP-00 06:46:04
SERIAL NUMBER = 41482002320
C842>=PG-36C2
Test Nails=3913,3202
C920<=PG-31C6
Test Nails=3913,6026
CAP_P5V>=C1,C22,C130,C139,++
U130%=72825_BG121_U130=PG-56
U131%=72825_BG121_U130=PG-56
/01-SEP-00 06:49:21
@01-SEP-00 06:50:11
SERIAL NUMBER = 41482002373
C25>=PG-46A6
Test Nails=3295,3223
U28%=IOVUIBLANK2=PG-52D4
FAILURE AT PIN 18
/01-SEP-00 06:52:12
@01-SEP-00 10:44:48
SERIAL NUMBER = 41482002385
C920<=PG-31C6
Test Nails=3913,6026
Q5_3<=PG-29A3,44A2
Test Nails=3913,3055
Q5_1<=PG-29A3,44A2
Q5_2<=PG-29A3,44A2
U71_TST>
U168_A<=PG-44C6
U212_A<=PG-43C6
U122_A_1%=PG-44C
U69_TST>
U70_TST>
/01-SEP-00 10:46:47
All 'sections' start with @<DATE> and end with
/<date>. Any blank lines need to be removed,
and the @<date> should be on the same row, with
the data in the same column.
i.e.
Column 1
@01-SEP-00 06:46:04
SERIAL NUMBER = 41482002320
C842>=PG-36C2
Test Nails=3913,3202
C920<=PG-31C6
Test Nails=3913,6026
CAP_P5V>=C1,C22,C130,C139,++
U130%=72825_BG121_U130=PG-56
U131%=72825_BG121_U130=PG-56
/01-SEP-00 06:49:21
Column 2
@01-SEP-00 06:50:11
SERIAL NUMBER = 41482002373
C25>=PG-46A6
Test Nails=3295,3223
U28%=IOVUIBLANK2=PG-52D4
FAILURE AT PIN 18
/01-SEP-00 06:52:12
Column 3
@01-SEP-00 10:44:48
SERIAL NUMBER = 41482002385
C920<=PG-31C6
Test Nails=3913,6026
Q5_3<=PG-29A3,44A2
Test Nails=3913,3055
Q5_1<=PG-29A3,44A2
Q5_2<=PG-29A3,44A2
U71_TST>
U168_A<=PG-44C6
U212_A<=PG-43C6
U122_A_1%=PG-44C4
U69_TST>
U70_TST>
/01-SEP-00 10:46:47
Posted by Mark W. on February 08, 2001 2:49 PM
Tim, here's one approach which could be developed into
a macro:
1. With your data beginning in cell A1 insert a new
column A:A and a new row 1:1.
2. Apply an AutoFilter to your list (now in column B:B)
that selects all blank rows.
3. Select all of the visible, blank rows (with a blue
row number), delete them, and then remove the AutoFilter.
4. Enter the formula, =A1+(LEFT(B2)="@"), into cell A2
and copy down.
5. Enter 1, 2, 3 into cells C1:E1.
6. Enter the formula, =IF($A2=C$1,$B:$B,#N/A), into cell
C2 and copy down and then fill right so that all rows
beyond 2:2 and columns C:E contain this formula.
7. Copy/Paste Special... Values for columns C:E
8. With columns C:E selected do a Edit Go To... Special...
choosing the "Constants" radio button and uncheck all but
the "Errors" check box.
9. Perform an Edit Delete... and choose the "Shift cells
up" radio button.
10. And, finally, with columns C:E selected choose the
Data Sort... menu command; click the Options... button;
choose the "Sort left to right" orientation and press OK;
and then sort by Row 2 in Ascending order.
