Duplicates

Sjjensen91
edited 12/09/19 in Formulas and Functions

How do i create a formula that shows there are duplicates.

 

I would like it to have a check box in a column showing that "Sarah Jensen" has duplicate expense submission based off Consultant Name, Date of Receipt,Total Expense. 

 

dupl.PNG

Comments

  • sean59916
    sean59916 ✭✭✭
    edited 06/07/19

    Hi, 

    Here is how I worked this request - 

    1. added a helper column to concatenate the fields that you are looking to compare, i.e. =[Consultant Name]@row + [Date of Receipt]@row + [Total Expense]@row
    2. added a checkbox with the following formula =IF([Consultant Name]@row = "", "", IF(COUNTIF(Helper:Helper, Helper@row) > 1, 1, 0))
    3. Hide the helper column

     

    See the screenshot - I hope this helps?

     

    Sean

     

     

    2019-06-07_16-29-00.jpg

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could skip the helper column and just include the additional column criteria in a COUNTIFS instead of a COUNTIF.

    .

    =COUNTIFS([Consultant Name]:[Consultant Name], [Consultant Name]@row, [Date of Receipt]:[Date of Receipt], [Date of Receipt]@row, [Total Expense]:[Total Expense], [Total Expense]@row)

     

    This will count how many rows have the same data in those 3 columns as the row the formula is currently sitting on. In your screenshot, going down the rows it would populate 

    2

    2

    1

    .

    We can then leverage that result in an IF statement to check your box if there are any rows that have a number higher than 1 meaning there is more than one row with matching data.

     

    =IF(COUNTIFS(..........) > 1, 1, 0)

    .

    So the finalized formula in the checkbox column would look something like this...

     

    =IF(COUNTIFS([Consultant Name]:[Consultant Name], [Consultant Name]@row, [Date of Receipt]:[Date of Receipt], [Date of Receipt]@row, [Total Expense]:[Total Expense], [Total Expense]@row) > 1, 1, 0)

    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

  • sean59916
    sean59916 ✭✭✭

    Good job Paul.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!