2

I have data from a text file I want to import into Excel. The text data is grouped in blocks of 40 rows with headings. After about 12 rows the new set of data starts again with headings. There are about 700k rows all up.

If I were to use Data Validation -> Fixed Width, this works, but then every 40-50 rows there is about 10 rows of garbage i need to exclude. This would take me a long time to filter out with over 700k rows all up.

Is there a faster way to do this? sample data - blurred

AdamV
  • 6,396
Nathan Schofield
  • 21
  • 2

1 Answers1

0

This is just a basic INDEX problem, with 11 for the columns parameter. The tricky part is the row parameter.

Building the row parameter can be done like so:

=TOCOL(SEQUENCE(1,D1,C1,54)+SEQUENCE(41),,TRUE)

Judging from the portion I can see in your pic, and assuming the data, once started, is in 54 row blocks, 41 data rows and 13 rows of what I picture as page headers, you can fill in cells C1 and D1 with known information. C1 takes the starting row of the last row of "not-data" before the first line of actual data. Lots of alternatives to that. In the pic, one can just hardcode "44" but perhaps it changes and one would like to specify it so I showed doing so.

The idea is to generate a sequence of starting points and add to each 1...41 to get the subsequences. One could enter the last row number for actual data, from inspection or with cleverness, and calculate how many subsequences to put into the generated overall sequence, thereby not getting a hundred thousand rows of errors.

To do so, take the number of rows of data in each block, plus the rows of the page header blocks, and multiply that sum by how many blocks/pages. Finally, add the starting rows that are not part of the first block. That looks like 31 rows. Then the overall sequence generated will draw out the number of rows actually needed. Or, just enter such a formula in D1 and have it reference E1 which would have the number of total rows in the data. Lots of alternatives there too. Could even simplify it a lot by just entering the data range as starting with the first row of the "page header/data" blocks. Lots of choices.

In any case, a 2-D array of rows and columns is created holding the overall sequence values. Not useful to INDEX. But TOCOL takes care of that, producing the "1-by" columnar array of row numbers for INDEX to use.

Jeorje
  • 11