Conditional Formatting for finding duplicates across multiple columns

[orgetorix]

Hello,

I am only familiar with the absolute basics of conditional formatting, but now I'd like to use it to find duplicates of more than one condition.

For example, say I have a sheet with three columns. Column A has letters A through H, column B has numbers 1-8 and column C has letters I through P.

What I am trying to accomplish is to have the cell in column A or the entire row highlighted in different colors depending on which columns match.

There should be four different colors:
1. Duplicates in A
2. Duplicates in A and B in the same row
3. Duplicates in A and C in the same row
4. Duplicates in A, B and C in the same row

I have embedded attached a sample document for clarification.

Thanks in advance for any and all help.

[Special-K]

So if ONLY B and C match (hence A is different to B and C) you don't want any highlighting?

[Special-K]

In your example NO columns match.
No columns match since A contains letters and B contains numbers, therefore column A weill NEVER match column B.
Additionally, although column C contains letters they are not the letters in column A so there are NO matches between columns A and C.
Nor A and B and C since B contains numbers.

You need to explain this more clearly and decide whether you are trying to match COLUMNs or ROWs

[orgetorix]

Thank you for your time and I'm sorry if I was unclear. I've edited and uploaded another workbook to try to illustrate what I am looking for.

The main criteria is column A. I know how to use conditional formatting to highlight a duplicate cell in column A, but the rest is where it breaks down for me.

In the examples, using car models, year and color, I'd like different highlight colors for:

1. Model only
2. Model and year only
3. Model and color only
4. Model year and color

I do not care about year and color.

[Pauleyb]

It would make things much easier to create helper columns. The first is a concatenation of the model and year, the next is a concatenation of the model and color, and the last is a concatenation of the model, year, and color. You can then use the technique you already know to find duplicates in a column.

[orgetorix]

You can then use the technique you already know to find duplicates in a column.

Thank you. I was able to create the helper columns with the concatenation criteria you outlined. Assuming that my helper columns are D-F with D being A&B, E being A&C and F being A&B&C, is there a way to hide those columns and have column A be formatted to highlight different colors based on duplicates in columns D-F?

[Pauleyb]

Yes. The conditional formula can reference other cells. So, you can set the range for the condition to be A:A (for all of column A) or A:C (if you want the coloring for columns A, B, C). Then your formula would be the one you have used in the past, but referencing columns D, E, or F, as appropriate.