Combining multiple table data

[hro5e]

Hello all,

I have a table like so called SEASON 1:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Player[/TD]
[TD]Appearances[/TD]
[TD]Goals[/TD]
[/TR]
[TR]
[TD]J. Hart[/TD]
[TD]9[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]V. Kompany[/TD]
[TD]9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Y. Toure[/TD]
[TD]10[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]E. Dzeko[/TD]
[TD]10[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]S. Aguero[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]S. Nasri[/TD]
[TD]9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D. Silva[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]G. Clichy[/TD]
[TD]9[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]J. Milner[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Now I have another table called SEASON 2:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Player[/TD]
[TD]Appearances[/TD]
[TD]Goals[/TD]
[/TR]
[TR]
[TD]J. Hart[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]V. Kompany[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Y. Toure[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]E. Dzeko[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]S. Aguero[/TD]
[TD]7[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]S. Nasri[/TD]
[TD]6[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D. Silva[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]G. Clichy[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]W. Rooney[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

The difference between these tables is that the player J. Milner has been replaced by W. Rooney but they are both two separate tables.

Now what I want to do is combine both tables into a 'HISTORY/RECORDS' table so this third table will grab all data from both SEASON 1 & SEASON 2 tables and combine them, so the third table should have these results:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Player[/TD]
[TD]Appearances[/TD]
[TD]Goals[/TD]
[/TR]
[TR]
[TD]J. Hart[/TD]
[TD]14[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]V. Kompany[/TD]
[TD]12[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Y. Toure[/TD]
[TD]15[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]E. Dzeko[/TD]
[TD]17[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]S. Aguero[/TD]
[TD]15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]S. Nasri[/TD]
[TD]14[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]D. Silva[/TD]
[TD]14[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]G. Clichy[/TD]
[TD]13[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]J. Milner[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]W. Rooney[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Notice that the third table has "summed" all matching data but all kept the data removed from 'SEASON 1' (J. Milner) and added the data from table 'SEASON 2' (W. Rooney).

Please ask if i haven't explained myself well enough.

I am looking for pure formulas if possible no VB.

Looking forward to your responses.

Thanks

 

[Rastaman]

But VB would be so much easier...

Here's a crack at it. If Season 1 is on Sheet2 starting in A1 and Season 2 is on Sheet3 starting in A1, on Sheet1 row 1 is headers, paste the following formula in cell A2 and confirm with <ctrl><shift><enter>, not just <enter>, then drag down as far as you figure you'll need to capture the unique names across the two lists. One drawback you'll notice is that it will show zero's at the bottom of the list. You could put another IF statement around this whole thing to get rid of those, but the formula becomes twice as long. Maybe some other array formula experts have a cleaner way to do this.


=IF(COUNTA(Sheet2!A:A)>=ROW(A2),Sheet2!A2,OFFSET(Sheet3!$A$1,SMALL(IF(NOT(ISNUMBER(MATCH(Sheet2!$A$2:$A$20,Sheet3!$A$2:$A$20,0))),ROW($A$2:$A$20)),ROW(A2)-COUNTA(Sheet2!$A:$A))-1,0,1,1))

 

[Fazza]

Or either a query table or pivot table - requires no VBA and no formulas.

 

[Joyner]

Pivot Tables are made for this. A Pivot Table with consolidated ranges takes only a minute to do and done...

 

[hro5e]

Thanks for your replies,

The reason why I did not want to use pivot tables is for the reason that you have to hit "refresh" every time any data has been updated/changed/removed etc.

Rastaman, I will try this formula later today and will update you to let you know if it has solved my problem.

Thanks

 

[hro5e]

<ctrl><shift><enter><enter>

But VB would be so much easier...

Here's a crack at it. If Season 1 is on Sheet2 starting in A1 and Season 2 is on Sheet3 starting in A1, on Sheet1 row 1 is headers, paste the following formula in cell A2 and confirm with <ctrl><shift><enter>, not just <enter>, then drag down as far as you figure you'll need to capture the unique names across the two lists. One drawback you'll notice is that it will show zero's at the bottom of the list. You could put another IF statement around this whole thing to get rid of those, but the formula becomes twice as long. Maybe some other array formula experts have a cleaner way to do this.


=IF(COUNTA(Sheet2!A:A)>=ROW(A2),Sheet2!A2,OFFSET(Sheet3!$A$1,SMALL(IF(NOT(ISNUMBER(MATCH(Sheet2!$A$2:$A$20,Sheet3!$A$2:$A$20,0))),ROW($A$2:$A$20)),ROW(A2)-COUNTA(Sheet2!$A:$A))-1,0,1,1))

Rastaman,

Unfortunately, that does not work. it returns the following table data:

[TABLE="width: 234"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Player[/TD]
[TD]Appearances[/TD]
[TD]Goals[/TD]
[/TR]
[TR]
[TD]J. Hart[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]V. Kompany[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Y. Toure[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]E. Dzeko[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]S. Aguero[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]S. Nasri[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]D. Silva[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]G. Clichy[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]J. Milner[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]J. Hart[/TD]
[TD]J. Hart[/TD]
[TD]J. Hart
[/TD]
[/TR]
</tbody>[/TABLE]
</enter></enter></shift></ctrl>


Also not sure what you mean by "confirm with <ctrl><shift><enter>, not just <enter>, "

If you could have another stab at it, it would be greatly appreciated.

Thanks again</enter></enter></shift></ctrl></enter></enter></shift></ctrl>

 

[Joyner]

Some of his text was missing, so this "Also not sure what you mean by "confirm with , not just , "

The formula is an array formula, so it was supposed to say confirm with (enter with) Ctri+Shift+Enter not just Enter

The formula won't work the same if you don't

 

[hro5e]

Some of his text was missing, so this "Also not sure what you mean by "confirm with , not just , "

The formula is an array formula, so it was supposed to say confirm with (enter with) Ctri+Shift+Enter not just Enter

The formula won't work the same if you don't

Oh yes of course.

Ok, just tried it and again does not work entirely, here is the result:

[TABLE="width: 220"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Player[/TD]
[TD]Appearances[/TD]
[TD]Goals[/TD]
[/TR]
[TR]
[TD]J. Hart[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]V. Kompany[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Y. Toure[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]E. Dzeko[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]S. Aguero[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]S. Nasri[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]D. Silva[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]G. Clichy[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]J. Milner[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]W. Rooney[/TD]
[TD]W. Rooney[/TD]
[TD]W. Rooney[/TD]
[/TR]
</tbody>[/TABLE]


The formula has successfully grabbed all player names but has not accumulated the data correctly.

the result should be this:

[TABLE="width: 224"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Player[/TD]
[TD]Appearances[/TD]
[TD]Goals[/TD]
[/TR]
[TR]
[TD]J. Hart[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]V. Kompany[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Y. Toure[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]E. Dzeko[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]S. Aguero[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]S. Nasri[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]D. Silva[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]G. Clichy[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]J. Milner[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]W. Rooney[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]


Any thoughts?

 

[hro5e]

But VB would be so much easier...

Here's a crack at it. If Season 1 is on Sheet2 starting in A1 and Season 2 is on Sheet3 starting in A1, on Sheet1 row 1 is headers, paste the following formula in cell A2 and confirm with <ctrl><shift><enter>, not just <enter>, then drag down as far as you figure you'll need to capture the unique names across the two lists. One drawback you'll notice is that it will show zero's at the bottom of the list. You could put another IF statement around this whole thing to get rid of those, but the formula becomes twice as long. Maybe some other array formula experts have a cleaner way to do this.


=IF(COUNTA(Sheet2!A:A)>=ROW(A2),Sheet2!A2,OFFSET(Sheet3!$A$1,SMALL(IF(NOT(ISNUMBER(MATCH(Sheet2!$A$2:$A$20,Sheet3!$A$2:$A$20,0))),ROW($A$2:$A$20)),ROW(A2)-COUNTA(Sheet2!$A:$A))-1,0,1,1))

Hi just to update, I have used your formula to get the names of the players into one table and then to sum the Appearances and Goals I used this formula.

=(SUMIF(Sheet2!$A$2:$A100,Sheet1!$A2,Sheet2!B$2:B100))+(SUMIF(Sheet3!A$2:$A100,Sheet1!$A2,Sheet3!B$2:$B100))

Works perfect,

Thanks guys
</enter></enter></shift></ctrl>