Running macros to combine excel sheets

[alijalil]

I'm trying to merge multiple excel sheets which don't have the same format (some start at column B some at C and so on).

The output sheet shows the results for some columns in new columns (listed under sheet name) and some in the same columns as the first sheet. Any way this can be resolved? TIA! 

[ShankerV]

Hi @alijalil 

I think you have different schema in the excel sheet you are trying to read the inputs.

Are they in the same excel under different sheet or in multiple excel sheets.

Many thanks

Shanker V

[alijalil]

@ShankerV yes, they do have different schema. All the excel sheets are in the same file.

[alijalil]

The columns that should appear under Column F2 are appearing under the Corporate Affairs Office column. The IMS sheet starts at Column B whereas the Corporate Affairs sheet starts at Column C. Also, the Purchasing sheet appears before Corporate Affairs sheet in the file.

[KSowers]

Give this macro a shot. If you provide the full list of file names, with excel tab name included, this should go through and read them all and concatenate them based on the column name. I have it checked so the Schema difference shouldn't error out. Let me know if this works. 

[ShankerV]

Hi @alijalil 

Please find the attached workflow with macro.

Hope this will resolve your issue. 

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V