Tally Names from a column excluding duplicates

KJL
KJL ✭✭
edited 03/13/23 in Formulas and Functions

So basically I have a column in a sheet with single or multiple names separated by a comma (as shown). What formula would I use to tally the total of names without duplicates ?


P.S the names are pulled from elsewhere and we cannot use Drop Downs for the Column

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @KJL

    How are you getting the data into this Passenger column? I ask because it looks like potentially there are a number of names that are missing commas between them (like in the first cell).

    The easiest way to do this would be to ensure you only have one name per cell, then you can use COUNT(DISTINCT.

    The other option would be to use a multi-select helper column (next to this column) which pulls in each name as a separate multi-select value. Then you can JOIN all those cells together into one cell, and count how many names appear in that one summary cell.

    We may be able to come up with more ideas if we could learn more about where the source data is coming from and if you're using other formulas right now.

    Cheers,

    Genevieve

  • KJL
    KJL ✭✭

    Hi Genevieve,

    They are pulled in from a PowerAutomate Flow that pulls it from our emails, so we can have it so each name is separated by a comma to assist the formula.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @KJL

    If you have all your names separated by a comma, we can then use the SUBSTITUTE function to translate those names into selections in a multi-select column:

    =SUBSTITUTE(Passengers@row, ",", CHAR(10))

    Then in your top row (or a different multi-select column) you can JOIN all of these names together:

    And use COUNTM to see how many names there are:

    Cheers,

    Genevieve

  • KJL
    KJL ✭✭

    Thats fantastic, Thanks very much Genevieve, could I trouble you with 1 more question ?

    Due to how our data is imported we get the string n/a in the data, I have played around with Replace and Substitute but cant get it right.

    So Basically, Person 1, Person 2, n/a, Person 3, How would I have n/a replaced with a blank space ?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @KJL

    I'm glad to hear it's working for you! 🙂

    You can replace "n/a" with a blank space in the first formula, the Substitute one:

    =SUBSTITUTE(SUBSTITUTE(Passengers@row, "n/a,", ""), ",", CHAR(10))

    Cheers,

    Genevieve

  • KJL
    KJL ✭✭

    FANTASTIC, Thanks very much Genevieve :D

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!