If function?

davemorse

Board Regular
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

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Domenic

MrExcel MVP
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

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

Replies
1
Views
100
Replies
2
Views
97
Replies
6
Views
691
Replies
9
Views
151
Replies
21
Views
452

1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back