Tally Names from a column excluding duplicates

KJL

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

Genevieve P.

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

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.

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

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.

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

FANTASTIC, Thanks very much Genevieve :D