Tally Names from a column excluding duplicates
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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 ?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
FANTASTIC, Thanks very much Genevieve :D
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!