Excel: consolidating data

[WillyNelson]

I'm after suggestions for an easy way to consolidate two tables of data.

I have two tables which share the same column structure but some of the rows in one are missing in the other and vise-versa. I want to consolidate them into one table whist retaining the source info, so that if I make any changes to the source it is reflected in the consolidation automatically.

A =vlookup(...)+vlookup(...) would work but there are 52 columns and about 200 rows and I don't really want to write each one manually (the column ref in a vlookup doesn't auto-change when you drag the formula across the range). Any ideas?

Thanks

 

[Munkeh]

Just use a new worksheet with an if statement.

=IF(Sheet1!B22="",Sheet4!B22,Sheet1!B22)

that make sense?

 

[WillyNelson]

It kind of does make sense but I probably wasn't clear in my OP.

Both table have common rows that I want added together as well as some rows exclusive to that table. If it helps, table 1 might have 52 columns of data for A, B, D & E and table 2 has 52 columns of data for A, C & D. I want to add the two tables together.