Formula for identifying duplicate data between cells in two columns and noticing blank cells

Kye

Hello!

As the title suggests, I am trying to think of a Formula that will tell me if there is matching data in the two cells in question. I have a Helper Checkbox Column for this and this is generally the formula I'm concocting so far.

=IF(ISBLANK(Office@row), 0, IF(COUNTIF(Office:Department, Office@row) > 1, 1))

This formula will look at if either Office or Department is blank and will mark this as unchecked but if there are values in both columns but not matching, it will still mark as checked. Is there an alteration that can be made to this formula or do I need a second helper column?

Paul Newcome

Are you able to provide a screenshot for context?

Kye

Of course, here you go.

The checkbox column is the helper column, the left column is Office and right is Department. I have added some Conditional Formatting so it will turn blue if they are presumed matching (checkbox ticked).

You can see that AU-Sydney and Blank are matching along with Contractor - Avovision and Contrac... etc.

I hope this helps.

Paul Newcome

So if both columns are filled in but do not match, it should check the box?

=IF(AND(Office@row <> "", Department@row <> "", Office@row <> Department@row), 1)

Kye

Ideally, I need to check to see if the columns are matching so if the 'Office' cell says "US-New York" and 'Department' cell says "US-New York" it will check the box.

Then if any box is blank, then uncheck the box.

Also, upon testing that formula, it looks like it checks values in the respective cells but only checks the box if the values are different. "US - New York" & "US-New Yo" and not "US-New York" & "US-New York" as an example.

Paul Newcome

=IF(AND(Office@row <> "", Department@row <> "", Office@row = Department@row), 1)