combine two sheets, summing the matching column/row quantities and adding in new any columns and rows that only appear on one of the sheets

[Lisa Harris]

Hi,

I have been frantically trying different methods and searching for help for a while to complete this, but unable to find a solution that allows you to combine sheets if column headers do not remain constant.

I need a macro that will produce a summary sheet combining an initial despatch log ('Before' tab) with a store orders / back orders log post despatch ('after' tab), so we can quickly view the total amount each one had of each item in the end. However, as time goes on, columns and rows get added in to a master (inserted as well as added to the end) so the 'after' sheet is likely to have additional columns (items) or Rows (stores) to the 'before' sheet and in a different order where new columns have been inserted as a result.

Is there VBA that will combine the two sheets, adding in the new 'unique' columns and rows in the correct order where necessary? then looking up the values and summing?

Any help would be much appreciated - thanks in advance


example of Before sheet:


[TABLE="width: 722"]
<colgroup><col span="4"><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]shop no[/TD]
[TD]shop name[/TD]
[TD]type[/TD]
[TD]new format?[/TD]
[TD]limes[/TD]
[TD]apples[/TD]
[TD]bananas[/TD]
[TD]pears[/TD]
[TD]grapes[/TD]
[TD]oranges[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]store A[/TD]
[TD]type 1[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]65[/TD]
[TD]Store B[/TD]
[TD]type 2[/TD]
[TD]N[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]77[/TD]
[TD]Store C[/TD]
[TD]type 1[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]896[/TD]
[TD]Store D[/TD]
[TD]type 1[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]43[/TD]
[TD]Store E[/TD]
[TD]type 1[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]256[/TD]
[TD]Store F[/TD]
[TD]type 2[/TD]
[TD]N[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]56[/TD]
[TD]Store G[/TD]
[TD]type 1[/TD]
[TD]N[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]432[/TD]
[TD]Store H[/TD]
[TD]type 1[/TD]
[TD]Y[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]32[/TD]
[TD]Store I[/TD]
[TD]type 1[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


example of After sheet:

[TABLE="width: 931"]
<colgroup><col span="4"><col><col span="6"><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]shop no[/TD]
[TD]shop name[/TD]
[TD]type[/TD]
[TD]new format?[/TD]
[TD]limes[/TD]
[TD]lemons[/TD]
[TD]apples[/TD]
[TD]bananas[/TD]
[TD]pears1[/TD]
[TD]pears2[/TD]
[TD]TOTAL pears[/TD]
[TD]grapes[/TD]
[TD]oranges[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]store A[/TD]
[TD]type 1[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]65[/TD]
[TD]Store B[/TD]
[TD]type 2[/TD]
[TD]N[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]77[/TD]
[TD]Store C[/TD]
[TD]type 1[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]896[/TD]
[TD]Store D[/TD]
[TD]type 1[/TD]
[TD]Y[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]43[/TD]
[TD]Store E[/TD]
[TD]type 1[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]256[/TD]
[TD]Store F[/TD]
[TD]type 2[/TD]
[TD]N[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]56[/TD]
[TD]Store G[/TD]
[TD]type 1[/TD]
[TD]N[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]432[/TD]
[TD]Store H[/TD]
[TD]type 1[/TD]
[TD]Y[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]32[/TD]
[TD]Store I[/TD]
[TD]type 1[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]