Count Duplicates Based on Text Field

Hello!

 

I'm running into a problem that I'm struggling to solve and was hoping that I could get some help here.  I have a numbered list that represents employees.  Sometimes, employees are converted from part-time to full-time and when this happens, they are assigned a new employee ID.  I need to be able to identify cases where there are multiple list items that represent the same individual (based on a text field that shows their name).  In excel, this would be accomplished by using a countif formula.  I'd like to know if there is a simple solution that I'm just not seeing.  I think I could create a new list based on the concatenation of employee ID and name, and then have the name be a list property and then do a count based on that, but I'd like to know if there is a solution that requires less steps (preferably no need for a new list or import action).  Screenshot below; The "Count of items" field is what I'd like to make formulaic.

 

jbrass_0-1616090450090.png

 

 

Thank you!

Josh

Best Answer

Answers

  • @jbrass 

     

    Josh,

     

    Playing Devil's Advocate, what if you have two different individuals with the same name?

  • I can actually use email (which is a text field) instead of name to make it a tighter match.  Understand that this may have holes, but this is just being used to flag potential issues so I'm not too concerned.

     

    Thanks,

    Josh

  • @jbrass 

     

    In that case, see if this. works:

    • Can you make a flat list out of the email addresses?
    • if so, then create a constant line item with a 1 as the value
    • then create a module based on the flat email list and do a sum off the constant line item.  If it is >1,  then you have duplicates.

     

    Does that work for you?

     

    Rob

  • Thanks Rob.  That makes sense.  I was trying to see if there was a way to do it without having to run an import process (to create that list), but it sounds like this is the only way.

     

    Thanks again,

    Josh

  • Thanks Rob. That’s the approach I’ll take. Was trying to see if I could skip the import process (that we’d use to populate that email list), but it sounds like that’s not possible.

     

    Appreciate your help on this!

    Josh

  • @kevin.cho  Brilliant. I think this can be turned into Best Practice Article.

  • Thanks @Misbah 🙂 Will see how to do that (might want to add a bit more to it too!)

  • @kevin.cho 

     

    Sure, go for it.  You can extend the capabilities of Rank function instead of just focusing on this particular use case. Looking at the hidden capabilities of Rank Function I started liking it even more. 

     

    Some of the limitations of Isfirstoccurrence (IFO) can easily be tackled with Rank function. For Example unlike IFO you can easily get the Rank 1s (Unique or in other words first occurred values) on any line item.

  • Thank you @kevin.cho , this works perfectly.

    Thank you @rob_marshall  and @Misbah  for your help and commentary as well.

     

     

  • @kevin.cho 

    thanks a lot.

    Very simple, you save my life 😀