Copy & Paste question

[Pezgordo]

I am working on a College Football spreadsheet that currently has game-by-game statistics for all 120 FBS teams. The cells/stats for each game run from column A to column EC. There are 1442 total rows. Each row contains information such as date, name of opponent, win/loss, etc and than mostly game stats.

What I would like to do, without having to copy and paste 1442 times, is place the corresponding row of each team's opponent next to that particular game. For example, on row 25 I have all of Arizona State's information from their game vs USC. On row 96 I have all of USC's information from their game vs Arizona State. How can I copy and paste the information in row 96 to row 25 (and from row 25 to row 96) starting at column ED? Since I would like to do this for each game (row), how can I do this without copy and pasting each game separately?

Any help would be appreciated. Thank you.

Tim

 

[GlennUK]

I'd imagine that you could look up the information for the opponent team. Is there some unique identifier per row for each team ( maybe it's the team name )? Is the opponent for a match identified by that unique identifier?

It may be a set of formulas like:
=INDEX(D$1:D$1422,MATCH(opponent_team_cell_ref,$A$1:$A$1422,0))

 

[Pezgordo]

Hi Glenn, thanks a lot for the reply. Unfortunately my Excel experience/knowledge is pretty limited, so when I tried your formula I just got #NAME?.

I'm sure I need to provide a better explanation and/or give more information. From columns A thru G I have the following information:

A = Date (game was played on)
B = Game #
C = Team
D = Opponent
E = Affiliation (indicates if it were a conference or non-conference game)
F = H/A (Home / Away)
G = W/L (Win / Loss)

An example of this would be (on row 7):

<table style="width: 632px; height: 77px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 75pt;" width="100"> <col style="width: 48pt;" width="64" span="3"> <col style="width: 62pt;" width="82"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl66" style="height: 15.75pt; width: 75pt;" width="100" height="21">Date</td> <td class="xl67" style="width: 48pt;" width="64">Game #</td> <td class="xl68" style="width: 48pt;" width="64">Team</td> <td class="xl68" style="width: 48pt;" width="64">Opponent</td> <td class="xl68" style="width: 62pt;" width="82"> Affiliation</td> <td class="xl69" style="width: 48pt;" width="64">H / A</td> <td class="xl70" style="width: 48pt;" width="64">W / L</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" height="21">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt;" align="right" height="20">3-Sep-10
</td> <td class="xl72"> 1
</td> <td class="xl73" style="border-left: medium none;">AZ</td> <td class="xl74">Tol</td> <td class="xl75" style="border-left: medium none;">NON</td> <td class="xl76">A</td> <td class="xl73">W</td> </tr> </tbody></table>
AZ = Arizona, and from column H to column EC, I have all of Arizona's statistics for this game.

Tol = Toledo. I have all of Toledo's (game) stats/information for this game on a separate row (row 1286). What I want to be able to do is copy and paste all of this information for Toledo (row 1286 from column A to column EC) and place it on row 7, starting at column ED (so it would start at ED7).

Ideally I would like to be able to do this for all 1442 rows (1442 separate games) without having to copy and paste 1442 separate times.

Thanks again for your help.

Tim




<table style="width: 502px; height: 78px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 15pt;" height="20"><td style="height: 15pt; width: 75pt;" width="100" height="20">
</td> <td class="xl81" style="width: 48pt;" width="64">
</td> <td class="xl64" style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 62pt;" width="82">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" height="21">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-left: medium none;">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">
</td> <td class="xl68">
</td> <td class="xl69">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl70">
</td> <td class="xl71">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" height="20">
</td> <td class="xl73">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl75">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl76" style="height: 15.75pt;" height="21">
</td> <td class="xl77">
</td> <td class="xl78">
</td> <td class="xl78">
</td> <td class="xl78">
</td> <td class="xl79">
</td> <td class="xl80">
</td> </tr> </tbody></table>

 

[GlennUK]

Does this make it easier to understand:

Excel Workbook
	A	B	C	D	E	F	G	ED	EE	EF	EG	EH	EI	EJ
1	Date	Game #	Team	Opponent	Affiliation	H / A	W / L
2	03-Sep-10	1	AZ	Tol	NON	A	W	06-Sep-10	1	Tol	AZ	ANY	H	L
3	04-Sep-10	1	AB
4	05-Sep-10	1	XY
5	06-Sep-10	1	Tol	AZ	ANY	H	L
Sheet2

 

[Pezgordo]

Glenn, the formula(s) worked perfect for Arizona's first opponent (Toledo), which was row 7 (started at ED7). However when I copy the formula and place it on ED8, instead of getting the information from Arizona's second opponent (Iowa), I get all the information from Iowa's first opponent. And if I copy the formula down even further, I get all of the information for Arizona's first opponents games.

Example:

<table border="0" cellpadding="0" cellspacing="0" width="466"><col style="width: 62pt;" width="82"> <col style="width: 48pt;" width="64" span="6"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl63" style="height: 15.75pt; width: 62pt;" width="82" height="21">Date</td> <td class="xl64" style="width: 48pt;" width="64">Game #</td> <td class="xl65" style="width: 48pt;" width="64">Team</td> <td class="xl65" style="width: 48pt;" width="64">Opponent</td> <td class="xl65" style="width: 48pt;" width="64">Affiliation</td> <td class="xl66" style="width: 48pt;" width="64">H / A</td> <td class="xl67" style="width: 48pt;" width="64">W / L</td> </tr> <tr style="height: 15.75pt;" height="21"> <td style="height: 15.75pt;" height="21">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">3-Sep-10</td> <td align="right">1</td> <td>TOL</td> <td>AZ</td> <td>NON</td> <td>H</td> <td>L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">11-Sep-10</td> <td align="right">1</td> <td>IA</td> <td>ISU</td> <td>NON</td> <td>H</td> <td>W</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">11-Sep-10</td> <td align="right">1</td> <td>CAL</td> <td>CU</td> <td>NON</td> <td>H</td> <td>W</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">4-Sep-10</td> <td align="right">1</td> <td>ORST</td> <td>tcu</td> <td>NON</td> <td>A</td> <td>L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">4-Sep-10</td> <td align="right">1</td> <td>WSU</td> <td>osu</td> <td>NON</td> <td>A</td> <td>L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">4-Sep-10</td> <td align="right">1</td> <td>UW</td> <td>byu</td> <td>NON</td> <td>A</td> <td>L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">4-Sep-10</td> <td align="right">1</td> <td>UCLA</td> <td>kst</td> <td>NON</td> <td>A</td> <td>L</td> </tr> </tbody></table>
I tried eliminating the game #, but it still brought it up by date. I tried eliminating the game # and date, but it still brought it up in the above format.

Any suggestions? I am sure I am just missing something simple, so I will keep playing around with it.

Thank you,

Tim

 

[GlennUK]

The formula does exactly what you first requested. However, your requirements seem to have changed ... I now don't know what the devil you are talking about ... I cannot see that row 8 relates to Arizona. I think you need to spend some time explaining exactly what you are expecting to see.

 

[Pezgordo]

Glenn, sorry for my poor explanation(s). Yes the formula woks perfect for row 7 (the first of the 1442 total rows). However, when I copy and paste it (the formula) to any of the next rows it does not bring up the corresponding game.

For example, here are the first 6 rows (starting at row 7). Each row is an Arizona game:

<table border="0" cellpadding="0" cellspacing="0" width="539"><col style="width: 75pt;" width="100"> <col style="width: 28pt;" width="37"> <col style="width: 48pt;" width="64" span="3"> <col style="width: 62pt;" width="82"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl66" style="height: 15.75pt; width: 75pt;" width="100" height="21">Date</td> <td class="xl67" style="width: 28pt;" width="37">
</td> <td class="xl67" style="width: 48pt;" width="64">Game #</td> <td class="xl68" style="width: 48pt;" width="64">Team</td> <td class="xl68" style="width: 48pt;" width="64">Opponent</td> <td class="xl68" style="width: 62pt;" width="82">Affiliation</td> <td class="xl69" style="width: 48pt;" width="64">H / A</td> <td class="xl70" style="width: 48pt;" width="64">W / L</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt;" height="21">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt;" align="right" height="20">3-Sep-10</td> <td class="xl71">
</td> <td class="xl72">1</td> <td class="xl73" style="border-left: medium none;">AZ</td> <td class="xl74">Tol</td> <td class="xl75" style="border-left: medium none;">NON</td> <td class="xl76">A</td> <td class="xl73">W</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt;" align="right" height="20">18-Sep-10</td> <td class="xl71">
</td> <td class="xl72" style="border-top: medium none;">2</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">AZ</td> <td class="xl78" style="border-top: medium none;">IA</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">NON</td> <td class="xl76" style="border-top: medium none;">H</td> <td class="xl73" style="border-top: medium none;">W</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt;" align="right" height="20">25-Sep-10</td> <td class="xl71">
</td> <td class="xl72" style="border-top: medium none;">3</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">AZ</td> <td class="xl78" style="border-top: medium none;">CAL</td> <td class="xl79" style="border-left: medium none;">PAC 12</td> <td class="xl80">H</td> <td class="xl73" style="border-top: medium none;">W</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt;" align="right" height="20">9-Oct-10</td> <td class="xl71">
</td> <td class="xl72" style="border-top: medium none;">4</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">AZ</td> <td class="xl78" style="border-top: medium none;">ORST</td> <td class="xl79" style="border-left: medium none;">PAC 12</td> <td class="xl80">H</td> <td class="xl73" style="border-top: medium none;">L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt;" align="right" height="20">16-Oct-10</td> <td class="xl71">
</td> <td class="xl72" style="border-top: medium none;">5</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">AZ</td> <td class="xl78" style="border-top: medium none;">Wsu</td> <td class="xl79" style="border-left: medium none;">PAC 12</td> <td class="xl80">A</td> <td class="xl73" style="border-top: medium none;">W</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt;" align="right" height="20">23-Oct-10</td> <td class="xl71">
</td> <td class="xl72" style="border-top: medium none;">6</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">AZ</td> <td class="xl78" style="border-top: medium none;">UW</td> <td class="xl79" style="border-left: medium none;">PAC 12</td> <td class="xl80">H</td> <td class="xl73" style="border-top: medium none;">W</td> </tr> </tbody></table>
As discussed previously, starting at column ED, I would like to place the corresponding row for each teams (in the example, Arizona) opponent, which should look like this:

<table border="0" cellpadding="0" cellspacing="0" width="494"><col style="width: 62pt;" width="82"> <col style="width: 21pt;" width="28"> <col style="width: 48pt;" width="64" span="6"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt; width: 62pt;" width="82" height="21">Date</td> <td class="xl66" style="width: 21pt;" width="28">
</td> <td class="xl66" style="width: 48pt;" width="64">Game #</td> <td class="xl67" style="width: 48pt;" width="64">Team</td> <td class="xl67" style="width: 48pt;" width="64">Opponent</td> <td class="xl67" style="width: 48pt;" width="64">Affiliation</td> <td class="xl68" style="width: 48pt;" width="64">H / A</td> <td class="xl69" style="width: 48pt;" width="64">W / L</td> </tr> <tr style="height: 15.75pt;" height="21"> <td style="height: 15.75pt;" height="21">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl78" style="height: 15pt;" align="right" height="20">3-Sep-10</td> <td class="xl78">
</td> <td class="xl79">1</td> <td>TOL</td> <td>AZ</td> <td>NON</td> <td>H</td> <td>L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" align="right" height="20">18-Sep-10</td> <td class="xl70">
</td> <td class="xl71">2</td> <td class="xl72" style="border-left: medium none;">IA</td> <td class="xl75">az</td> <td class="xl74" style="border-left: medium none;">NON</td> <td class="xl73">A</td> <td class="xl72">L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" align="right" height="20">25-Sep-10</td> <td class="xl70">
</td> <td class="xl71" style="border-top: medium none;">3</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">CAL</td> <td class="xl75" style="border-top: medium none;">az</td> <td class="xl76" style="border-left: medium none;">PAC 12</td> <td class="xl77">A</td> <td class="xl72" style="border-top: medium none;">L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" align="right" height="20">9-Oct-10</td> <td class="xl70">
</td> <td class="xl71" style="border-top: medium none;">5</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">ORST</td> <td class="xl75" style="border-top: medium none;">az</td> <td class="xl76" style="border-left: medium none;">PAC 12</td> <td class="xl77">A</td> <td class="xl72" style="border-top: medium none;">W</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" align="right" height="20">16-Oct-10</td> <td class="xl70">
</td> <td class="xl71" style="border-top: medium none;">6</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">WSU</td> <td class="xl75" style="border-top: medium none;">AZ</td> <td class="xl76" style="border-left: medium none;">PAC 12</td> <td class="xl77">H</td> <td class="xl72" style="border-top: medium none;">L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" align="right" height="20">23-Oct-10</td> <td class="xl70">
</td> <td class="xl71" style="border-top: medium none;">7</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">UW</td> <td class="xl75" style="border-top: medium none;">az</td> <td class="xl76" style="border-left: medium none;">PAC 12</td> <td class="xl77">A</td> <td class="xl72" style="border-top: medium none;">L</td> </tr> </tbody></table>
When I use the formula for the first row (the game vs Toledo) it comes out perfect. However when I use it for the other rows it looks as follows:

<table border="0" cellpadding="0" cellspacing="0" width="500"><col style="width: 62pt;" width="82"> <col style="width: 26pt;" width="34"> <col style="width: 48pt;" width="64" span="6"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl65" style="height: 15.75pt; width: 62pt;" width="82" height="21">Date</td> <td class="xl66" style="width: 26pt;" width="34">
</td> <td class="xl66" style="width: 48pt;" width="64">Game #</td> <td class="xl67" style="width: 48pt;" width="64">Team</td> <td class="xl67" style="width: 48pt;" width="64">Opponent</td> <td class="xl67" style="width: 48pt;" width="64">Affiliation</td> <td class="xl68" style="width: 48pt;" width="64">H / A</td> <td class="xl69" style="width: 48pt;" width="64">W / L</td> </tr> <tr style="height: 15.75pt;" height="21"> <td style="height: 15.75pt;" height="21">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" align="right" height="20">3-Sep-10</td> <td class="xl70">
</td> <td class="xl71">1</td> <td>TOL</td> <td>AZ</td> <td>NON</td> <td>H</td> <td>L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" align="right" height="20">11-Sep-10</td> <td class="xl70">
</td> <td class="xl71">1</td> <td>IA</td> <td>ISU</td> <td>NON</td> <td>H</td> <td>W</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" align="right" height="20">11-Sep-10</td> <td class="xl70">
</td> <td class="xl71">1</td> <td>CAL</td> <td>CU</td> <td>NON</td> <td>H</td> <td>W</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" align="right" height="20">4-Sep-10</td> <td class="xl70">
</td> <td class="xl71">1</td> <td>ORST</td> <td>tcu</td> <td>NON</td> <td>A</td> <td>L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" align="right" height="20">4-Sep-10</td> <td class="xl70">
</td> <td class="xl71">1</td> <td>WSU</td> <td>osu</td> <td>NON</td> <td>A</td> <td>L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" align="right" height="20">4-Sep-10</td> <td class="xl70">
</td> <td class="xl71">1</td> <td>UW</td> <td>byu</td> <td>NON</td> <td>A</td> <td>L</td> </tr> </tbody></table>
Instead of bringing up Arizona's game vs Iowa in the 2nd row, it brings up Iowa's first game against Iowa State and so forth. I tried eliminating the dates and the game #'s, but the formula still brings up the first game for each opponent throughout the spreadsheet.

 

[GlennUK]

Aha, I see now. Is there only going to be one row per combination of Team and Opponent?

I mean taking AZ TOL as one combination and TOL AZ as another. And there being only one of each in the list.

 

[Pezgordo]

Yes there would only be one row of the combination (taking AZ TOL as one combination and TOL AZ as another. And there being only one of each in the list).

 

[GlennUK]

Have a look at this simple example, and see if you can expand it for your needs:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Date		Game #	Team	Opponent	Affiliation	H / A	W / L	fetch record
2	03-Sep-10		1	AZ	Tol	NON	A	W	ggg
3	18-Sep-10		2	AZ	IA	NON	H	W	mmm
4	25-Sep-10		3	AZ	CAL	PAC 12	H	W	xxx
5	09-Oct-10		4	AZ	ORST	PAC 12	H	L	No record found
6	16-Oct-10		5	AZ	Wsu	PAC 12	A	W	No record found
7	23-Oct-10		6	AZ	UW	PAC 12	H	W	No record found
8	03-Sep-10		99	CAL	AZ	xxx
9	18-Sep-10		100	CAL	Tol	yyy
10	25-Sep-10		101	CAL	IA	zzz
11	09-Oct-10		102	CAL	ORST	aaa
12	16-Oct-10		103	CAL	Wsu	bbb
13	23-Oct-10		104	CAL	UW	ccc
14	03-Sep-10		105	Tol	AZ	ggg
15	18-Sep-10		106	Tol	IA	hhh
16	25-Sep-10		107	Tol	CAL	iii
17	09-Oct-10		108	IA	AZ	mmm
18	16-Oct-10		109	IA	Tol	nnn
19	23-Oct-10		110	IA	CAL	ooo
Sheet3