Merge multiple worksheet to summary worksheet based on column header value match

Pls help with a macro to - Merge multiple excel files in a folder - to a new summary worksheet based on column header value match. The column in the excel file are not in same order. In the below example the Phone header is in Column c in Workbook1-sheet 1 whereas the Phone header is in Colum B in Workbook2-sheet 1

Workbook1-sheet 1
ColA ColB ColC
Name Nickname Phone
John Jo 9841

Workbook2-sheet 1
ColA ColB
Name Phone
Mathew 9840


Result - new summary worksheet
ColA ColB ColC
Name Nickname Phone
John Jo 9841
Mathew empty 9840


Thank you

The Macro has to create the Summary worksheet with headers.

The exact header value's are given below.
I found one more issue while copying the exact header name. There are two columns headers(Ft/Pt) with the same name. Is it possible to treat it as two separate columns only for the value (Ft/Pt).

S.No
Date
Phonenumber
Surname
Husband's name
Wife Name
Age
Occupation
Ft/Pt
Partner's Occupation
Ft/Pt
Income
Home Status
Prop Value
Mortage
No of IP
IP Value
No of years own IP
Address
Suburb
Post code
State
Survey Comments

Missed the below two headers in other excel file

DISPOSITION
Mobile number

The two columns Ft/Pt are always after Occupation and Partner's Occupation. Will this condition work?

Any Idea on how to merge excel files based on column header value match?
Can anyone point me to a pointer where I can start merging based on column header value match.

Yes DISPOSITION and Mobile number are part of the summary file.

Attached 2 sample files.

Columns will either have data or it will be empty.

yes and even csv format

Can anyone guide me to merge excel files based on column header value match. Need to merge multiple excel files based on column header.
Has anyone done this already?

I found a code and modified according to the below requirement - Merge multiple excel files in a folder - to a new summary worksheet. The Macro has to create the Summary worksheet with headers.

Workbook1-sheet 1
ColA ColB ColC
Name Nickname Phone
John Jo 9841

Workbook2-sheet 1
ColA ColB
Name Phone
Mathew 9840


Result - new summary worksheet
ColA ColB ColC
Name Nickname Phone
John Jo 9841
Mathew empty 9840

Requirement:
Pls help to update the below code for the below features

Want to update the summary sheet column 2 if the excel file column heading is "S.No"
Want to update the summary sheet column 3 if the excel file column heading is "Date"

Can you pls guide me to update the code

Please Login or Register  to view this content.