Merge Multiple Excel Workbooks and Map Headers

[mdrouin]

Hello Everyone,

I've been reading a lot of different posts here and on other forums, as well as crawling Google looking for a viable option for what i'm hoping to accomplish. So far, not a lot of luck. I have about 90 different Workbooks (and for simplicity sake, each only have one worksheet, i think) that I need to merge into one master workbook in a single sheet. Each workbook contains a list of sales leads from a different source/provider. This is what is causing the problem. Each sheet has some of the same data, but it is in different columns with different head row naming. For example, workbook one may have first names labeled as "fname" in column A, while workbook 2 may have it labeled as "first name" in column B, while workbook 3 has it labeled "first" in column R. It is like this in every workbook, and there isn't a whole lot of consistency among them... but there is in some (we may have 15 workbooks provided from one source, so those ones generally match up well, but it's hard to tell by filenames which workbook came from what source). Also, some workbooks may have columns that aren't in any other workbooks (for example, one workbook has a column labeled "date lead captured from 3rd party", which is not relevant to any other column in any other workbook), that i need to keep as well.

Is there any way to merge all the files into one by mapping the header rows to match? Hopefully I made this make sense, and maybe this is an Access job (looked for access solutions too, and couldn't find anything). Or even if anyone knows a program out there that will help accomplish this (even if it's a paid program).

Thank you in advance for any help or insight you guys are able to provide.

EDIT: and to clarify a bit, it's not just one column of data that needs to be mapped. For example, last names, phone numbers, addresses, etc also all are in different column positions with different header labels too, and i need everything of the same data type in the same columns.

[Alf]

Is there any way to merge all the files into one by mapping the header rows to match?

As you state that the head rows are not consistent I must say I've problems with seeing how it is possible to get the data sorted as you say

i need everything of the same data type in the same columns.

The best I can offer is a macro that imports all data from every sheet (only 1 sheet to each file you said) and places it side by side on a sheet in your target file. As Excel 2010 is limited to 16384 columns and you have 90 files there could be a problem if the average column number is more than 180 columns/file.

You need to change the "sPath" and the "sFile" to the folder where your 90 files are stored. As the macro is set to copy everything you may also wish to change the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Alf

[Greg M]

Hi there,

One important question - do all ~90 of your target workbooks have the Header Row located on the same row of the worksheet, or can Header Rows be located on different rows in different workbooks/worksheets?

Also, how do you plan to import the data - will you manually open each workbook in turn and extract its data, or are you hoping for one "big button" which will do everything? Obviously the second approach will be more complicated (but might be possible) than the first!

Let me know and I'll see if I'm able to help you out.

Regards,

Greg M