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

 

[Pezgordo]

I'm sorry Glenn but my Excel knowledge/skills is pretty rudimentary. After reviewing your example, I do get ggg in I2, but I do not understand how this retrieves the information and stats for that game. If I copy the formula over to J2 I get No Record Found.

Also, based on your example am I supposed to add a column (B) between the date (column A) and the game # (column C) when using it in my spreadsheet?

When I put the formula in as is (in my column EE after adding column B), I get the following in the date column:

<table style="width: 273px; height: 232px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 62pt;" width="82"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 62pt;" width="82" height="20">PAC 12</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PAC 12</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">No record found</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PAC 12</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">No record found</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">NON</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PAC 12</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PAC 12</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PAC 12</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">PAC 12</td> </tr> </tbody></table>
Also, just out of curiosity, why is No Record Found in the formula? I ask because of the 1442 rows of information/stats for each team (771 total games), there will always be a record found for each opponent.

Thank you,

Tim

 

[GlennUK]

Here is an example that will copy as you wish:

Excel Workbook
	A	B	C	D	E	F	G
1	Date	Game #	Team	Opponent	Affiliation	H / A	W / L
2	03-Sep-10	1	AZ	Tol	NON	A	W
3	18-Sep-10	2	AZ	IA	NON	H	W
4	25-Sep-10	3	AZ	CAL	PAC 12	H	W
5	09-Oct-10	4	AZ	ORST	PAC 12	H	L
6	16-Oct-10	5	AZ	Wsu	PAC 12	A	W
7	23-Oct-10	6	AZ	UW	PAC 12	H	W
8	03-Sep-10	99	CAL	AZ	xxx	B	R
9	18-Sep-10	100	CAL	Tol	yyy	C	S
10	25-Sep-10	101	CAL	IA	zzz	D	T
11	09-Oct-10	102	CAL	ORST	aaa	E	U
12	16-Oct-10	103	CAL	Wsu	bbb	F	V
13	23-Oct-10	104	CAL	UW	ccc	G	W
14	03-Sep-10	105	Tol	AZ	ggg	H	X
15	18-Sep-10	106	Tol	IA	hhh	I	Y
16	25-Sep-10	107	Tol	CAL	iii	J	Z
17	09-Oct-10	108	IA	AZ	mmm	K	AA
18	16-Oct-10	109	IA	Tol	nnn	M	BB
19	23-Oct-10	110	IA	CAL	ooo	N	CC
20	03-Sep-10	111	ORST	AZ	ppp	O	DD
21	18-Sep-10	112	Wsu	AZ	qqq	P	EE
22	25-Sep-10	113	UW	AZ	rrr	Q	FF
23	23-Oct-10	114	ORST	CAL	sss	qq	fff
24	03-Sep-10	115	Wsu	CAL	ttt	qqq	ffff
25	18-Sep-10	116	UW	CAL	uuu	qqqq	fffff
Sheet2

Excel Workbook
	H	I	J
1	fetch record field1	fetch record field2	fetch record field3
2	ggg	H	X
3	mmm	K	AA
4	xxx	B	R
5	ppp	O	DD
Sheet2

 

[Pezgordo]

Success. Thanks Glenn, it worked great. I am sorry it took me so long to catch on. FYI, one minor discrepancy I noticed (and since it occurred only 4 times in 1442 games it was definitely not a problem), the formula would not work for UW vs NB (Washington vs Nebraska) or SC vs AUB (South Carolina vs Auburn). I finally figured out that the reason why it wouldn't work for those particular teams/games was because they each played each other twice during the season. Washington played Nebraska during the regular season and again in a bowl game while South Carolina played Auburn during the regular season and than again in the SEC Championship. So for those 4 games the formula did not work (4 completely unrelated games showed up).

Thanks again,

Tim

 

[GlennUK]

Success. Thanks Glenn, it worked great. I am sorry it took me so long to catch on. FYI, one minor discrepancy I noticed (and since it occurred only 4 times in 1442 games it was definitely not a problem), the formula would not work for UW vs NB (Washington vs Nebraska) or SC vs AUB (South Carolina vs Auburn). I finally figured out that the reason why it wouldn't work for those particular teams/games was because they each played each other twice during the season. Washington played Nebraska during the regular season and again in a bowl game while South Carolina played Auburn during the regular season and than again in the SEC Championship. So for those 4 games the formula did not work (4 completely unrelated games showed up).

Thanks again,

Tim

It's great that it's working ( to a major degree ).

As for the discrepency .... that's why I asked whether there would be one, and only one occurence of each pair in the list ... it depends on that being the case. You said that was the case too.

A quick fix is to mark the different matches by altering the team names to identify which pair of rows match each other like AZ1 and AZ2 etc etc.

If you want the formula to give a message when there is more than 1 match, just add an extra level of IF function, and do the same testing as for SUMPRODUCT being zero, but test for greater than 1.

 

[Pezgordo]

Glenn, it worked great. Yes I did say there would be only one occurrence of each pair in the list. I completely overlooked these two particular rematches. But typically there will only be one occurrence per season.

Thank you again,

Tim

 

[GlennUK]

My pleasure.