Trying to get a list of distinct values across multiple columns

02/27/21
Answered - Pending Review

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

  • Mark CronkMark Cronk ✭✭✭✭✭

    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?

  • Mark CronkMark Cronk ✭✭✭✭✭

    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!

  • Mark CronkMark Cronk ✭✭✭✭✭

    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.

Sign In or Register to comment.