Conditional Formatting

[Venners]

Is there a way to highlight a cell that precedes another cell if other certain cells are matching? So in the dataset below I need to highlight all instances where a cross immediately precedes a shot but it needs to be the same id number to signal the different games and it needs to be the same team having the cross and shot.

Example.xlsx
	A	B	C	D	E
1	id	team	event_type	currentHalf	clockTime
2	3023090	Team A	Chance	secondHalf	80:32:00
3	3023090	Team A	Cross	secondHalf	80:32:00
4	3023090	Team B	event_A	secondHalf	80:16:00
5	3023090	Team A	Pass	secondHalf	80:13:00
6	3023088	Team C	event_A	secondHalf	67:51:00
7	3023088	Team D	Cross	secondHalf	67:51:00
8	3023088	Team C	Pass	secondHalf	67:50:00
9	3023088	Team D	event_A	secondHalf	67:24:00
10	3023088	Team C	Cross	secondHalf	67:24:00
11	3023088	Team C	event_A	secondHalf	67:02:00
12	3023088	Team D	Cross	secondHalf	67:02:00
13	3023088	Team C	event_A	secondHalf	66:41:00
14	3023088	Team D	event_A	secondHalf	65:58:00
15	3023088	Team C	Chance	secondHalf	65:58:00
16	3023088	Team C	Cross	secondHalf	65:43:00
17	3023088	Team D	Chance	secondHalf	65:43:00
18	3023088	Team C	event_A	secondHalf	65:39:00
19	3023088	Team D	Pass	secondHalf	65:39:00
20	3023088	Team D	event_A	secondHalf	65:05:00
21	3023088	Team C	event_A	secondHalf	64:53:00
22	3023088	Team D	Cross	secondHalf	64:53:00
23	3022889	Team E	event_C	secondHalf	95:21:00
24	3022889	Team D	event_B	secondHalf	95:06:00
25	3022889	Team E	event_A	secondHalf	94:51:00
26	3022889	Team D	event_A	secondHalf	94:49:00
27	3022889	Team E	Chance	secondHalf	94:49:00
28	3022889	Team E	Cross	secondHalf	94:37:00
29	3022889	Team D	Cross	secondHalf	94:37:00
30	3022889	Team D	event_A	secondHalf	94:31:00
31	3022889	Team E	Cross	secondHalf	94:31:00
32	3022889	Team E	event_A	secondHalf	94:19:00
33	3022889	Team D	event_A	secondHalf	94:16:00
34	3022889	Team D	event_A	secondHalf	94:13:00
Sheet1

 

[Joe4]

Can you try this:-

=IF(AND(A1=A2,B1=B2,C1="chance",C2="cross"),"true","false")

I am a beginner and appreciate if @Venners or @Joe4 could comment on this.

Thanks in advance.

It does close to the exact same thing my code does, but is overkill, and does have one issue.

Functions like AND and OR are boolean functions, meaning that they return TRUE or FALSE based on if the conditions are met.
Since they already return TRUE or FALSE, there is no need to wrap them in an IF function.

Secondly, you would not want to wrap the words "true" and "false" in quotes, as those would then be text values, and not boolean values.

So this would be valid:

=IF(AND(A1=A2,B1=B2,C1="chance",C2="cross"),TRUE,FALSE)

but can just be simplified to this:

=AND(A1=A2,B1=B2,C1="chance",C2="cross")

 

[Shesil]

Got it. Thank you very much!