Conditional formatting based on several cell values

[alecambo]

Good day! I have dataset that looks like this (let's assume that "Name" is column A and "Sequence" is column B):
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Sequence[/TD]
[/TR]
[TR]
[TD]BUNNY[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]BUNNY[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]BUNNY[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]BUNNY[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]TIGER[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]TIGER[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]TIGER[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]TIGER[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]DOG
[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]DOG
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]DOG
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]DOG
[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

I am in the need of a simple formula highlights the 4 "Sequence" cells in red when the sequence for each "Name" is any different from the two following sequences: "1 ,2, 3, 4" or "1, 2, 3, -": in the given example, DOG should turn red since the sequence is different from the two allowed ones.

Thanks in advance,

Alejandro

 

[Special-K99]

Will there always be four numbers for each animal?
If not, what happens if you get this sequence

BUNNY 1
BUNNY 1
BUNNY 2
BUNNY 3
BUNNY 4

I see TWO four number sequences, the 1234 you've stated starting at row 2 and 1123 starting at row 1
1123 is not a valid sequence so should that be highlighted?

 

[alecambo]

Will there always be four numbers for each animal?
If not, what happens if you get this sequence

BUNNY 1
BUNNY 1
BUNNY 2
BUNNY 3
BUNNY 4

I see TWO four number sequences, the 1234 you've stated starting at row 2 and 1123 starting at row 1
1123 is not a valid sequence so should that be highlighted?

You are correct, there will always be four numbers for each animal.

 

[Special-K99]

We can do this with a helper column, there may be a way with array formulas to avoid the helper column, though I cant see it at the moment.

in C2
=(COUNTIF(A$2:A2,A2)=B2)*1
and copy down the column

Then
Select the range to highlight

Conditional Formatting
New Rule
Use a formula to determine...

=AND(A2<>"",SUMIF(A$2:A$1000,A2,C$2:C$1000)=4)

Format as red