Check if consecutive values are the same

[neodjandre]

I have 10 columns which either take the value "A" or "B"

The correct pattern should always be "A"s followed by "B"s something like:

A A A A B B B B B B
A A B B B B B B B B
A A A A A A A B B B

I need a check which returns TRUE if the user mixes up the pattern such as:

A A A B A A A B B B

How could I do this please?

many thanks,
Andrew

 

[Eric W]

Try:

=IFERROR(LOOKUP(2,1/(A1:I1="A"),COLUMN(A1:I1)-COLUMN(A1)+1)>MATCH("B",A1:I1,0),FALSE)

 

[Rick Rothstein]

This formula will return TRUE if the condition you describe exists and nothing otherwise...

=IF(SUMPRODUCT(--(A1:I1&B1:J1="BA")),TRUE,"")

You can change the "" to some value if you do not what okay values to report blanks.

 

[neodjandre]

thank you, Rick's solution is more neat but Eric's solution can be used on other scenarios I have

 

[Rick Rothstein]

thank you, Rick's solution is more neat but Eric's solution can be used on other scenarios I have

Can you give me an example of a scenario where you think my method would not work for you?