Combining 3+ excel files into one with different data starting points

[camalloy]

Hello All,

I'm trying to find a way to combine multiple (3+) excel files into one. These excel files are auto-generated once a week by an excel plugin.

The issue is the first 11 rows are all information about the weekly data pull request, I would need to keep this data (same across all files) but I would only need to keep it from one of the files and skip it in the other files.

The end goal here is to combine all excel workbooks into one, skipping the first 11 rows on every file except the first file. 

Best,

[MarqueeCrew · Accepted solution]

When reading the file(s), you can use the configuration option #5 (Output File Name as Field) and set it to File Name Only.  Union the data together and then you have options as to how to keep the first set of rows on the first file.  here is one way:

use a multirow formula:

Create RowID (by filename) 

Groupby Filename and current row ROWID is row-1:RowID + 1

use another multirow formula:

Create Filenum variable

If row-1:filename = current row:filename then row-1 filenum else row-1:filenum+1

Then you can filter the data:

RowID > 11 or Filenum == 1

That will get you only the rows that you're looking for.

Cheers,

Mark