Merging Excel worksheets

[Alexxx]

Sponsored Links

I am working for an organization as a data operator where i need to work on MS office for lots of time. there is two excel files which i required to merge so that i can get what i want. so , is there any way to do that?

[Arzaan]

This is the good tip which you must know. There are some ways to do that but the simplest way i know is by VLOOKUP. It is an formula based function you can operate.To do so,follow this steps:-

One way of doing it is by using the VLOOKUP formula. In your case, I would use an approach like the following:


1. Make a backup copy of the workbook before starting.

2. Choose one worksheet as the master, that will receive information from the other worksheet.

3. Identify the columns on the other worksheet that you want to move to the master worksheet, and note down the column number for each data. Ensure that the ID number is the leftmost column of the data range in that worksheet.

4.On the master sheet, use a formula like this for each data that you want to migrate:
=VLOOKUP($A2,Sheet2!$A$1:$Z:$1000,5,FALSE)
Where the parameters are:

• $A2: The reference to cell where the number ID resides in the master sheet. I have used an absolute reference in the column, so you can use the same formula for different target columns. Regarding the row, this will point to row 2, so the formula should be entered in that row.
  
• Sheet2!$A$1:$Z:$1000: The whole range where the data to be migrated resided. Change it as appropriate.
  
• 5: The column of the data that you want to migrate, as identified in the third step of the process. Change it for every column you want to migrate.
  
• FALSE: Determines the behavior of the VLOOKUP formula, just keep it this way all the time.

5. I would do one row, change the third parameter of the formula for each different value to migrate, and check that everything is OK. After that, you can copy the formulas in all the range in the master sheet.

Hope you are done !!

[O'Brian]

If you are working as a data operator, its often you need to merge several worksheets into one.The best way to do that is VLOOKUP but its true that its difficult for new users.

So you can try a software made for this task, its Merge Tables Wizard for Microsoft Excel, it will help you to merge books.

Merge Tables Wizard for Microsoft Excel makes finding and copying matching data in Microsoft Excel worksheets a breeze. This handy vlookup add-in quickly merges two Microsoft Excel tables into one by matching values in corresponding columns.

The two tables can be located in the same Microsoft Excel file or in two different Microsoft Excel files. The intuitive and easy-to-understand wizard will guide you through the merge process. Rows from the two tables with different number of columns will be matched perfectly without copying and pasting.

i guess you'll get the result you expected.

[Kraker999]

You can try this steps to merge two books in excel simply:-

1. Open your base copy of the workbook you distributed.

2. Open the Tools menu and select Merge Workbooks.

3. Save the shared workbook if you are prompted.

4. In the box that appears, select one of the copies you made of the workbook.

5. Click OK.

6. Repeats steps 2 through 4 until all copies of the shared workbooks are merged.