Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reading in poorly formatted text file
I need to take data from a report in a txt format and enter it into excel.
The portion of the report I'm interested in looks like this: 3532 10:10 12:15 310 13:22 14:19 14:37 15:18 449 2312 09:01 09:32 10:22 11:11 543 13:12 14:20 310 The data I'd need is any row with data in the 4th column but I need to know the number in the first column too. The problem is, the way the data is grouped the rows won't all have data in that first column. In the example above, I'd need to somehow note that the 3rd row is valid data and is associated with the value 3532. And, the last 2 rows are associated with a value of 2312. There are many other rows in the report that I don't care about. If rows 2 and 3 had 3532 in them in that first position and the last 2 rows had 2312 in that first position I'd have no problem.I have no idea how to do this. Anybody else? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reading in poorly formatted text file
I need to take data from a report in a txt format and enter it into
excel. The portion of the report I'm interested in looks like this: 3532 10:10 12:15 310 13:22 14:19 14:37 15:18 449 2312 09:01 09:32 10:22 11:11 543 13:12 14:20 310 The data I'd need is any row with data in the 4th column but I need to know the number in the first column too. The problem is, the way the data is grouped the rows won't all have data in that first column. In the example above, I'd need to somehow note that the 3rd row is valid data and is associated with the value 3532. And, the last 2 rows are associated with a value of 2312. There are many other rows in the report that I don't care about. If rows 2 and 3 had 3532 in them in that first position and the last 2 rows had 2312 in that first position I'd have no problem. I have no idea how to do this. Anybody else? Here's one way. With your data in columns A-D, enter these formulas. In E1: =A1 In E2: =IF(A2="",E1,A2) Then copy E2 down as far as needed. Then you can select column D and use Data Filter Autofilter (Nonblanks) to view only the rows you care about. The associated first-column value is in column E. (Hiding column A might avoid confusion.) Adapt to suit your needs. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert a month to a quarter ...... | New Users to Excel | |||
Copy from a Cell to a text box. | Excel Worksheet Functions | |||
need to export row as csv file with column e plus .bom as file name | Excel Discussion (Misc queries) | |||
Text File | Excel Discussion (Misc queries) | |||
Changing a comma separated text file and save it. | Excel Discussion (Misc queries) |