Combining data from 2 rows into 1

[MiniD011]

Hi guys,

I am working on a project at the moment which requires information from 2 different sources to be consolidated into just one row with the title.

To give an example, I have two football teams, team 1 & 2 (because I'm feeling creative you see). If I am tracking their progress monthly and I end up with two tables, each with 3 fields, to track which teams they played and the goals scored, I would get something like the following:

[TABLE="width: 411"]
<TBODY>[TR]
[TD]Team 1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Team 2</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team Played</SPAN>
[/TD]
[TD="align: right"]Goals Scored</SPAN>
[/TD]
[TD][/TD]
[TD]Team Played</SPAN>
[/TD]
[TD="align: right"]Goals Scored</SPAN>
[/TD]
[/TR]
[TR]
[TD]Team 3</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD][/TD]
[TD]Team 3</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]Team 4</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD]Team 7</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
[TR]
[TD]Team 5</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD]Team 8</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[/TR]
[TR]
[TD]Team 6</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD][/TD]
[TD]Team 4</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


If I then want to make a list to know how many goals the opposing teams (3-8) have conceded, regardless of who they were against, what would be the best way to do this automatically, preferably in VBA?

I need these to feed in to a different sheet to show how many goals each team have conceded, but I am struggling when 1 team has been played by both Teams 1 & 2.

I am hoping to end up with something like this, only automatic as the number of 'Teams' I am actually using is far far higher.

[TABLE="width: 195"]
<TBODY>[TR]
[TD]Team Name</SPAN>
[/TD]
[TD="align: right"]Goals Conceded</SPAN>
[/TD]
[/TR]
[TR]
[TD]Team 3</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[/TR]
[TR]
[TD]Team 4</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[/TR]
[TR]
[TD]Team 5</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
[TR]
[TD]Team 6</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[/TR]
[TR]
[TD]Team 7</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
[TR]
[TD]Team 8</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


The actual project I am working on has nothing to do with football but it is the same basic thing I am trying to achieve, so if someone could help with this I will hopefully be able to apply the lessons learnt here to my own problem as a resolution.

Thanks in advance!

 

[Andrew Poulsom]

Have your tried a pivot table with multiple consolidation ranges? It worked for me with your dample data:

Excel 2003
	A	B	C	D	E	F	G
1	Team 1		Team 2
2	Team Played	Goals Scored	Team Played	Goals Scored
3	Team 3	2	Team 3	1		Sum of Value	Column
4	Team 4	1	Team 7	0		Row	Goals Scored
5	Team 5	0	Team 8	4		Team 3	3
6	Team 6	3	Team 4	1		Team 4	2
7						Team 5	0
8						Team 6	3
9						Team 7	0
10						Team 8	4
Sheet8

 

[MiniD011]

Have your tried a pivot table with multiple consolidation ranges? It worked for me with your dample data:

Excel 2003

	A	B	C	D	E	F	G
Team 1	Team 2
Team Played	Goals Scored	Team Played	Goals Scored
Team 3	Team 3	Sum of Value	Column
Team 4	Team 7	Row	Goals Scored
Team 5	Team 8	Team 3
Team 6	Team 4	Team 4
Team 5
Team 6
Team 7
Team 8

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

</TBODY>

Sheet8​

First and foremost thanks for the speedy response, this is exactly what I am looking for!

This may sound silly but I have never used a Pivot Table before in my life, so how precisely is this done, and can it be done in VBA? I am attempting to automate several processes, of which this is one, at the click of a button so using VBA to do this is pretty essential.

Thanks again, and thanks in advance for any further responses!

 

[Andrew Poulsom]

Here's how it's done:

Excel Pivot Table Tutorial -- Multiple Consolidation Ranges

The macro recorder should give you some VBA code.