Mark Adjacent Column if Before or After Reference Date

[paulfitz320]

Hi,

Stuck with this, would appreciate some help please.

I need a formula for COLUMN I

Start at COL D (Horse) then go across to...
COL H - If it contains a 1 for that horse leave adjacent cell in COL I blank
COL H - If it contains a 2 for that horse put a 4 in COL I (If the date in COL B is AFTER the date where the 1 occured in COL H for that horse)
COL H - If it contains a 2 for that horse put a 3 in COL I (If the date in COL B is BEFORE the date where the 1 occured in COL H for that horse)

wpm hurdles won hurdles debut sp less then 9to2.xlsx
	A	B	C	D	E	F	G	H	I
1	Course	Date	Time	Horse	Fin Pos	Hurdle Runs Before	Race Type	hurdle debut yn	After 1 Next Door
2	Punchestown	27/04/2021	17:55	Adamantly Chosen	1	0	NHF	2	3
3	Fairyhouse	27/11/2021	13:01	Adamantly Chosen	2	0	Hurdle	1
4	Cork	08/01/2022	13:03	Adamantly Chosen	2	1	Hurdle	2	4
5	Thurles	10/02/2022	14:55	Adamantly Chosen	1	2	Hurdle	2	4
6	Cheltenham	18/03/2022	17:30	Adamantly Chosen	11	3	Hurdle	2	4
7	Punchestown	30/04/2022	17:30	Adamantly Chosen	4	4	Hurdle	2	4
8	Galway	28/07/2022	17:05	Adamantly Chosen	13	5	Hurdle	2	4
9	Listowel	19/09/2022	17:05	Adamantly Chosen	1	6	Chase	2	4
10	Punchestown	12/10/2022	15:55	Adamantly Chosen	1	6	Chase	2	4
11	Fairyhouse	04/12/2022	13:55	Adamantly Chosen	5	6	Chase	2	4
12	Limerick	26/12/2022	14:40	Adamantly Chosen	2	6	Chase	2	4
13	Leopardstown	05/02/2023	13:40	Adamantly Chosen	2	6	Chase	2	4
14	Cheltenham	15/03/2023	14:10	Adamantly Chosen	7	6	Chase	2	4
15	Fairyhouse	09/04/2023	16:55	Adamantly Chosen	6	6	Chase	2	4
16	Punchestown	28/04/2023	16:15	Adamantly Chosen	PU	6	Chase	2	4
17	Killarney	19/08/2022	16:05	Aghaboy	2	0	Hurdle	1
18	Ballinrobe	09/09/2022	15:50	Aghaboy	1	1	Hurdle	2	4
19	Listowel	18/09/2022	14:10	Aghaboy	1	2	Hurdle	2	4
20	Fairyhouse	04/12/2022	12:25	Aghaboy	7	3	Hurdle	2	4
21	Leopardstown	26/12/2022	13:10	Aghaboy	7	4	Hurdle	2	4
22	Galway	03/08/2023	17:40	Aghaboy	13	5	Turf	2	4
Sheet1

 

[awoohaw]

what is your tiebreaker rule, "Adamantly Chosen" has multiple 1's in column H? What version of Excel do you use?

 

[paulfitz320]

365

 

[paulfitz320]

Just giving this one a bump.

 

[awoohaw]

Just giving this one a bump.

try this:

Mr excel questions 70.xlsm
	A	B	C	D	E	F	G	H	I
1	Course	Date	Time	Horse	Fin Pos	Hurdle Runs Before	Race Type	hurdle debut yn
2	Punchestown	2021-04-27	17:55:00	Adamantly Chosen	1	0	NHF	2
3	Fairyhouse	2021-11-27	13:01:00	Adamantly Chosen	2	0	Hurdle	1
4	Cork	2022-01-08	13:03:00	Adamantly Chosen	2	1	Hurdle	2	4
5	Thurles	2022-02-10	14:55:00	Adamantly Chosen	1	2	Hurdle	2	4
6	Cheltenham	2022-03-18	17:30:00	Adamantly Chosen	11	3	Hurdle	2	4
7	Punchestown	2022-04-30	17:30:00	Adamantly Chosen	4	4	Hurdle	2	4
8	Galway	2022-07-28	17:05:00	Adamantly Chosen	13	5	Hurdle	2	4
9	Listowel	2022-09-19	17:05:00	Adamantly Chosen	1	6	Chase	2	4
10	Punchestown	2022-10-12	15:55:00	Adamantly Chosen	1	6	Chase	2	4
11	Fairyhouse	2022-12-04	13:55:00	Adamantly Chosen	5	6	Chase	2	4
12	Limerick	2022-12-26	14:40:00	Adamantly Chosen	2	6	Chase	2	4
13	Leopardstown	2023-02-05	13:40:00	Adamantly Chosen	2	6	Chase	2	4
14	Cheltenham	2023-03-15	14:10:00	Adamantly Chosen	7	6	Chase	2	4
15	Fairyhouse	2023-04-09	16:55:00	Adamantly Chosen	6	6	Chase	2	4
16	Punchestown	2023-04-28	16:15:00	Adamantly Chosen	PU	6	Chase	2	4
17	Killarney	2022-08-19	16:05:00	Aghaboy	2	0	Hurdle	1
18	Ballinrobe	2022-09-09	15:50:00	Aghaboy	1	1	Hurdle	2	4
19	Listowel	2022-09-18	14:10:00	Aghaboy	1	2	Hurdle	2	4
20	Fairyhouse	2022-12-04	12:25:00	Aghaboy	7	3	Hurdle	2	4
21	Leopardstown	2022-12-26	13:10:00	Aghaboy	7	4	Hurdle	2	4
22	Galway	2023-08-03	17:40:00	Aghaboy	13	5	Turf	2	4
paulfitz320

Cell Formulas
Range	Formula
I2:I22	I2	=LET(d, B2, Date1,INDEX($B$2:$B2,MATCH(D2&1,$D$2:$D2&$H$2:$H2,0)),h,H2, result,IFERROR(IF(h=1,"",IF(h=2,IF(d>Date1,4,IF(d<Date1,3,"")),"")),""),result)

 

[paulfitz320]

Thanks. Unfortunately the formula will not do what I need it to do. For example cell I2 should contain a 3. Also column D will contain many more rows with many groups of horses. When I paste it into the actual sheet with over 5000 rows, the formula does not adjust for the next horse and its group of runs.

Anyway thanks again, much appreciated.

 

[awoohaw]

Then you gave incomplete requirements. The ONLY requirement you list above when a "1" is in Column H is:

COL H - If it contains a 1 for that horse leave adjacent cell in COL I blank

So please give all the information for your calculations for cells in column I. The forum cannot read minds.

 

[Peter_SSs]

The ONLY requirement you list above when a "1" is in Column H is:

As far as I can see that is the only requirement when a "1" is in column H according to the expected results given in post 1.

365

Please add that 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’)

From your sample data it appears that the data for each horse is grouped together and the dates in column B are in ascending order for each horse. If that is always the case then does this do what you want?

23 10 30.xlsm
	B		D				H	I
1	Date		Horse				hurdle debut yn	After 1 Next Door
2	27/04/2021		Adamantly Chosen				2	3
3	27/11/2021		Adamantly Chosen				1
4	8/01/2022		Adamantly Chosen				2	4
5	10/02/2022		Adamantly Chosen				2	4
6	18/03/2022		Adamantly Chosen				2	4
7	30/04/2022		Adamantly Chosen				2	4
8	28/07/2022		Adamantly Chosen				2	4
9	19/09/2022		Adamantly Chosen				2	4
10	12/10/2022		Adamantly Chosen				2	4
11	4/12/2022		Adamantly Chosen				2	4
12	26/12/2022		Adamantly Chosen				2	4
13	5/02/2023		Adamantly Chosen				2	4
14	15/03/2023		Adamantly Chosen				2	4
15	9/04/2023		Adamantly Chosen				2	4
16	28/04/2023		Adamantly Chosen				2	4
17	19/08/2022		Aghaboy				1
18	9/09/2022		Aghaboy				2	4
19	18/09/2022		Aghaboy				2	4
20	4/12/2022		Aghaboy				2	4
21	26/12/2022		Aghaboy				2	4
22	3/08/2023		Aghaboy				2	4
paulfitz320

Cell Formulas
Range	Formula
I2:I22	I2	=IF(H2=1,"",3+(COUNTIFS(D$2:D2,D2,H$2:H2,1)>0))

 

[paulfitz320]

Thank you, Peter.

Your formula is not far off.

To finish it....
The grouping variable is Col D (Horse)
Whenever a horse does not contain the number 1 for any of its runs in COL H, then a 4 needs to returned in COL I.
I've posted another example below...
The horse Acapella Bourgeois contains no number 1 in its COL H, so a 4 should be returned in all its rows in COL I
The horse Adamantly Chosen is correct as its COL I contains a 3 (as its proceeded by a 1 in COL H), COL I is blank as its adjacent to a 1 in COL H, COL I contains 4 for all runs for that horse after the date where it was awarded a 1 in COL H. Your correct all horses are sorted by date in ascending order.
I hope that's clear. I will put up my Excel version as requested.
Thanks again.

wpm hurdles won hurdles debut sp less then 9to2.xlsx
	A	B	C	D	E	F	G	H	I
1	Course	Date	Time	Horse	Fin Pos	Hurdle Runs Before	Race Type	hurdle debut yn	wanted
2	Sligo	23/08/2023	20:10	A Penny A Hundred	1	0	NHF	2	3
3	Limerick	21/10/2023	14:05	A Penny A Hundred	1	0	Hurdle	1
4	Gowran Park	17/02/2018	15:10	Abbyssial	4	7	Hurdle	2	3
5	Cheltenham	14/03/2018	14:50	Abbyssial	PU	8	Hurdle	2	3
6	Fairyhouse	17/04/2018	17:50	Abbyssial	13	9	Hurdle	2	3
7	Punchestown	28/04/2018	17:35	Abbyssial	19	10	Hurdle	2	3
8	Punchestown	31/12/2022	15:42	Abi's Champ	2	0	NHF	2	3
9	Downpatrick	17/06/2023	17:10	Abi's Champ	1	0	NHF	2	3
10	Killarney	21/07/2023	17:25	Abi's Champ	1	0	NHF	2	3
11	Gowran Park	29/09/2023	17:35	Abi's Champ	3	0	NHF	2	3
12	Killarney	15/05/2023	17:55	Absurde	1	0	Hurdle	1
13	Ascot	20/06/2023	18:10	Absurde	2	1	Turf	2	4
14	Galway	01/08/2023	17:10	Absurde	6	1	Hurdle	2	4
15	York	26/08/2023	15:35	Absurde	1	2	Turf	2	4
16	Fairyhouse	14/01/2018	14:35	Acapella Bourgeois	PU	6	Chase	2	3
17	Cheltenham	15/03/2019	17:30	Acapella Bourgeois	17	6	Hurdle	2	3
18	Clonmel	26/03/2019	16:20	Acapella Bourgeois	1	7	Chase	2	3
19	Fairyhouse	22/04/2019	17:00	Acapella Bourgeois	3	7	Chase	2	3
20	Navan	07/12/2019	13:05	Acapella Bourgeois	10	7	Hurdle	2	3
21	Tramore	01/01/2020	14:20	Acapella Bourgeois	2	8	Chase	2	3
22	Gowran Park	23/01/2020	15:00	Acapella Bourgeois	6	8	Chase	2	3
23	Fairyhouse	22/02/2020	16:02	Acapella Bourgeois	1	8	Chase	2	3
24	Tramore	01/01/2021	14:37	Acapella Bourgeois	2	8	Chase	2	3
25	Gowran Park	28/01/2021	15:25	Acapella Bourgeois	5	8	Chase	2	3
26	Fairyhouse	27/02/2021	14:52	Acapella Bourgeois	1	8	Chase	2	3
27	Aintree	10/04/2021	17:15	Acapella Bourgeois	13	8	Chase	2	3
28	Tramore	01/01/2022	14:20	Acapella Bourgeois	PU	8	Chase	2	3
29	Kilbeggan	17/06/2019	18:00	Accidental	PU	3	Hurdle	2	3
30	Punchestown	27/04/2021	17:55	Adamantly Chosen	1	0	NHF	2	3
31	Fairyhouse	27/11/2021	13:01	Adamantly Chosen	2	0	Hurdle	1
32	Cork	08/01/2022	13:03	Adamantly Chosen	2	1	Hurdle	2	4
33	Thurles	10/02/2022	14:55	Adamantly Chosen	1	2	Hurdle	2	4
34	Cheltenham	18/03/2022	17:30	Adamantly Chosen	11	3	Hurdle	2	4
35	Punchestown	30/04/2022	17:30	Adamantly Chosen	4	4	Hurdle	2	4
36	Galway	28/07/2022	17:05	Adamantly Chosen	13	5	Hurdle	2	4
37	Listowel	19/09/2022	17:05	Adamantly Chosen	1	6	Chase	2	4
38	Punchestown	12/10/2022	15:55	Adamantly Chosen	1	6	Chase	2	4
39	Fairyhouse	04/12/2022	13:55	Adamantly Chosen	5	6	Chase	2	4
40	Limerick	26/12/2022	14:40	Adamantly Chosen	2	6	Chase	2	4
41	Leopardstown	05/02/2023	13:40	Adamantly Chosen	2	6	Chase	2	4
42	Cheltenham	15/03/2023	14:10	Adamantly Chosen	7	6	Chase	2	4
43	Fairyhouse	09/04/2023	16:55	Adamantly Chosen	6	6	Chase	2	4
44	Punchestown	28/04/2023	16:15	Adamantly Chosen	PU	6	Chase	2	4
wpm hurdles won hurdles debut s

Cell Formulas
Range	Formula
H2:H44	H2	=IF(AND(F2=0,G2="Hurdle"),1,2)
I2:I44	I2	=IF(H2=1,"",3+(COUNTIFS(D$2:D2,D2,H$2:H2,1)>0))

 

[Peter_SSs]

Whenever a horse does not contain the number 1 for any of its runs in COL H, then a 4 needs to returned in COL I.

Well that possibility/condition was definitely not included in post 1 so it isn't surprising that the suggested formula did not account for it.

Try this instead.

23 10 30.xlsm
	D				H	I
1	Horse				hurdle debut yn	wanted
2	A Penny A Hundred				2	3
3	A Penny A Hundred				1
4	Abbyssial				2	4
5	Abbyssial				2	4
6	Abbyssial				2	4
7	Abbyssial				2	4
8	Abi's Champ				2	4
9	Abi's Champ				2	4
10	Abi's Champ				2	4
11	Abi's Champ				2	4
12	Absurde				1
13	Absurde				2	4
14	Absurde				2	4
15	Absurde				2	4
16	Acapella Bourgeois				2	4
17	Acapella Bourgeois				2	4
18	Acapella Bourgeois				2	4
19	Acapella Bourgeois				2	4
20	Acapella Bourgeois				2	4
21	Acapella Bourgeois				2	4
22	Acapella Bourgeois				2	4
23	Acapella Bourgeois				2	4
24	Acapella Bourgeois				2	4
25	Acapella Bourgeois				2	4
26	Acapella Bourgeois				2	4
27	Acapella Bourgeois				2	4
28	Acapella Bourgeois				2	4
29	Accidental				2	4
30	Adamantly Chosen				2	3
31	Adamantly Chosen				1
32	Adamantly Chosen				2	4
33	Adamantly Chosen				2	4
34	Adamantly Chosen				2	4
35	Adamantly Chosen				2	4
36	Adamantly Chosen				2	4
37	Adamantly Chosen				2	4
38	Adamantly Chosen				2	4
39	Adamantly Chosen				2	4
40	Adamantly Chosen				2	4
41	Adamantly Chosen				2	4
42	Adamantly Chosen				2	4
43	Adamantly Chosen				2	4
44	Adamantly Chosen				2	4
paulfitz320 (2)

Cell Formulas
Range	Formula
H2:H44	H2	=IF(AND(F2=0,G2="Hurdle"),1,2)
I2:I44	I2	=IF(H2=1,"",3+OR(COUNTIFS(D$2:D2,D2,H$2:H2,1)>0,COUNTIFS(D$2:D$44,D2,H$2:H$44,1)=0))