Returning a value based on duplicates in two columns

George S

I'm trying to create a formula that will return a value if criteria from two different columns are duplicates within their own columns. It would look at Column A and look for duplicates. If it finds a duplicate, it then looks at Column B to see if the rows that meet that criteria have a duplicate. If it finds a duplicate, it checks the box in a separate column. Column A will not be a duplicate of Column B. They will be different duplicates.

Both formulas work on their own, but together, UNPARSEABLE. Here is what I have:

=COUNTIFS([Column A]:[Column A], [Column A]@row) > 1), ([Column B]:[Column B], [Column B]@row) > 1)

brianschmidt

For your checkbox column, I would use this formula:

=IF(AND(COUNTIF([Column A]:[Column A], [Column A]@row) > 1, COUNTIF([Column B]:[Column B], [Column B]@row) > 1), 1, 0)

Hope this helps!:)

George S

Thanks. This is very close, but not quite what I am trying to achieve. I need the second formula to be reliant on the first. In other words, in your example above, the third checkbox should NOT be checked. "4" is a duplicate in column A, but "5" and "6" are not in column B. If it finds duplicates in rows 5 and 6 in column A, I want it to look for duplicates only in rows 5 and 6 in column B.

Emory

Hello,

I hope I understand, or at least this is in the right direction for what you are looking for.

=IF(COUNTIF([Column2]:[Column2], [Column2]@row) > 1, IF(COUNTIF([Column3]:[Column3], [Column3]@row) > 1, 1, 0), 0)

I think you are looking for a duplicate check in column A. Then, if true, a duplicate check for column B. If both are true, then the box should be checked.

If not, I hope the nested IF function may lead you to your solution.

George S

I'm not sure what I need is possible. I need it to look for duplicates in Column A. Once it finds duplicates, I need it to look at those duplicates (as groups of duplicates) and look for duplicates in Column B (specifically to the duplicates in Column A). It may be one step too far for Smartsheet.

Emory

Sorry, I am not fully grasping what you are looking for.

Would it be possible for you to include a screenshot showing the three columns, and manually selecting what the resulting 3rd column should be. And a short description of the logic that example should be doing?

I'm unsure if what you are asking is possible. But if I can understand what you are looking for, I am willing to try and find a solution.

George S

I'm hoping this helps to explain the issue. And Thanks for your help! There can be multiple duplicates in each column, but I am looking to find the duplicates in Column A that share duplicates in Column B. In the example above, SN1234 has a duplicate, but because the Asset Type is different, it should not be flagged. However, SN3456 has a duplicate which also has an Asset Type duplicate. Therefore, it should be checked. Does that better illustrate the issue?

heyjay

You might need a helper column to evaluate each row. I would textjoin cell A and B, then use IF + COUNTIF on the Duplicate (checkbox columns) column.

Emory

=IF(AND(COUNTIF([Column A - Serial Number]:[Column A - Serial Number], [Column A - Serial Number]@row) > 1, COUNTIFS([Column A - Serial Number]:[Column A - Serial Number], [Column A - Serial Number]@row, [Column B - Serial Number]:[Column B - Serial Number], [Column B - Serial Number]@row) > 1), 1, 0)

Hello. Please try this and let me know if it is what you are looking for.

This formula checks if the value in "Column A - Serial Number" at the current row is a duplicate in the entire "Column A - Serial Number" and if the combination of values in "Column A - Serial Number" and "Column B - Serial Number" at the current row is also a duplicate in the entire dataset. If both conditions are met, the checkbox will be checked (returning 1), indicating a "Yes" for duplication; otherwise, it will be unchecked (returning 0), indicating a "No" for duplication.

George S

This (heyjay) was the answer. I built the helper column where I combined the two columns, then weeded out the duplicates from the helper column. Thank you all so much. I appreciate everyone that jumped in to help.

@Emory, I tried the formula you wrote above, but it was still capturing all of the duplicates (including the wrong ones). I think it was too much to do without using the helper column.

Thanks for all of the help!

In the Helper Column

=IF([Column A]@row = "", "", JOIN([Column A]@row + [Column B]@row))

In the New Column

=IF([Helper Column]@row = "", "", IF(COUNTIF([Helper Column]:[Helper Column], =[Helper Column]@row) > 1, 1, 0))