If function?

[davemorse]

Hi all,

This is my predicament. Ive got a number of rows of data within a sheet. There are 10 columns of data to fill in. In each cell is a dropdown which lists different types of animals. What i want to do is identify which rows have the following orders from left to right (there could be cells with blanks).
cat, dog, hamster, rabbit, horse. However I have blank cells thorughout the list which i need ignoring.

So (from column A-J) I'm looking for this order of animals in any of the cells - the following are correct:

cat, dog, hamster, rabbit, horse, blank, blank, blank, blank, blank, blank
cat, blank, blank, blank, dog, hamster, blank, rabbit, horse blank.

The following isnt correct as dog and hamster are the wrong way round:
cat, hamster, blank, blank, dog,blank , blank, rabbit, horse blank.

Whats the best way to identify if each row complies to the order I want. I was thinknig IF statements but i think there may be too many variations with the IF's.
Does any one have any ideas...

Thanks

 

[Domenic]

If the only values that each row can contain are the five mentioned along with blanks, try...

=(CONCATENATE(A2,B2,C2,D2,E2,F2,G2,H2,I2)=CONCATENATE("cat","dog","hamster","rabbit","horse"))+0

The formula will return 1 when the order is in compliance, and 0 when it's not.

Hope this helps!

 

[davemorse]

Ah so its like an exact statment using concatenates. EXCELLENT. This is is a great way to avoid nested IFs!