Returning a value based on duplicates in two columns
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)
Answers
-
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!:)
-
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.
-
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.
-
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.
-
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.
-
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?
-
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.
...
-
=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.
-
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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!