Need help with conditional formatting to compare values within two columns.

Question

I am trying to use conditional formatting to see if there are duplicates in two sets of 2 separate (AA, AE, and AQ) columns. I had this working last year with this spreadsheet, but this year after I copied it, it is not working. I have attached two screen shots of the old and new spreadsheets, showing the formulas. I tried copying and pasting the formula that worked into the new copy of the spreadsheet. It gave me a message; see screenshot. Can anyone help me write a formula that will compare column AE with AQ for duplicates? I must also compare column AA with AQ for duplicates. I appreciate you time and expertise. I'm using Windows 11.

Answer 1

=$AE3:$AE300,$AQ3:$AQ300 is not a useful formula for conditional formatting - it does not return TRUE or FALSE.

The rule that worked is of another type - it is created by selecting the range(s), then selecting Conditional Formatting > Highlight Cells Rules > Duplicate Values...

You can create such a rule in your new workbook too.

Answer 2

Hi Gwen. I am an Excel user like you.

As Hans said the formula you see was created by Excel using the Duplicate Value automated process and cannot be manually entered as a formula. You can re-create that formula by going through that process again or if you want a formula that can be copied to use in another workbook you can enter your own formula using Conditional Formatting>New Rule>Use a Formula using this formula:

1. Select all of the cells that you want formatted. In the illustration below I only selected cells in Column AA. If desired, you can select cells in multiple columns so that a whole row or section of a row is formatted.
2. In the Format Values where this Formula Is True window enter this formula

=$AA3=$AE3

NOTE: The selection you make must begin with the same row that is used in the formula. So, for this formula your selection of where to apply the Conditional Formatting must begin with Row 3. If you begin your selection of where to apply the Conditional Formatting in a different row, then adjust the formula to match that.

You can do the same with the other formula by entering it into another New Rule:

=$AA3=$AQ3

Answer 3

Thanks for your advice, Hans. I have tried writing formulas, but they don't work. I need step by step guidance, unfortunately.

Answer 4

I tried to follow your thoughtful directions, but had no luck. The formatting did not appear in the duplicate cell. Instead it appeared in blank cells within the column. I took a screen shot to illustrate. Thank you. If you have any other advice for this problem, I'd appreciate your help.

Answer 5

The problem is with the range you selected for the Conditional Formatting. You selected the entire column for Column AE which then applies the formatting for the first Row 3 Comparison to the cell(s) in Row 1 since that is the first row of the selection.

As I stated earlier your selection to apply the formatting MUST begin in Row 3 to match the formula. Also, the formatting is only applied to random cells in Column AQ so you will not get accurate results there either.

That is why AE1 and AQ1 are formatted because the first comparison which is in in Row 3 is TRUE so it formatted the first row of the range which is Row 1.

The easiest way to fix this is probably to start over.

1. Delete the current rule.

2 Select AE3:AE?? (I can't see how far down your data goes--thus the question marks). If you also want Column AQ to be formatted, use the Control key to add a range. With control held down select AQ3:AQ?? (Use the same end row in the range as you did for Column AE).

3. With those ranges selected, go to Conditional Formatting>New Rule>Use a Formula and enter the formula as you had it.

=$AE3=$AQ3

That will apply the formatting to the correct row where the data actually is.