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:

Team 1</SPAN>			Team 2</SPAN>
Team Played</SPAN>	Goals Scored</SPAN>		Team Played</SPAN>	Goals Scored</SPAN>
Team 3</SPAN>	2</SPAN>		Team 3</SPAN>	1</SPAN>
Team 4</SPAN>	1</SPAN>		Team 7</SPAN>	0</SPAN>
Team 5</SPAN>	0</SPAN>		Team 8</SPAN>	4</SPAN>
Team 6</SPAN>	3</SPAN>		Team 4</SPAN>	1</SPAN>

<TBODY>
</TBODY>

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.

Team Name</SPAN>	Goals Conceded</SPAN>
Team 3</SPAN>	3</SPAN>
Team 4</SPAN>	2</SPAN>
Team 5</SPAN>	0</SPAN>
Team 6</SPAN>	3</SPAN>
Team 7</SPAN>	0</SPAN>
Team 8</SPAN>	4</SPAN>

<TBODY>
</TBODY>

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
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

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</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.