If function?

davemorse

Board Regular
Joined
Mar 15, 2006
Messages
141
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
Joined
Mar 10, 2004
Messages
19,696
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
Joined
Mar 15, 2006
Messages
141
Ah so its like an exact statment using concatenates. EXCELLENT. This is is a great way to avoid nested IFs!
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top