Summary Report

[Steve 1962]

Hi
I am attempting to summarize a team’s play report. Columns A, B & C is the playing program (Week No. / Team location (away or home)).

I require this to be summarized in Columns E to H specifically, column F to show which team the team noted in cell F1 played in the nominated round shown in column E and also its location HOME if located in column B or AWAY if located in column C. Cells F2 to G5 require the solution formula. In this example, Team – 2 is being analysed (cell F1).

Column H is nothing – I have just provided it to show the rows from which the data need to be extracted.

Thanks

Book1
	A	B	C	D	E	F	G	H
1	Week	Home - Team	Away - Team		Week	Team - 2	Location	Comments
2	1	Team - 1	Team - 2		1	Team - 1	Away	(row 2)
3	1	Team - 3	Team - 4		2	Team - 7	Home	(row 6)
4	1	Team - 5	Team - 6		3	Team - 3	Away	(row 11)
5	1	Team - 7	Team - 8		4	Team - 5	Home	(row 16)
6	2	Team - 2	Team - 7
7	2	Team - 3	Team - 4
8	2	Team - 5	Team - 6
9	2	Team - 8	Team - 1
10	3	Team - 1	Team - 4
11	3	Team - 3	Team - 2
12	3	Team - 5	Team - 7
13	3	Team - 6	Team - 8
14	4	Team - 8	Team - 7
15	4	Team - 6	Team - 4
16	4	Team - 2	Team - 5
17	4	Team - 3	Team - 1
Sheet1

 

[Steve 1962]

Thank you very much Bebo. Have a nice week.

 

[Steve 1962]

Hi Bebo

Not sure if you are able to see this (which relates to an earlier thread you helped with) but I rearranged my worksheet a bit and am now having errors.

The source table (A1 to C32) is actually located on another worksheet. I'm not sure if this is the problem or not. The formula in cells H2:H5 is not working properly. The objective is to identify whether the team being analysed in cell E2 is playing "HOME" or "AWAY".

Book1
	A	B	C	D	E	F	G	H	I
1	Week	Team - Home	Team - Away		Team Analysed	Week	Team	Ground
2	1	Sea Eagles	Rabbitohs		Broncos	1	Roosters	#VALUE!	Should read "AWAY"
3	1	Roosters	Broncos			2	Rabbitohs	#VALUE!	Should read "HOME"
4	1	Knights	Raiders			3	Panthers	#VALUE!	Should read "AWAY"
5	1	Warriors	Sharks			4	Cowboys	#VALUE!	Should read "HOME"
6	1	Storm	Panthers
7	1	Eels	Bulldogs
8	1	Titans	Dragons
9	1	Dolphins	Cowboys
10	2	Broncos	Rabbitohs
11	2	Sharks	Bulldogs
12	2	Panthers	Eels
13	2	Raiders	Tigers
14	2	Cowboys	Knights
15	2	Storm	Warriors
16	2	Sea Eagles	Roosters
17	2	Dolphins	Dragons
18	3	Panthers	Broncos
19	3	Warriors	Raiders
20	3	Roosters	Rabbitohs
21	3	Bulldogs	Titans
22	3	Dragons	Cowboys
23	3	Tigers	Sharks
24	3	Eels	Sea Eagles
25	3	Knights	Storm
26	4	Roosters	Panthers
27	4	Rabbitohs	Bulldogs
28	4	Broncos	Cowboys
29	4	Dragons	Sea Eagles
30	4	Titans	Dolphins
31	4	Warriors	Knights
32	4	Sharks	Raiders
Sheet2

Cell Formulas
Range	Formula
G2:G5	G2	=IFERROR(LOOKUP(2,1/($A$2:$A$32=$F2)/($C$2:$C$32=$E$2),$B$2:$B$32),LOOKUP(2,1/($A$2:$A$32=$F2)/($B$2:$B$32=$E$2),$C$2:$C$32))
H2:H5	H2	=CHOOSE(AGGREGATE(15,6,COLUMN($B$1:$C$1)/($B$2:$C$32=G2)/($A$2:$A$32=F2)-3,1),"Home","Away")

 

[bebo021999]

Just returned and saw your message. I apologize for making you wait.
Given that your data is on a different sheet and within a table, it has become complex.
It would be best if you upload the file using Google Drive.
See you soon.

 

[Fluff]

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.