PDA

View Full Version : [SOLVED] Merge Excel files with different yet similar data headers



RandiJ
03-10-2016, 11:03 AM
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
03-11-2016, 11:42 PM
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
03-14-2016, 11:33 AM
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.


15627

excelliot
03-15-2016, 03:45 AM
hi can you attach an excel file with all sample sheet

RandiJ
03-15-2016, 10:46 AM
See attached sample data.

Thank you for reviewing.

excelliot
03-16-2016, 01:26 AM
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
03-17-2016, 01:51 AM
mark this thread as completed if your query is resolved..

RandiJ
03-17-2016, 03:08 PM
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
03-17-2016, 07:13 PM
Yes heading should match in 3 sheets.

RandiJ
03-17-2016, 08:00 PM
Thank you very much! I got the macro to work.

Much appreciated.

excelliot
03-17-2016, 09:32 PM
Thank you very much! I got the macro to work.

Much appreciated.
Cheers!!