Best way to combine two excel worksheets

[rohankekatpure1987]

I've the below two excel worksheets Sales and delivery sheet. Both worksheets contain 1000 -1500 rows and 40 columns. I need to combine both these worksheets in to a new worksheet " Worksheet 3" which contains combined data from these worksheets. In the below example I've given only 3 columns, but since the number of rows and columns is high, plus some missing entries, using vlookup, index, match is becoming a challenge. Can you suggest what is the best method of combining these work-sheets? Would automating by VBA work ? How to do that ?

Expected Output: "Worksheet 3" = 1) Sales Sheet + 2) Delivery Sheet :

[TABLE="width: 465"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD="align: center"]Items[/TD]
[TD="colspan: 2, align: center"]18-Jun-16[/TD]
[TD="colspan: 2, align: center"]19-Jun-16[/TD]
[TD="colspan: 2, align: center"]20-Jun-16[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Sale [/TD]
[TD="align: center"]Delivery[/TD]
[TD="align: center"]Sale [/TD]
[TD="align: center"]Delivery[/TD]
[TD="align: center"]Sale [/TD]
[TD="align: center"]Delivery[/TD]
[/TR]
[TR]
[TD="align: center"]Dog[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Cat[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD="align: center"]Mouse [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]19[/TD]
[/TR]
[TR]
[TD="align: center"]Cheese [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



1) Sales Sheet:

[TABLE="width: 268"]
<colgroup><col style="text-align: center;"><col span="3" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Items[/TD]
[TD="align: center"]6/18/2016[/TD]
[TD="align: center"]6/19/2016[/TD]
[TD="align: center"]6/20/2016[/TD]
[/TR]
[TR]
[TD="align: center"]Dog[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]Cat[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Mouse [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]Cheese [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]

2) Delivery Sheet:

[TABLE="width: 268"]
<colgroup><col style="text-align: center;"><col span="3" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Items[/TD]
[TD="align: center"]6/18/2016[/TD]
[TD="align: center"]6/19/2016[/TD]
[TD="align: center"]6/20/2016[/TD]
[/TR]
[TR]
[TD="align: center"]Dog[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Cat[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]04[/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD="align: center"]Mouse [/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]19[/TD]
[/TR]
[TR]
[TD="align: center"]

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[bhos123]

The easier way would be create the column titles as per your output, put sheetnames in row 1 for each columns, use INDIRECT function to refer sheetnames and Range and Match function to refer the cell references.