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

 

[Cubist]

Editing

 

[Steve 1962]

Hi Cubist

Was there supposed to be a reply with this ??

Thanks

 

[Cubist]

I did but noticed some error, but try this. This assmes that your column A is already in order. Until someone comes up with a prettier solution...

Book1
	A	B	C	D	E	F	G
1	Week	Home - Team	Away - Team		Week	Team - 8	Location
2	1	Team - 2	Team - 1		1	Team - 7	Away
3	1	Team - 3	Team - 4		2	Team - 1	Home
4	1	Team - 5	Team - 6		3	Team - 6	Away
5	1	Team - 7	Team - 8		4	Team - 7	Home
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

Cell Formulas
Range	Formula
F2:G5	F2	=LET(f,FILTER(B2:C17,(B2:B17=F1)+(C2:C17=F1)), br,BYROW(f,LAMBDA(r,MATCH($F$1,r,0))), loc,CHOOSE(br,"Home","Away"), team,FILTER(TOCOL(f),TOCOL((f))<>F1), HSTACK(team,loc))
Dynamic array formulas.

 

[Steve 1962]

Thanks Cubist

I'll keep working with it. I must have done something wrong as I'm now getting a #SPILL! issue.

 

[Cubist]

It's a spilled array. You only need to insert in formula in cell F2.
This option incorporates the week as well. Insert this into E2 only.

=LET(
f,FILTER(B2:C17,(B2:B17=F1)+(C2:C17=F1)),
w,FILTER(A2:A17,(B2:B17=F1)+(C2:C17=F1)),
br,BYROW(f,LAMBDA(r,MATCH($F$1,r,0))),
loc,CHOOSE(br,"Home","Away"),
team,FILTER(TOCOL(f),TOCOL((f))<>F1),
HSTACK(w,team,loc))

 

[bebo021999]

Another version for, Excel 2010 or newer

X day high low.xlsx
	A	B	C	D	E	F	G
1	Week	Home - Team	Away - Team		Week	Team - 2	Location
2	1	Team - 1	Team - 2		1	Team - 1	Away
3	1	Team - 3	Team - 4		2	Team - 7	Home
4	1	Team - 5	Team - 6		3	Team - 3	Away
5	1	Team - 7	Team - 8		4	Team - 5	Home
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

Cell Formulas
Range	Formula
F2:F5	F2	=IFERROR(LOOKUP(2,1/($A$2:$A$17=$E2)/($C$2:$C$17=$F$1),$B$2:$B$17),LOOKUP(2,1/($A$2:$A$17=$E2)/($B$2:$B$17=$F$1),$C$2:$C$17))
G2:G5	G2	=CHOOSE(AGGREGATE(15,6,COLUMN($B$1:$C$1)/($B$2:$C$17=F2)/($A$2:$A$17=E2)-1,1),"Away","Home")

 

[Steve 1962]

Hi Bebo

Thanks very much - works fine.

I have decided to place the data from columns A, B & C (which you referred to above in your reply) into a separate worksheet. I have provided a screenshot of that on the right-hand side below.

I have attempted to modify your formula so that it reads from the separate worksheet. I have had success modifying the formulas in cells F2:F5 to source the separate worksheet but obviously the

CHOOSE or AGGREGATE in cells G2:G5 doesn't like the move. What do I need to do to get the formulas in cells G2:G5 to source the separate worksheet.

The data in the separate worksheet is an EXCEL TABLE, therefore when referencing the data, instead of accepting the actual cell reference, by default the table name is inserted (as opposed to a cell reference).

Hoping you can help.

Thanks

 

[Steve 1962]

Thanks Bebo. I have now solved it.

 

[bebo021999]

Jus come back from weekend.
Yes, its nice to hear it works.
Further request, feel free to ask.