Identify last 5 occurences in a list of multiple columns

[PedroGomez9]

Hi guys

I have a list of sporting results, and I'm trying to put a TRUE/FALSE on whether or not it's one of the team's last 'x' (user-defined, changeable value) games - they can appear in either Column A or Column B.
I have a helper column, lets call it C1 for arguments sake with the user defined value of games I'm looking to isolate.

HomeTeam	AwayTeam
Watford	Middlesbrough
Fulham	Arsenal
Birmingham	Brentford
Wycombe	Rotherham
Crystal Palace	Southampton
Barnsley	Luton
Bournemouth	Blackburn
Bristol City	Coventry
Cardiff	Sheffield Weds
Derby	Reading
Huddersfield	Norwich
Millwall	Stoke
Preston	Swansea
QPR	Nott'm Forest
Accrington	Peterboro
Crewe	Charlton
Doncaster	Milton Keynes Dons
Fleetwood Town	Burton
Gillingham	Hull
Lincoln	Oxford
Northampton	AFC Wimbledon
Plymouth	Blackpool
Portsmouth	Shrewsbury
Sunderland	Bristol Rvs
Swindon	Rochdale
Barrow	Stevenage
Bolton	Forest Green
Bradford	Colchester
Cambridge	Carlisle
Cheltenham	Morecambe
Mansfield	Tranmere
Oldham	Leyton Orient
Port Vale	Crawley Town
Salford	Exeter
Scunthorpe	Newport County
Southend	Harrogate
Walsall	Grimsby
Liverpool	Leeds
West Ham	Newcastle
Ipswich	Wigan
West Brom	Leicester
Tottenham	Everton
Sheffield United	Wolves
Brighton	Chelsea
Coventry	QPR
Everton	West Brom
Nott'm Forest	Cardiff
Carlisle	Southend
Leeds	Fulham
Blackburn	Wycombe
Brentford	Huddersfield
Luton	Derby
Middlesbrough	Bournemouth
Norwich	Preston
Reading	Barnsley
Rotherham	Millwall
Sheffield Weds	Watford
Swansea	Birmingham
AFC Wimbledon	Plymouth
Blackpool	Swindon
Bristol Rvs	Ipswich
Burton	Accrington
Charlton	Doncaster
Hull	Crewe
Milton Keynes Dons	Lincoln
Oxford	Sunderland
Peterboro	Fleetwood Town
Shrewsbury	Northampton
Wigan	Gillingham
Colchester	Bolton
Crawley Town	Scunthorpe
Exeter	Port Vale
Forest Green	Bradford
Grimsby	Salford
Harrogate	Walsall
Leyton Orient	Mansfield
Morecambe	Cambridge
Newport County	Barrow
Stevenage	Oldham
Tranmere	Cheltenham
Man United	Crystal Palace
Arsenal	West Ham
Southampton	Tottenham
Newcastle	Brighton
Stoke	Bristol City
Rochdale	Portsmouth
Chelsea	Liverpool
Leicester	Burnley
Aston Villa	Sheffield United
Wolves	Man City
Huddersfield	Nott'm Forest
Brighton	Man United
Watford	Luton
Wycombe	Swansea
Crystal Palace	Everton
Barnsley	Coventry
Birmingham	Rotherham
Cardiff	Reading
Derby	Blackburn

 

[Peter_SSs]

Something like this?

21 03 30.xlsm
	A	B	C	D	E	F
1	HomeTeam	AwayTeam	Last n?		Team	Swansea
2	Watford	Middlesbrough			Last N	2
3	Fulham	Arsenal
4	Birmingham	Brentford
5	Wycombe	Rotherham
6	Crystal Palace	Southampton
7	Barnsley	Luton
8	Bournemouth	Blackburn
9	Bristol City	Coventry
10	Cardiff	Sheffield Weds
11	Derby	Reading
12	Huddersfield	Norwich
13	Millwall	Stoke
14	Preston	Swansea	FALSE
15	QPR	Nott'm Forest
16	Accrington	Peterboro
17	Crewe	Charlton
18	Doncaster	Milton Keynes Dons
19	Fleetwood Town	Burton
20	Gillingham	Hull
21	Lincoln	Oxford
22	Northampton	AFC Wimbledon
23	Plymouth	Blackpool
24	Portsmouth	Shrewsbury
25	Sunderland	Bristol Rvs
26	Swindon	Rochdale
27	Barrow	Stevenage
28	Bolton	Forest Green
29	Bradford	Colchester
30	Cambridge	Carlisle
31	Cheltenham	Morecambe
32	Mansfield	Tranmere
33	Oldham	Leyton Orient
34	Port Vale	Crawley Town
35	Salford	Exeter
36	Scunthorpe	Newport County
37	Southend	Harrogate
38	Walsall	Grimsby
39	Liverpool	Leeds
40	West Ham	Newcastle
41	Ipswich	Wigan
42	West Brom	Leicester
43	Tottenham	Everton
44	Sheffield United	Wolves
45	Brighton	Chelsea
46	Coventry	QPR
47	Everton	West Brom
48	Nott'm Forest	Cardiff
49	Carlisle	Southend
50	Leeds	Fulham
51	Blackburn	Wycombe
52	Brentford	Huddersfield
53	Luton	Derby
54	Middlesbrough	Bournemouth
55	Norwich	Preston
56	Reading	Barnsley
57	Rotherham	Millwall
58	Sheffield Weds	Watford
59	Swansea	Birmingham	TRUE
60	AFC Wimbledon	Plymouth
61	Blackpool	Swindon
62	Bristol Rvs	Ipswich
63	Burton	Accrington
64	Charlton	Doncaster
65	Hull	Crewe
66	Milton Keynes Dons	Lincoln
67	Oxford	Sunderland
68	Peterboro	Fleetwood Town
69	Shrewsbury	Northampton
70	Wigan	Gillingham
71	Colchester	Bolton
72	Crawley Town	Scunthorpe
73	Exeter	Port Vale
74	Forest Green	Bradford
75	Grimsby	Salford
76	Harrogate	Walsall
77	Leyton Orient	Mansfield
78	Morecambe	Cambridge
79	Newport County	Barrow
80	Stevenage	Oldham
81	Tranmere	Cheltenham
82	Man United	Crystal Palace
83	Arsenal	West Ham
84	Southampton	Tottenham
85	Newcastle	Brighton
86	Stoke	Bristol City
87	Rochdale	Portsmouth
88	Chelsea	Liverpool
89	Leicester	Burnley
90	Aston Villa	Sheffield United
91	Wolves	Man City
92	Huddersfield	Nott'm Forest
93	Brighton	Man United
94	Watford	Luton
95	Wycombe	Swansea	TRUE
96	Crystal Palace	Everton
97	Barnsley	Coventry
98	Birmingham	Rotherham
99	Cardiff	Reading
100	Derby	Blackburn
Last n

Cell Formulas
Range	Formula
C2:C100	C2	=IF(COUNTIF(A2:B2,F$1),COUNTIF(C3:C$1000,TRUE)<F$2,"")

 

[PedroGomez9]

Hi Peter

Thanks for the reply. It's close to what I'm after, but ideally for all rows instead of being team specific.

For example I already have it broken down whether or not it's within each team's last 'n' home games OR their last 5 away games... I just can't figure out how to get it so that it's their last 5 games irrespective of home or away.

A more comprehensive data set is below (Columns A-N, with column N ideally housing the TRUE/FALSE).
Formula already used to calculate if it's the last 'n' in either column is:

=COUNTIF(C1:C$1938,[@HomeTeam])<='User Defined Analysis'!$B$1

This is dragged down to all subsequent cells, whereby 'User Defined Analysis'!$B$1 is my 'n' value.

Div

Date

HomeTeam

AwayTeam

HomeTeamGoals

AwayTeamGoals

Result

TotalGoals

BTTS

HomeTeamYellow

AwayTeamYellow

Is last 'x' home?

Is last 'x' away?

Last 'x' overall

FL1

16/02/2021

Ipswich

Northampton

0

0

D

0

No

2

3

TRUE

TRUE

FL2

16/02/2021

Bradford

Morecambe

2

1

H

3

Yes

1

3

TRUE

TRUE

FL2

16/02/2021

Crawley Town

Stevenage

0

1

A

1

No

3

6

FALSE

TRUE

FL2

16/02/2021

Forest Green

Oldham

4

3

H

7

Yes

2

3

TRUE

FALSE

FL2

16/02/2021

Harrogate

Carlisle

1

0

H

1

No

1

1

TRUE

FALSE

FL2

16/02/2021

Newport County

Exeter

1

1

D

2

Yes

0

0

TRUE

FALSE

FL2

16/02/2021

Salford

Barrow

1

0

H

1

No

2

2

TRUE

TRUE

FL2

16/02/2021

Walsall

Cheltenham

1

2

A

3

Yes

2

0

TRUE

TRUE

CONF

16/02/2021

Aldershot

Solihull

1

3

A

4

Yes

3

0

TRUE

FALSE

CONF

16/02/2021

Barnet

Halifax

2

1

H

3

Yes

2

1

TRUE

TRUE

CONF

16/02/2021

Dag and Red

Bromley

1

0

H

1

No

2

1

FALSE

TRUE

CONF

16/02/2021

Wrexham

Woking

2

0

H

2

No

0

3

TRUE

FALSE

FLC

16/02/2021

Bristol City

Reading

0

2

A

2

No

4

3

TRUE

TRUE

FLC

16/02/2021

Middlesbrough

Huddersfield

2

1

H

3

Yes

1

1

TRUE

TRUE

FLC

16/02/2021

Wycombe

Derby

1

2

A

3

Yes

2

2

FALSE

FALSE

CONF

16/02/2021

Boreham Wood

Yeovil

2

3

A

5

Yes

3

6

TRUE

FALSE

CONF

16/02/2021

King’s Lynn

Notts County

0

1

A

1

No

4

2

TRUE

TRUE

CONF

16/02/2021

Maidenhead

Stockport

0

0

D

0

No

3

2

TRUE

TRUE

FLC

16/02/2021

Luton

Cardiff

0

2

A

2

No

0

1

TRUE

TRUE

FL2

17/02/2021

Mansfield

Bolton

2

3

A

5

Yes

2

0

FALSE

TRUE

FL1

17/02/2021

Wigan

Hull

0

5

A

5

No

1

0

TRUE

TRUE

EPL

17/02/2021

Burnley

Fulham

1

1

D

2

Yes

1

1

TRUE

TRUE

FLC

17/02/2021

Coventry

Norwich

0

2

A

2

No

1

3

TRUE

FALSE

FLC

17/02/2021

Millwall

Birmingham

2

0

H

2

No

2

2

TRUE

TRUE

FLC

17/02/2021

QPR

Brentford

2

1

H

3

Yes

5

1

FALSE

TRUE

FLC

17/02/2021

Swansea

Nott'm Forest

1

0

H

1

No

1

0

TRUE

TRUE

FL1

17/02/2021

Doncaster

Accrington

0

1

A

1

No

0

1

TRUE

TRUE

FLC

17/02/2021

Barnsley

Blackburn

2

1

H

3

Yes

1

1

FALSE

TRUE

 

[Peter_SSs]

1. What would that final column mean if True for one team and false for the other?
2. Do you really want the two columns you already have or prefer to go straight to that final column if possible?
3. Is this data in a formal Excel table (your use of [@Home Team])?

 

[PedroGomez9]

1. True/False columns 1 & 2 don't really have any bearing on the outcome of column 3. Column 1 is if the row is one of the team's last 5 home games, and column 2 is if it's one of the team's last 5 away games. Column 3 should ideally isolate the last 5 games irrespective of home or away.
2. As above, they are separate calculations so would need to stay.
3. Yes it's in a formal Excel table

 

[Peter_SSs]

how to get it so that it's their last 5 games irrespective of home or away.

Who is their?
You seem to be describing something that will be returned in a single column. However, there are two teams on each row.
What would you want returned in that single column if for team 'A' it is one of their last 'x' games but for team 'B' on the same row it is not one of their last 'x' games?

Perhaps you could clarify by copying this to a blank worksheet, manually completing the final column and posting it back here with any further explanation.

BTW, I have suggested alternative formulas for the original two columns of results. They are a little longer that what you are currently using but do lend themselves more fully to the table structure/nomenclature. Do they give the results you want?

PedroGomez9.xlsm
	C	D								L	M	N	O	P	Q
1	HomeTeam	AwayTeam								Is last 'x' home?	Is last 'x' away?	Last 'x' overall		Last x ->	3
2	B	A								FALSE	FALSE
3	B	D								FALSE	FALSE
4	B	C								FALSE	FALSE
5	E	C								FALSE	FALSE
6	C	A								FALSE	FALSE
7	D	A								FALSE	FALSE
8	A	E								FALSE	TRUE
9	B	A								TRUE	TRUE
10	A	B								FALSE	FALSE
11	E	C								TRUE	FALSE
12	A	E								FALSE	TRUE
13	B	D								TRUE	FALSE
14	D	B								FALSE	FALSE
15	C	B								FALSE	FALSE
16	A	D								FALSE	TRUE
17	C	B								TRUE	TRUE
18	A	B								FALSE	TRUE
19	E	D								TRUE	TRUE
20	D	C								TRUE	FALSE
21	C	B								TRUE	TRUE
22	A	C								TRUE	FALSE
23	D	A								TRUE	TRUE
24	B	E								TRUE	TRUE
25	E	D								TRUE	TRUE
26	C	A								TRUE	TRUE
27	D	C								TRUE	TRUE
28	A	C								TRUE	TRUE
29	A	C								TRUE	TRUE
Last n

Cell Formulas
Range	Formula
L2:L29	L2	=COUNTIF([@HomeTeam]:INDEX([HomeTeam],ROWS([HomeTeam])),[@HomeTeam])<=$Q$1
M2:M29	M2	=COUNTIF([@AwayTeam]:INDEX([AwayTeam],ROWS([AwayTeam])),[@AwayTeam])<=$Q$1