Combining Multiple Excel Spreadsheets

[Mamacat104]

Good morning
I'm trying to help a friend combine several spread sheets .... I've read many things online about merging but so far nothing I've found sounds like it will work with her reports..
her reports are different information about her customer base... So for most of the reports, Column A is the names of the customers... so doing copy and paste would not work to add the columns from other spreadsheets because customers are added or customers are removed so just doing a copy paste would not work... so we need to match the column of the customer names and then apply the new column information so the customer matches.. (Hope I am explaining this right)...
Sheet 1 might be address and phone information, contact person, etc... column a is customer names, column B address, column c phone number, column d might be comments added, etc...
Sheet 2 might be qty of inventory they have, column for open orders, column for last date of inventory done, etc... column a being customer names, column b maybe open order number, column c maybe date of last inventory, column d maybe qty of inventory...

I'm not sure what all her information is on all her sheets but the customer name is in Column A on each one... so like I would need to merge sheet 2 into sheet 1, matching the customers and adding columns b-? to Sheet 1 beginning with the next available column... so it would be adding columns of information... and when a customer didn't match (because maybe a new customer had been entered... this would just return a N/A or just leave blank.... for that customer) If a customer didn't match because a customer was removed from the master sheet 1, it ignored and moved to the next matching customer..

What would be the easiest way to merge these two sheets.. I do a lot in Excel but have never had to do this...

thank you
Barbara
Have a good day

 

[pdevito3]

One option:

1. save as a new workbook so you don't mess it up!

2. add each column from sheet 2 to sheet 1... column a is customer names, column B address, column c phone number, column d might be comments added, column e being customer names, column f maybe open order number, column g maybe date of last inventory, column h maybe qty of inventory... etc

3. in each column add a vlookup formula, kind of like this:

=IF(A1="","",IFERROR(VLOOKUP(A1,Sheet2!$A$1:$B$2,2,FALSE),"name not found"))

Note: A1 is the cell for the customer name. Sheet2!$A$1:$B$2 is the entire table on the second sheet. Make sure to use dollar signs or a named range to keep it from changing. The "2" next to that is the column of the range you established that you want to return. For example, date of last inventory would be "7" in the example above. customer name must be the leftmost column for this to work. if the data isn't setup like this and index/match function can be used. Also, note that this will return a null value if the customer name is blank and "name not found" if there is not a match.

4.Once all the vlookups have been established, copy the entire first sheet and paste special values.

 

[Mamacat104]

thank you.. will give her the information.. appreciate quick reply... she has about 15 sheets to merge...

 

[pdevito3]

Less ideal. We could probably get some VBA together to handle it if needed, but we can cross that bridge later.

 

[Mamacat104]

I gave her the information and she is going to try the VLookup .. appreciate your help... I haven't worked with the excel like this in several years now.. I used to rewrite macros to do what I wanted but been long time and I usually have to have one to start with .... I used to write them that would go on the computer and pull information from the computer and put onto the Excel ... I just rewrote a macro for her that she can run and it will take columns of info for her customer and put it on a Word doc, then save the doc as the customer name... finally got it working.. I would like to get it to create hyperlink on the excel form same time it creates the word doc but haven't figured that one out yet... I had two macros I was using to do my rewrite... their wording is different so working on getting it to work ... may not... back to work.. thank you for info...
have a good day
Barbara