Macro to consolidate data from 4 different excel sheets into a single summary sheet

Hello Everyone,

I have 4 Excel sheets that are used by individual team members to input information. All 4 sheets have identical headers and the data is filled progressively. For example, if Person A has met a customer, he/she will update the sheet post the meeting. Similarly for the rest.

I have a consolidated summary sheet that has same headers as the individual sheets. I need a Macro program that can consolidate data from all the 4 individual sheets as and when they are updated. So the condition here is, that the data will be unique and pasted in different rows of the summary sheet as and when data gets filled in the 4 individual sheets. The macro should identify the unique records every single time and update the same in the summary sheet.

I hope you got the problem I am trying to portray.

Please help me to solve this issue. Thanks in Advance!

Thanks,
Varhead

Hi Akhilesh,

Thanks for the add-in! I forgot to mention that I use 2010 version of MS Excel. Apologies! Do you have an add-in for this version?

Ron's code does not deal with unique values.
Please attach your sample.

Sure, I have attached the file.

The headers will be the same in Individual sheets and Consolidated Sheet. The four individuals will input different customer details they have met and all of them will be unique in their nature i.e different customer data.

You have 4 sheets and yet you posted a book with a single sheet and no data on it.

Sorry, i didn't make it clear to you.

I have added one row of data in each of Individual Sheets (1, 2, 3 & 4). They need to be consolidated in the Consolidated.xls sheet so that i have 4 rows filled up with 4 unique data collected by these 4 individuals.

Are these sheets on the same book? If they are not, I suggest you should use the link above. Once all cells are consolidated in to a single sheet, you can delete the duplicates.

Hi AB, the sheets are on five different excel files like 4 Individual files + 1 Consolidated file. Sorry, I can't see any link. Could you please give me the link?

http://www.rondebruin.nl/win/addins/rdbmerge.htm

Hmmmm... this won't work because every time i run this, new data will overwrite the existing one. I need data from all the four workbooks to be stacked one below the other after they have been entered into 4 individual sheets.

I have attached an image description of how a Consolidated sheet will look after data from all 4 individual workbooks is collated. Have provided comments alongside each entry for your understanding.

Kindly help!

Which one will not work? Which code did you try?

I tried RDBMerge

I will write you a new code. Are all the books on the same folder? On the picture, it appears you consolidating 4 columns only, but on the sample books, you have more columns, which one is correct?

The books are in a single folder.

Please ignore the image, as it was just a representation of the fact that i need the data to be collated in the consolidated sheet as and when they are entered into individual sheets by different individuals.

From the Individual sheets 1, 2, 3 & 4, i need to capture data from the range B:R (eg, B1:R1 etc.) to the range B:R of Consolidated workbook.

Thanks,
Varhead

Try the attached on your actual sheet, but you need to change the path to suit your needs.
Once we got all your data in one sheet, we need another code to get rid of the duplicates.

Hi AB, the Macro worked. I changed the path and ran the program; it worked perfectly. I will update more information in Individual sheets and import them into consolidated.xlsm. lemme see how it goes. Thanks a ton Will get back to you with the results

Hi AB, thank you very much! The macro works like a breeze. I am very much grateful to you for your help!

Hi AB, just a doubt. Are there any changes required in the Macro if i want to run this macro in a network location? I placed the documents (4 Individual + 1 Consolidated) in a network location and opened the Consolidated.xlsm sheet in edit mode from IE browser. Ran the macro but it didn't work. Before doing this, i changed the path in the program to the network path, but it didn't work.

Please assist!

Thanks,
Varhead

I am not sure if Dir path works on shared network file.