Excel Magic Trick 999: MATCH Rather Than OR function to Conditionally Format Row With OR Criteria

See how to use MATCH instead of OR function to conditionally format a row when you have OR Criteria. Match one of three dates to conditionally format row when the date is one date or a second date or a third date. Mixed Cell References

[mjSnap]

Sorry, my last post didn't format correctly. Each animal name is the start of a new row. Great work on all the videos by the way. Excellent education and information! Subscribed!

[ExcelStrategy]

The difference between the OR function and the MATCH function in processing the empty cell it's very usefull.

[srizki]

Excellent, I used to use, =SUMPRODUCT(--ISNUMBER(SEARCH(A4, D4:D6)))
after highlighting the columns that need to be highlighted.
Your formula is better and easy.

Thanks

Thanks

[mjSnap]

(continued from previous comment)

The 4 attributes can be totally different attributes from the other items, the same, or partially the same.

What I need to be able to do is sort the entire sheet so that items with the most attributes in common are in the top row. The list should procede downward with items that have fewer and fewer attributes in common. The last row would be the item with the least in common with any of the others.

My apologies. This will take 3 comments. (continued in next)

[mjSnap]

I have a problem that will take the space of 2 comments to describe:

Not sure what to search for to see whether you've covered this:

I have a sheet with 65 columns. The first column is the item name. The other 64 represent different attributes assigned to the item in the first column. Each column in a row can contain a 0 space if it does not have the attribute, or a 1 if it does. The catch is, each item on the left only (and always) has 4 out of the 64 attributes. (continued in next comment)

[RoSiYou]

Ough. I would like to have Your excel knowledge. Or at least understand to all your tutorials :)

[ronwb]

Its hard for me to get all these new Excel keyboard shortcuts.
What's the trick to getting them?

[mjSnap]

(cont'd from previous)

A simplified, example using only the item column and 6 attribute cols (for this, only 2 of the 4 cols can have 1's. Null cells are 0's).

Horse, 1, 0, 1, 0, 0, 0
Cow, 0, 0, 1, 1, 0, 0
Cat, 0, 1, 0, 1, 0, 0
Dog, 1, 1, 0, 0, 0, 0
Mouse, 0, 1, 1, 0, 0, 0
Chicken, 0, 0, 0, 0, 1, 1
Goose, 1, 0, 0, 0, 0, 1
Rabbit, 1, 1, 0, 0, 0, 0

How would I sort this so that the most common combos are at the top, and least common at the bottom? (and how to do it for my 65-col table?

Thanks!

[donnachen]

Hi Mike. How could I submit an Excel worksheet question? Could I email you my excel reports and question?