Highlighting duplicate data based on two conditions

Paul Newcome

No need to throw in the towel. Follow these steps...

Manually start typing the formula up until this point...

=IF(CONTAINS([ID#]@row;

Next you will click on the link in the helper box that says "Reference Another Sheet"

Then you will select the source sheet from the list on the left and then select your range. If you want to reference an entire column, you can just click on the column header. You can also rename the range at the top so that you know exactly what you are referencing.

Then in the bottom right corner of that window, you will click on the button to "Insert Reference". Now you have created a cross sheet reference. and the formula should look something like:

=IF(CONTAINS([ID#]@row; {Other Sheet Range Name}

From there you would finish typing out the formula

=IF(CONTAINS([ID#]@row; {Other Sheet Range Name}) = true; 1)

And there you have it. Give that a try...

Unknown

This content has been removed.

Paul Newcome

The ID# is actual numerical values isn't it? My apologies. The CONTAINS function doesn't like numerical values, but the FIND function will work. We are just going to have to change up our formula a touch.

Try this instead...

=IF(COUNTIFS({DRINV ID#}, FIND([ID#]@row, @cell) > 0) > 0, 1)

Basically the way this works is we use the COUNTIFS to count how many cells in the other range contain the ID#. If that count is greater than zero, then we check the box.

P.S.

Since you already have that particular cross sheet reference created, you can copy/paste the formula instead of having to retype and go through the steps to create the reference all over again.

Unknown

This content has been removed.

Paul Newcome

That's odd that you keep getting the error until you refresh the sheet.

Try logging out, clearing your browser's cache and cookies, then logging back in again. That may help with the error issue.

Unknown

This content has been removed.

Paul Newcome

Ok. So now that we have an IF looking at 1 sheet, we need to duplicate this formula (following the appropriate cross sheet referencing steps) for each sheet.

=IF(COUNTIFS({Sheet 2 ID#}, FIND([ID#]@row, @cell) > 0) > 0, 1)

=IF(COUNTIFS({Sheet 3 ID#}, FIND([ID#]@row, @cell) > 0) > 0, 1)

=IF(COUNTIFS({Sheet 4 ID#}, FIND([ID#]@row, @cell) > 0) > 0, 1)

so on and so forth.

Then we nest those IF's together:

=IF(COUNTIFS({DRINV ID#}, FIND([ID#]@row, @cell) > 0) > 0, 1, IF(COUNTIFS({Sheet 2 ID#}, FIND([ID#]@row, @cell) > 0) > 0, 1, IF(COUNTIFS({Sheet 3 ID#}, FIND([ID#]@row, @cell) > 0) > 0, 1, IF(COUNTIFS({Sheet 4 ID#}, FIND([ID#]@row, @cell) > 0) > 0, 1, ............................................................................

Once you have nested all of the IF's you will just close them all out at the same time by counting how many times you see IF in the final formula, and including a closing parenthesis for each.

Unknown

This content has been removed.

Paul Newcome

When nesting IF's like this you actually leave them all open until the very end whereas in your formula above, you are closing them out. Note the closing parenthesis that have been removed before starting the next IF statement. (There are also two IF's missing the "I". I fixed those as well.

=IF(COUNTIFS({DRINV ID#}; FIND([ID#]@row; @cell) > 0) > 0; 1;IF(COUNTIFS({S&A ID#}; FIND([ID#]@row; @cell) > 0) > 0; 1;IF(COUNTIFS({X3ANN ID#}; FIND([ID#]@row; @cell) > 0) > 0; 1;IF(COUNTIFS({SACPAINV ID#}; FIND([ID#]@row; @cell) > 0) > 0; 1;IF(COUNTIFS({Q&R ID#}; FIND([ID#]@row; @cell) > 0) > 0; 1;IF(COUNTIFS({OFCS ID#}; FIND([ID#]@row; @cell) > 0) > 0; 1;IF(COUNTIFS({CASEMAN ID#}; FIND([ID#]@row; @cell) > 0) > 0; 1)))))))

The space not being between the semicolon and the IF isn't going to hurt anything. I personally am a little OCD about having them in there, but that is a personal preference simply because it makes it easier for me to read.

Unknown

This content has been removed.

Paul Newcome

Try this...

Click inside of each reference one at a time. Do not select the entire reference. Just drop the cursor somewhere between the curly brackets.

Then look at the help box underneath.

Where the link is it will either say to edit the reference or create the reference. If it says to create the reference, then go ahead and create it. If it says to edit, click on the link and ensure that the proper range is in fact selected.

LisaB:-)

Hi @Paul Newcome, could you help me with a small differentiation on this formula? I have two date columns [Start] and [Agreed Start Date], and I would like a checkbox column to be checked if they are NOT the same date. Then I will add conditional formatting to alert me of the discrepancy. I used the original formula you suggested on this thread, but it didn't return the results I expected, and it checks dates that are the same, rather than dates that are not the same. Many thanks.

Paul Newcome

@LisaB:-) Are you looking for a formula that will only look at one row at a time?

LisaB:-)

Sorry for the delay, for some reason I didn't get a notification of your response. Yes please, one row at a time. I shall put the formula in the checkbox cell of each row.

Paul Newcome

@LisaB:-) Try this...

=IF(Start@row <> [Agreed Start]@row, 1)