Merge Excel files with different yet similar data headers

[RandiJ]

Hello,
I have two excel files that I am trying to merge. I've looked at various code examples but they don't seem to work with my issue.

File 1 has the following headers: (this is also my desired output)

Ledger Group

Year

Period

Fund Code

Organization

Description

Program

BOC

Pre Encum Amount

Encumbrance Amount

Expenses Amount

File 2 has the following headers:

Show Budget Type

Ledger Group

Account

Fund

Org

Description

Program

BOC

BFY

LY

Fiscal Year

Period

Budget

Expense

Encumbrance

Pre-Encumbrance

Avail Budget

Percent Avail

I only need the following from File 2:

Ledger Group

Fiscal Year

Period

Fund

Org

Description

Program

BOC

Pre-Encumbrance

Encumbrance

Expenses

As you can see, only Ledger Group, Period, Description, Program, and BOC are similar but I need to merge the data using the headings from File 1 in the merged file.

Is is possible to get the desired result using VBA Code?

Thank you for any help received.

[excelliot]

Hi

What do you want to do? do you want to put data side by side in File1 or take data from File1 & File2 in merged sheet. When there is common heading which data to consider?

If possible share sample data.

[RandiJ]

Hi,

I would like to append the data from File 2 onto the data from File 1. I've attached a sample final output image below. File 1 is the ACTUALS data and File 2 is the DETAILS data.


SampleData Image.JPG

[excelliot]

hi can you attach an excel file with all sample sheet

[RandiJ]

See attached sample data.

Thank you for reviewing.

[excelliot]

Hi,

Check attachment, go to last sheet, and run a macro. (Enable macro in workbook, Press Alt + F8 to call macro, Select macro1 & press run)

Caution, Macro will first delete existing data from sheet and then copy paste data from sheet 1 & sheet 2, also make sure heading in both sheet are matching with heading in last sheet, if they are not same no data will be copied.

Cheers!!

[excelliot]

mark this thread as completed if your query is resolved..

[RandiJ]

Hello,

Sorry for the delayed response but I could not get the macro to work as intended. In your attachment on Sheet 3 it seems that the data from File 1 did copy over but not all of the data from File 2.

You stated the following: "make sure heading in both sheet are matching with heading in last sheet, if they are not same no data will be copied." Does this mean that I should pre-populate the third sheet with the desired headings before running the macro? Also, the headings in File 1 and File 2 don't match when the data is originally retrieved for their respective databases. Does that mean I also have to, for example, change the headings in File 2 to match the desired output headings, which will be the same as File 1 by default?

Thank you.

[excelliot]

Yes heading should match in 3 sheets.

[RandiJ]

Thank you very much! I got the macro to work.

Much appreciated.

[excelliot]

Thank you very much! I got the macro to work.

Much appreciated.

Cheers!!