Combing Excel Files with Different Number of Fields

Hi, I am looking to combine a folder of 20+ excel files that have slightly different number of fields. I tried using a wildcard, but since the first file has 20 fields and the third file has 21 fields, that third file doesn't get aggregated.

What tool should I be using to combine these files so that I get one aggregated file with the max number of fields (and for the files without those fields it displays a blank)?

Also, is there a logic I can put in place to remove a row based on the content in the leftmost column? As an example, each file has a "Total" row that I don't need.

Thanks!

Input

Macros

Accepted answers

Hi @CBA ,

I've attached an example workflow and the macros you need.

This will load in all sheets from all workbooks.

Save the .yxmc files in your macro folder then open the workflow.

I hope this helps,

M.

Dynamic Multi File Sheet Names.yxmc

Import Multiple Schema xlsx.yxmc

Multi Sheet Import Example.yxmd

All comments

Hi @CBA ,

I've attached an example workflow and the macros you need.

This will load in all sheets from all workbooks.

Save the .yxmc files in your macro folder then open the workflow.

I hope this helps,

M.

Dynamic Multi File Sheet Names.yxmc

Import Multiple Schema xlsx.yxmc

Multi Sheet Import Example.yxmd

@mceleavey, this is perfect, and then I will incorporate some logic to clean up this aggregated file.

Really helpful, I appreciate it!

Quick Links

Tags

This months top contributors

atcodedog05 19458

Qiu 15866

MarqueeCrew 13708

apathetichell 13703