formatting a text file imported into excel

[GinaH]

I would consider my self an intermediate user of excel, and I am just not
sure what the fastest way of formatting my text file would be.

I imported a text file into excel and now it needs some manipulation. I
know how to do most, but I am stuck on how to get my item number linked to
the appropriate data. See below.

Item: 12345
1015839 37315 12.83 12.33 .50

1015668 37315 64.17 61.67 2.50

1015661 37315 38.50 37.00 1.50

Item: 67890
1000495 13732 22.36 20.86 1.50

1000639 37233 22.36 20.86 1.50

1000339 37239 22.27- 20.77- 1.50-

1000686 37246 22.36 20.86 1.50


I need to link the item number to each line in its group so then I can sort
by item and then I can go on from there and use the data and create a pivot
table to get my answers. I just would like to know if there is something
quicker than cutting a pasting the item number next to each line that doesn't
have the item number? I have 177 items and 16,000 rows of data, and 5 more
reports like this to run. I am waiting on our IT to redo the reports itself
so it will include the item number in each line, but who knows when that will
happen. Please help if you can or give me some advice. Thanks. Sorry if I
am not using the correct terminology.

 

[Ken]

Gina

I think this process should work for you.

1. Insert two new columns just to the left of your data (I'll assume
it is column A and B).

2. Fill in column A with the numbers 1 through 16,000 (use the ROW
function and convert to values or some other fill functionality)

3. Put =B1 in cell A1 and copy it down your 16,000 rows.

4. Sort by column D. I believe D should be blank except for the rows
that have the item numbers.

5. Delete the formulas in column B for all the rows that have data
NOT item numbers in colum C.

6. Sort the data by column A to get back to the original order.
Column B should have the item number on the item number rows, and be
blank for the rest.

7. Highlight column B

8. Hit F5 (GoTo); then select Special, then select Blanks. You
should now have all the blanks activated.

9.In cell B2 (or the first data row for the first item put in the
formula =B1; then hit Control Enter to put the formula in all the
blank cells at once.

10. Convert column B to values

11. You may want to sort again by column D to get all the item numbers
together and delete them now that you have the data the way you wanted
it.

Good luck

Ken
Norfolk, Va