Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting a text file imported into excel
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting a text file imported into excel
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 On Aug 6, 12:51*pm, GinaH wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I remove page headings from an imported text file | Excel Discussion (Misc queries) | |||
Formatting Imported Text | Excel Discussion (Misc queries) | |||
Single text file imported to multiple worksheets | Excel Worksheet Functions | |||
imported doc from pdf file, want to seperate text & date in cells | Excel Discussion (Misc queries) | |||
can I display the file name of the imported text file in another c | Excel Discussion (Misc queries) |