Forum Discussion

WMonty223's avatar
WMonty223
Copper Contributor
Oct 29, 2021

Text Join - Return Unique Text Only

Hi, I am trying to use text join with Unique function and it does not seem to be working for text. I have successfully used it for values in the same spreadsheet but can't seem to eliminate the dupli...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 29, 2021

WMonty223 

UNIQUE works with text fields in 365 but perhaps not in the manner you require.

Records are only eliminated only if they duplicate another record in its entirety.  

I am not sure what you are planning to do with TEXTJOIN.

  • journey4life's avatar
    journey4life
    Copper Contributor
    Oct 18, 2023

    PeterBartholomew1 

    In the example you share below for Dogs and Cats, I am trying to have an outcome of what pet types and what colors they are available in - both unique values based on the data in the 'original table'
    Dog   Black, Brown

    Cat     Ginger, Black, White

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Oct 19, 2023

      journey4life 

      This is the file.  I have made life more complicated by including a version that returns the colours as an array of arrays, as opposed to an array of comma-separated lists. 

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Oct 19, 2023

      journey4life 

      I would normally recommend a new question but, since the test data already exists, I will try to answer here.  What you require is an array of comma-separated lists which is supported in Excel 365, if a little complicated.  In the example, the first task of creating an array of distinct species is straightforward.

      = LET(
    species, UNIQUE(Pet),
    species
  )

      To continue the formula to colours can be achieved by defining a Lambda function that returns the list of colours for a single species. 

      = LAMBDA(s,
    LET(
      distinctColour, UNIQUE(FILTER(Colour, Pet = s)),
      TEXTJOIN(", ", , distinctColour)
    )
  )

      To test the function

      = SpeciesColoursλ("Dog")
returns
"Black, Brown"

       

      This allows the initial formula to be extended using MAP and the new Lambda function

      = LET(
    species, UNIQUE(Pet),
    colours, MAP(species, SpeciesColoursλ),
    HSTACK(species, colours)
  )

       

      Things get harder if the lists are expected to be returned as row arrays giving a ragged 2D array!

Resources