Find Number of Duplicates within Multiple Columns

Sara H
Sara H ✭✭✭✭
edited 06/01/23 in Formulas and Functions

I need to create a COUNTIF formula (I assume) that will count the number of duplicates of a number in at least 10 different columns. I will eventually create a notification for the end result but I can't figure out how to do it over more than one column without only being able to compare the other columns to just one column.

I have combed through many different questions about duplicates and can't seem to find a formula that will work for my situation.

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would have to have it set up on a row by row basis so that "@row" is the criteria on every row. You can then create a report or filter to show any rows that are flagged if you wanted to just look at all duplicates in general.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    You would have to use a COUNTIFS formula to do that. A COUNTIF will only count one criteria in one range. You would have to have multiple COUNTIFS formulas if you are looking for duplicates of different numbers. A single COUNTIFS is going to only do one number well.


    =COUNTIFS([Employee ID 1]:[Employee ID 1], 1234, [Employee ID 2]:[Employee ID 2], 1234, etc...


    Replace 1234 with your number you are looking for. You will have to have another formula for each number you are wanting to count duplicates for.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Sara H
    Sara H ✭✭✭✭
    edited 06/01/23

    @Kleerfyre Thank you. There might be hundreds of numbers and I don't know which would be duplicates. I was hoping to find a way to identify duplicates that showed up no matter which column they were in. For example, if there was a number in column 3 (example: an employee ID) and then the same number was in column 7, I was hoping for a flag saying "2" in a helper column to identify that the number showed up twice in any row in all 10 columns.

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    Have you looked at the Formula Handbook Template for all the different functions? This has been my go to in creating formulas that are unique to a sheet. I would look there and try to put together something. If you get stuck with that, I would be more than happy to help more if you needed it.


    To find the Formula Handbook, go to the templates section in Smartsheet and just type Formula Handbook in the search there.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Sara H
    Sara H ✭✭✭✭

    @Kleerfyre Thank you! I don't think I even knew about that Formula Handbook.

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    Yeah, that thing is very handy!

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Sara H
    Sara H ✭✭✭✭

    @Kleerfyre I don't see a way to do this without having a specific criterion to reference. I may need to try something else. I am a bit confused now.

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    Yeah, if you don't have a specific criteria to be able to reference, it will be hard to just pull any numbers that are duplicates from all columns. I will look in my collection of formulas that I have created to see if I have anything that might do what you are looking for.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Sara H
    Sara H ✭✭✭✭
    edited 06/02/23

    @Paul Newcome Would you take a look at the question above? I swear I've seen your name when helping with duplicates formulas. I think the challenge I am running into is 2 fold - I want the formula to look across multiple columns (I think that one can be managed) and and I don't have a specific criterion (I want it to look for any duplicates vs a specific duplicate). Any thoughts would be greatly appreciated!


    EDITED: I think this will work - https://community.smartsheet.com/discussion/77471/highlight-duplicates-across-multiple-columns

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    So the only thing I have close to helping is the INDEX/MATCH formula set. Not sure if its dynamic enough to search for a value in one place and see if there are matches in another place. Maybe trying to figure out if a helper column/s could be used to help with this? I don't think I have much more than that.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would have to have it set up on a row by row basis so that "@row" is the criteria on every row. You can then create a report or filter to show any rows that are flagged if you wanted to just look at all duplicates in general.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!