Trying to get a list of distinct values across multiple columns
Hello,
I have a list of providers. Each provider can list up to 4 languages spoken at their office. I would like to get a list of distinct languages for the entire list of providers. Language 3 and Language 4 have different languages further down the rows. I have joined the columns but I still cant get a list of distinct languages. I count 9 languages in total.
Can you please help?
Answers
-
Try:
=COUNT(DISTINCT(COLLECT([language 1]:[language 1], ISTEXT(@cell), [language 2]:[language 2], ISTEXT(@cell), [language 3]:[language 3], ISTEXT(@cell), [language 4]:[language 4], ISTEXT(@cell) )))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Mark, thank you for replying. That will give me a count, but what I am looking for is a list of all the unique languages within all four columns. Any suggestions?
-
Good morning,
Try:
=JOIN(DISTINCT(COLLECT([language 1]:[language 1], ISTEXT(@cell), [language 2]:[language 2], ISTEXT(@cell), [language 3]:[language 3], ISTEXT(@cell), [language 4]:[language 4], ISTEXT(@cell) )), ", ")
Work for you?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thanks for hanging in there with me Mark. I tried the formula above but it is giving me an invalid operation error. Any ideas?
-
Hi Mark,
I was able to get the following to work on a rollup sheet.
=JOIN(DISTINCT({DE Language range}), ", ")
With my range being Language 1 to Language 4
Thanks for all your help!
-
Glad you found a solution. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
Check out the Formula Handbook template!