Identify Duplicates with 2 criteria

KevinL
KevinL ✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hello,  I am trying to flag duplicates that meet 2 criteria.  I have a sheet that has Request type actions (Add, Revise, Delete, etc.) and also a Part ID column.  I am trying to identify duplicate Part ID that also have the same Request type.  See screenshot.

Ex: Row 1 and 4 should flag as duplicates as they are the same part ID and both of those unique part number rows identified as duplicates are also "ADD" requests.    Row 2 is not flagged because even though the same Part ID, it is a "Delete" request.

All I can find are solutions with identifying 1 criteria....like this:  

=IF(COUNTIF([Part ID]:[Part ID], [Part ID]1) > 1, 1,0)

Any help would be appreciated.

Mock.JPG

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Kevin,

    Would it work if you had the different status options in a drop-down list instead?

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could use a helper column (I'll call it "Helper" for this example). In the Helper column use this formula...

     

    =[Part ID]@row + " " + MATCH("CHECK", Add@row:[Cost Exchange]@row, 0)

    .

    This will give you the part ID followed by a space and then a number (using your above screenshot) of 1 - 6 depending on which column has "CHECK" in it.

    Your helper column for the above screenshot would look like this...

     

    A 1

    A 3

    B 5

    A 1

    C 4

    D 1

    E 1

    .

    You could then use your duplicate formula on the Helper column.

  • KevinL
    KevinL ✭✭✭✭

    Great idea Paul, I think I'm getting closer but I'm running into another wall.....  

    I'm using this formula in my Duplicate (Flag symbol) column:   =IF(COUNTIF([Duplicate Helper]:[Duplicate Helper], [Duplicate Helper]@row) > 1, 1, 0

    It's populating a #NO MATCH error instead of a Flag or Blank Flag

    Any suggestions from anyone?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Your part ID... Is it text, number, or a combination of?

  • KevinL
    KevinL ✭✭✭✭

    Part ID is text:   Ex:    PED0KC000Z0

    However the Duplicate "flag" column is referencing the Duplicate Helper column.  Is there an issue with a column referencing a column that is formula generated?

    • Duplicate "flag" column formula:  =IF(COUNTIF([Duplicate Helper]:[Duplicate Helper], [Duplicate Helper]1) > 1, 1, 0)
    • Duplicate Helper formula:  =[Part ID]@row + " " + MATCH(true, ADD@row:[COST CHANGE]@row, 0)

    Duplicate Helper populated with this Example:  PED0KC000Z0 1

    Duplicate populated with this Example:  #NO MATCH

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Hmm... Referencing data that is formula generated shouldn't be an issue. Let me fiddle around with it a bit, and I'll get back to you.

  • KevinL
    KevinL ✭✭✭✭

    Thanks for all your efforts Paul, just following up to see if you had any luck?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Sorry for the delay. I have tested and tested and keep coming up with no errors going step by step with the setup we previously established. Everything is working fine. Are you able to post some example screenshots with the error?

  • KevinL
    KevinL ✭✭✭✭

    Hmm, if you're not getting errors, I'm guessing I just incorrectly tweaked something. 

    Formula Screenshots.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    All of your formulas look to be in order. The only time I got the #NO MATCH error was when I dropped the formula in the Helper column to a row that did not have any boxes checked, and even then the error was being thrown in the Helper column.

     

    I was unable to get data in the helper column but the error in the flag column.

     

    Try updating all of your row references to @row.

    Comm.PNG

  • KevinL
    KevinL ✭✭✭✭

    Took a new approach with the advise of my Smartsheet Customer Success Manager and I think we have it working with this formula

    =[Part ID]@row + JOIN(ADD1:[COST CHANGE]1)

     

    Thanks again for your efforts!!!

    Join Helper.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

     

    Glad you were able to find a working solution. I'm still not sure why it was working for me but not for you. Hmm...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!