merge and combine in excel

Question

I have two sheets in excel, in both of them, I have a column a as ID but ID in both sheets are not the same. both of them have some ID number that does not exist in another. there are several column else in both sheets. by the way, I want to merge and combine these two sheets. Till now I Used some Add-ins like DIGdb or ablebits as trial. I can not buy these add-ins or software from my country. It is your kind to help me.

Thanks a lot Arash

Answer 1

You have multiple options how to combine worksheets:

1. By using Consolidate function of MS Excel, well covered in this answer: Merge Excel rows from two sheets

2. Excel Query Wizard - probably the most universal solution, which allows joining data from various data sources. You could use following steps:

   • Define names for both datasets that you want to merge (Formulas tab -> Define name)
   • Go to Data tab, select "From Other Sources", and from the dropdown, select "From Microsoft Query"
   • Select your workbook file and confirm that you want to merge the columns manually
   • In the following window "Query from Excel Files", drag&drop the ID column of first dataset into the ID column of second dataset - a link between these columns will be created
   • Go to File menu, click "Return Data to MS Office Excel", an Import Data dialog will pop up
   • Select the sheet into which you would like the matched data to be imported
   • Click OK -> you should see matched data

3. If your spreadsheets use specific structure, you can try INDEX MATCH or VLOOKUP functions to lookup and match values in certain data ranges. You'll find plenty of resources on this topic, including official MS Office documentation.

4. If you don't mind uploading your workbook to an online service, you can use for example http://www.gridoc.com/join-tables and merge the sheets using drag&drop (Disclaimer: I am author of the tool)

Hope this helps.