Column to that will match names with previous instances --> test those instances against certain conditions

[Michaelson]

Hello all,

Here's a pic with the data I think is relevant. This is Excel 2007 on XP btw.

I need to generate four more columns:

Col AQ ('Home Team Returning From Bye')
Col AR ('Away Team Returning From Bye')
Col AS ('Home Team Backing up from Monday')
Col AT ('Away Team Backing up from Monday')

Column AQ needs to return results in instances where the Home Team's previous game was two rounds earlier. So Melbourne in round 12 above were a team coming off the bye: their previous game was in round 10.

Note: it doesn't matter whether the previous game was played at home or away. This is the same for every column.

Column AR is the same as AQ but testing the Away Teams.

Columns AS and AT have to list results where the relevant team's previously played game was on a Monday. In Round 9 pictured above Manly and Canberra played on Monday night. So for their round 10 games I need to get a positive hit for these teams in the 'Backing up from Monday' columns that apply (these teams both played away games in Round 10, so the positive results should appear in Col AT).

For these columns, just to complicate matters, I don't want it to return matches for teams whose last game was a Monday, but came before a bye. So Manly in Round 12 last played on a Monday, but they had the bye in between so I don't want to pick that up as a hit.

Thankyou in advance for any help! I've learned a lot working on this spreadsheet and have been able to do most things I've needed to do with IFs and COUNTIFS and the like, but for this I don't really know where to start!

 

[Michaelson]

Still haven't been able to work this out, just bumping in the hope someone may be able to help?

 

[Peter_SSs]

One reason you may have not had help earlier is the way you have provided your sample data. It cannot be copied from that image so if somebody wants to test a formula, they would have to type a lot of that data from the screen to their workbook. You will get much better/faster help if you post copyable sample data directly into your post. My signature block has suggestions for that.

I have assumed that the values in column C are text. If they are dates, formatted to show just the day, my formulas for columns AS and AT will need to be modified.

Clearly my results for rounds 9 and 10 will be problematic since I don't have all the round 9 data.

All formula copied down (and up).

Excel Workbook
	A	B	C	E	I	AQ	AR	AS	AT
68	9	8/05/11	Sunday	St George-Illawarra	North Queensland	TRUE	TRUE	FALSE	FALSE
69	9	9/05/11	Monday	Manly	Canberra	TRUE	TRUE	FALSE	FALSE
70	10	13/05/11	Friday	Canterbury	St George-Illawarra	TRUE	FALSE	FALSE	FALSE
71	10	13/05/11	Friday	Penrith	Brisbane	TRUE	TRUE	FALSE	FALSE
72	10	14/05/11	Saturday	Melbourne	Canberra	TRUE	FALSE	FALSE	TRUE
73	10	14/05/11	Saturday	North Queensland	Parramatta	FALSE	TRUE	FALSE	FALSE
74	10	14/05/11	Saturday	South Sydney	Wests Tigers	TRUE	TRUE	FALSE	FALSE
75	10	15/05/11	Sunday	Newcastle	NZ Warriors	TRUE	TRUE	FALSE	FALSE
76	10	15/05/11	Sunday	Cronulla	Sydney Roosters	TRUE	TRUE	FALSE	FALSE
77	10	16/05/11	Monday	Gold Coast	Manly	TRUE	FALSE	FALSE	TRUE
78	11	20/05/11	Friday	Canberra	Canterbury	FALSE	FALSE	FALSE	FALSE
79	11	21/05/11	Saturday	Wests Tigers	Penrith	FALSE	FALSE	FALSE	FALSE
80	11	22/05/11	Sunday	NZ Warriors	South Sydney	FALSE	FALSE	FALSE	FALSE
81	11	22/05/11	Sunday	Sydney Roosters	Newcastle	FALSE	FALSE	FALSE	FALSE
82	11	23/05/11	Monday	Parramatta	Cronulla	FALSE	FALSE	FALSE	FALSE
83	12	27/05/11	Friday	Manly	Brisbane	TRUE	TRUE	FALSE	FALSE
84	12	27/05/11	Friday	Canterbury	Gold Coast	FALSE	TRUE	FALSE	FALSE
85	12	28/05/11	Saturday	Newcastle	Parramatta	FALSE	FALSE	FALSE	TRUE
86	12	28/05/11	Saturday	North Queensland	Sydney Roosters	TRUE	FALSE	FALSE	FALSE
87	12	29/05/11	Sunday	Melbourne	Cronulla	TRUE	FALSE	FALSE	TRUE
88	12	29/05/11	Sunday	St George-Illawarra	Wests Tigers	TRUE	FALSE	FALSE	FALSE
Team Info

 

[Peter_SSs]

On second thoughts, this is simpler and I think still does the trick.

Excel Workbook
	A	B	C	E	I	AP	AQ	AR	AS	AT
68	9	8/05/11	Sunday	St George-Illawarra	North Queensland	9: St George-Illawarra: North Queensland: Sunday	TRUE	TRUE	FALSE	FALSE
69	9	9/05/11	Monday	Manly	Canberra	9: Manly: Canberra: Monday	TRUE	TRUE	FALSE	FALSE
70	10	13/05/11	Friday	Canterbury	St George-Illawarra	10: Canterbury: St George-Illawarra: Friday	TRUE	FALSE	FALSE	FALSE
71	10	13/05/11	Friday	Penrith	Brisbane	10: Penrith: Brisbane: Friday	TRUE	TRUE	FALSE	FALSE
72	10	14/05/11	Saturday	Melbourne	Canberra	10: Melbourne: Canberra: Saturday	TRUE	FALSE	FALSE	TRUE
73	10	14/05/11	Saturday	North Queensland	Parramatta	10: North Queensland: Parramatta: Saturday	FALSE	TRUE	FALSE	FALSE
74	10	14/05/11	Saturday	South Sydney	Wests Tigers	10: South Sydney: Wests Tigers: Saturday	TRUE	TRUE	FALSE	FALSE
75	10	15/05/11	Sunday	Newcastle	NZ Warriors	10: Newcastle: NZ Warriors: Sunday	TRUE	TRUE	FALSE	FALSE
76	10	15/05/11	Sunday	Cronulla	Sydney Roosters	10: Cronulla: Sydney Roosters: Sunday	TRUE	TRUE	FALSE	FALSE
77	10	16/05/11	Monday	Gold Coast	Manly	10: Gold Coast: Manly: Monday	TRUE	FALSE	FALSE	TRUE
78	11	20/05/11	Friday	Canberra	Canterbury	11: Canberra: Canterbury: Friday	FALSE	FALSE	FALSE	FALSE
79	11	21/05/11	Saturday	Wests Tigers	Penrith	11: Wests Tigers: Penrith: Saturday	FALSE	FALSE	FALSE	FALSE
80	11	22/05/11	Sunday	NZ Warriors	South Sydney	11: NZ Warriors: South Sydney: Sunday	FALSE	FALSE	FALSE	FALSE
81	11	22/05/11	Sunday	Sydney Roosters	Newcastle	11: Sydney Roosters: Newcastle: Sunday	FALSE	FALSE	FALSE	FALSE
82	11	23/05/11	Monday	Parramatta	Cronulla	11: Parramatta: Cronulla: Monday	FALSE	FALSE	FALSE	FALSE
83	12	27/05/11	Friday	Manly	Brisbane	12: Manly: Brisbane: Friday	TRUE	TRUE	FALSE	FALSE
84	12	27/05/11	Friday	Canterbury	Gold Coast	12: Canterbury: Gold Coast: Friday	FALSE	TRUE	FALSE	FALSE
85	12	28/05/11	Saturday	Newcastle	Parramatta	12: Newcastle: Parramatta: Saturday	FALSE	FALSE	FALSE	TRUE
86	12	28/05/11	Saturday	North Queensland	Sydney Roosters	12: North Queensland: Sydney Roosters: Saturday	TRUE	FALSE	FALSE	FALSE
87	12	29/05/11	Sunday	Melbourne	Cronulla	12: Melbourne: Cronulla: Sunday	TRUE	FALSE	FALSE	TRUE
88	12	29/05/11	Sunday	St George-Illawarra	Wests Tigers	12: St George-Illawarra: Wests Tigers: Sunday	TRUE	FALSE	FALSE	FALSE
Team Info

 

[Michaelson]

Thankyou so much for this!

If I had realised how important it was to post in an easy to copy/paste format I would have. I'm very sorry, and grateful, that you took the time to type out all that data! I will certainly make sure to post using Excel Jeanie in the future.

Can I ask exactly how those formulas work? The criteria parts in particular, and having them equal zero and one respectively?

Thanks again!

 

[Peter_SSs]

.. grateful, that you took the time to type out all that data!

I wasn't that keen.
Luckily I had access to a draw in Excel so a Copy/Paste or two and I pretty much had it.

Can I ask exactly how those formulas work? The criteria parts in particular, and having them equal zero and one respectively?

You have all the critical info for each row in your concatenated values in col AP.

Lets take the formula in AQ88 (home team returning from a bye?):
=COUNTIF(AP$2:AP87,A88-1&"*"&E88&"*")=0

A88 (=12) is the round relating to that row.
E88 is the home team (St George-Illawarra) in that row.

So the formula becomes:
=COUNTIF(AP$2:AP87,12-1&"*"&"St George-Illawarra"&"*")=0
=COUNTIF(AP$2:AP87,"11*St George-Illawarra*")=0

The green part counts the rows above row 88 in column AP that ..
- start with 11
- followed by any text (* is a wildcard for any number of characters)
- followed by "St George Illawarra"
- followed by any text

If St George had played in round 11, the result of this count would be 1. As it is, because StG did not play in round 11, this count is 0.
So putting this count = 0 returns TRUE because StG did not play in round 11.

Similar for the away team returning from a bye.
So now to backing up from Monday the previous round. Take the formula in AT87 (away team backing up from Monday?):

=COUNTIF(AP$2:AP86,A87-1&"*"&I87&"*"&"Monday")=1
=COUNTIF(AP$2:AP86,12-1&"*"&"Cronulla"&"*"&"Monday")=1
=COUNTIF(AP$2:AP86,"11*Cronulla*Monday")=1

Since the red pattern (start with 11 followed by anything followed by Cronulla followed by anything followed by Monday at the end) is found in cell AP82, the COUNTIF returns a 1 and therefore the formula returns TRUE since Cronulla played on Monday in the previous round.

 

[Michaelson]

You are a true champion and I say that even though I now know you didn't type out a mass of data into a spreadsheet to help me out.

Really, thanks a bunch!