Trying to get a list of distinct values across multiple columns

Sherry Hamilton · 02/27/21

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?

Mark Cronk · 03/01/21

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

Sherry Hamilton · 03/01/21

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?

Mark Cronk · 03/01/21

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

Sherry Hamilton · 03/05/21

Thanks for hanging in there with me Mark. I tried the formula above but it is giving me an invalid operation error. Any ideas?

Sherry Hamilton · 03/05/21

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!

Mark Cronk · 03/05/21

Glad you found a solution. Thank you for contributing to the Community.

Mark

Trying to get a list of distinct values across multiple columns

Answers

Help Article Resources

Categories