LAST 3 Events in 2 Seperate Columns?

[AYouQueTai]

Hello,
I have the NHL hockey games listed in my Schedule sheet like so.

Game ID | Status | Date | Visitor | Home | Vis Goals | Home Goals |
02001 | Completed | Jan18 | Montreal | Vancouver | 3 | 5
02002 | Completed | Jan18 | Toronto | Winnipeg | 4 | 2
02003 | Completed | Jan19 | Ottawa | Montreal | 5 | 1
02004 | Completed | Jan19 | Ottawa | Toronto | 6 | 2
02005 | Completed | Jan 20 | Montreal | Toronto | 3 | 2
02006 | Scheduled | Jan21 | Vancouver| Montreal | |

I would like to make a team sheet that lists a team's Last 3 "Completed" game Results.
Whether they were Visitor or Home doesn't matter
So in the Montreal Team Sheet it would display

Visitor | Home | Vis Goals | Home Goals |
Montreal | Vancouver | 3 | 5
Ottawa | Montreal | 5 | 1
Montreal | Toronto | 3 | 2

I can't figure this one out, can any of you?
Thanks,

 

[jasonb75]

Something like this perhaps?

spill fails.xlsx
	A	B	C	D	E	F	G
1	Game ID	Status	Date	Visitor	Home	Vis Goals	Home Goals
2	2001	Completed	Jan-18	Montreal	Vancouver	3	5
3	2002	Completed	Jan-18	Toronto	Winnipeg	4	2
4	2003	Completed	Jan-19	Ottawa	Montreal	5	1
5	2004	Completed	Jan-19	Ottawa	Toronto	6	2
6	2005	Completed	Jan-20	Montreal	Toronto	3	2
7	2006	Scheduled	Jan-21	Vancouver	Montreal
8
9	Montreal
10	Visitor	Home	Vis Goals	Home Goals
11	Montreal	Vancouver	3	5
12	Ottawa	Montreal	5	1
13	Montreal	Toronto	3	2
Sheet4

Cell Formulas
Range	Formula
A11:D13	A11	=INDEX(FILTER(D2:G7,((D2:D7=A9)+(E2:E7=A9))*(F2:F7<>"")*(G2:G7<>"")),SEQUENCE(3,,COUNTIFS(D2:E7,A9,F2:G7,"<>")-2),{1,2,3,4})
Dynamic array formulas.

 

[alz]

How about this!

Excel Message board.xlsm
	A	B	C	D	E	F	G
1	Game ID	Status	Date	Visitor	Home	Vis Goals	Home Goals
2	2001	Completed	Jan18	Montreal	Vancouver	3	5
3	2002	Completed	Jan18	Toronto	Winnipeg	4	2
4	2003	Completed	Jan19	Ottawa	Montreal	5	1
5	2004	Completed	Jan19	Ottawa	Toronto	6	2
6	2005	Completed	Jan 20	Montreal	Toronto	3	2
7	2006	Scheduled	Jan21	Vancouver	Montreal	1	1
8
9	Montreal	Completed	(last 3 match)
10	No.	Visitor	Home	Vis Goals	Home Goals
11	1	Montreal	Vancouver	3	5
12	2	Ottawa	Montreal	5	1
13	3	Montreal	Toronto	3	2
14
15
Sheet3

Cell Formulas
Range	Formula
B11:E13	B11	=INDEX(D$2:D$7,AGGREGATE(15,6,(((ROW($D$2:$D$7)-ROW($D$2)+1)))/(($B$2:$B$7="Completed")*((--($A$9=$D$2:$D$7))+(--($A$9=$E$2:$E$7)))>0),$A11))

 

[Fluff]

Slight tweak to Jason's idea to cater for less than 3 games completed

+Fluff v2.xlsm
	A	B	C	D	E	F	G
1	Game ID	Status	Date	Visitor	Home	Vis Goals	Home Goals
2	2001	Completed	01/01/2018	Montreal	Vancouver	3	5
3	2002	Completed	01/01/2018	Toronto	Winnipeg	4	2
4	2003	Completed	01/01/2019	Ottawa	Montreal	5	1
5	2004	Completed	01/01/2019	Ottawa	Toronto	6	2
6	2005	Completed	01/01/2020	Montreal	Toronto
7	2006	Scheduled	01/01/2021	Vancouver	Montreal
8
9	Montreal	2
10	Visitor	Home	Vis Goals	Home Goals
11	Montreal	Vancouver	3	5
12	Ottawa	Montreal	5	1
13
Master

Cell Formulas
Range	Formula
B9	B9	=MIN(3,COUNTIFS(D2:E7,A9,F2:G7,"<>"))
A11:D12	A11	=INDEX(FILTER(D2:G7,((D2:D7=A9)+(E2:E7=A9))*(F2:F7<>"")*(G2:G7<>"")),SEQUENCE(B9,,COUNTIFS(D2:E7,A9,F2:G7,"<>")-B9+1),{1,2,3,4})
Dynamic array formulas.

 

[jasonb75]

Nice one @Fluff, that was something I hadn't thought of

Not done full evaluation but I think that @alz suggestion would be the most efficient and practical. Given that it's a fixed set of results, I don't see any advantage to using dynamic arrays for this, just appear to be using them out of habit now.

A couple of tweaks to @alz formula, nothing wrong with the original but surplus parenthesis are kind of an ocd thing for me

=INDEX(D$2:D$7,AGGREGATE(15,6,ROW($D$2:$D$7)/(($B$2:$B$7="Completed")*(($A$9=$D$2:$D$7)+($A$9=$E$2:$E$7))>0),$A11)-ROW($D$2)+1)

I moved the row adjustment as well, wonder if anyone (except @Fluff) can see why.

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option with dynamic arrays

+Fluff v2.xlsm
	A	B	C	D	E	F	G
1	Game ID	Status	Date	Visitor	Home	Vis Goals	Home Goals
2	2001	Completed	01/01/2018	Montreal	Vancouver	3	5
3	2002	Completed	01/01/2018	Toronto	Winnipeg	4	2
4	2003	Completed	01/01/2019	Ottawa	Montreal	5	1
5	2004	Completed	01/01/2019	Ottawa	Toronto	6	2
6	2005	Completed	01/01/2020	Montreal	Toronto	1	5
7	2006	Scheduled	01/01/2021	Vancouver	Montreal
8
9	Montreal	3
10	Visitor	Home	Vis Goals	Home Goals
11	Montreal	Vancouver	3	5
12	Ottawa	Montreal	5	1
13	Montreal	Toronto	1	5
14
15	Montreal	Vancouver	3	5
16	Ottawa	Montreal	5	1
17	Montreal	Toronto	1	5
Master

Cell Formulas
Range	Formula
B9	B9	=MIN(3,COUNTIFS(D2:E7,A9,F2:G7,"<>"))
A11:D13	A11	=INDEX(FILTER(D2:G7,((D2:D7=A9)+(E2:E7=A9))*(F2:F7<>"")*(G2:G7<>"")),SEQUENCE(B9,,COUNTIFS(D2:E7,A9,F2:G7,"<>")-B9+1),{1,2,3,4})
A15:D17	A15	=IFERROR(INDEX(D$2:D$7,AGGREGATE(14,6,(ROW($D$2:$D$7)-ROW($D$2)+1)/(($D$2:$D$7=$A$9)+($E$2:$E$7=$A$9))/($B$2:$D$7="Completed"),ROWS($A15:$A$17))),"")
Dynamic array formulas.

@alz
Your formula will pull the 1st matches, not the last 3

 

[alz]

@Fluff you are right ! my mistake.
here is the corrected formula with a slight tweak. I like Double negative -- because easy to evaluate the formula.

Excel Message board.xlsm
	A	B	C	D	E	F	G
1	Game ID	Status	Date	Visitor	Home	Vis Goals	Home Goals
2	2001	Completed	Jan18	Montreal	Vancouver	3	5
3	2002	Completed	Jan18	toranto	Winnipeg	4	2
4	2003	Completed	Jan19	Ottawa	Montreal	5	1
5	2004	Completed	Jan19	Ottawa	Toronto	6	2
6	2005	Completed	Jan 20	Montreal	Toronto	3	2
7	2006	Scheduled	Jan21	Vancouver	Montreal
8
9	Montreal	Completed	(last 3 match)
10	Visitor	Home	Vis Goals	Home Goals
11	Montreal	Vancouver	3	5
12	Ottawa	Montreal	5	1
13	Montreal	Toronto	3	2
Sheet3

Cell Formulas
Range	Formula
A11:D13	A11	=INDEX(D$2:D$7,AGGREGATE(14,6,(ROW($D$2:$D$7)-ROW($D$2)+1)/((--($B$2:$B$7=$B$9))+(--($A$9=$D$2:$D$7))+(--($A$9=$E$2:$E$7))=2),ROWS($A11:$A$13)))

 

[jasonb75]

I like Double negative -- because easy to evaluate the formula.

They also add extra calculation steps to the formula, reducing the efficiency. Not much difference on a small array like this, but if you had a few thousand rows of data.

 

[AYouQueTai]

Jason, Sorry that I didn't have my account details up to date, the FILTER command isn't available in Excel 2013 so I wasn't able to test your solution. I guess it's time to upgrade.
ALZ, thank you very much. It works great and will save me lots of time!

 

[alz]

@AYouQueTai Thanks for your feedback.


@jasonb75 thanks for your advice !

For my learning,.
I created data for 100000 Rows and . download timer VBA code from Microsoft
Selected answer cells (total 12 for each) and tested the calculation speed
A little tweak formula of fluff is the fasted and follow by yours. ( not included for 365 version)

Excel Message board - Copy.xlsm
	M	N	O	P	Q
3	Rank
4	3	ALZ	=INDEX(D$2:D$100000,AGGREGATE(14,6,(ROW($D$2:$D$100000)-ROW($D$2)+1)/((--($B$2:$B$100000=$J$4))+(--($I$4=$D$2:$D$100000))+(--($I$4=$E$2:$E$100000))=2),ROWS($I6:$I$8)))
5		average
6		0.537616667	0.54634	0.53422	0.53229
7
8	4	Fluff original	=IFERROR(INDEX(D$2:D$100000,AGGREGATE(14,6,(ROW($D$2:$D$100000)-ROW($D$2)+1)/(($D$2:$D$100000=$I$10)+($E$2:$E$100000=$I$10))/($B$2:$D$100000="Completed"),ROWS($I11:$I$13))),"")
9		average
10		0.738006667	0.74378	0.73362	0.73662
11
12	2	Jason	=INDEX(D$2:D$100000,AGGREGATE(14,6,ROW($D$2:$D$100000)/(($B$2:$B$100000="Completed")*(($I$15=$D$2:$D$100000)+($I$15=$E$2:$E$100000))>0),$H16)-ROW($D$2)+1)
13		average
14		0.43431	0.43547	0.43492	0.43254
15
16	1	Fluff ( a little tweak)	=IFERROR(INDEX(D$2:D$100000,AGGREGATE(14,6,(ROW($D$2:$D$100000)-ROW($D$2)+1)/(($D$2:$D$100000=$I$20)+($E$2:$E$100000=$I$20))/($B$2:$B$100000="Completed"),ROWS($I21:$I$23))),"")
17		average
18		0.428703333	0.43054	0.42673	0.42884
30

Cell Formulas
Range	Formula
M4,M16,M12,M8	M4	=RANK(N6,$N$6:$N$18,1)
N6,N18,N14,N10	N6	=AVERAGE(O6:Q6)