Unique values from column where cells have multiple values with a delimiter

[ausswe]

Hi,
I've been sourcing around to figure out how to use a formula to get a unique list with values in a column where some cells have multiple values in the same cell with "; " as a delimiter between them. Would anyone have any ideas?

Example:

A (unique values from B)	B
Sarskild boendeform	Sarskild boendeform; Trygghetslarm; Boendestod; Matdistribution
Trygghetslarm	Matdistribution
Boendestod	Sarskild boendeform; Trygghetslarm; Matdistribution
Matdistribution	Trygghetslarm
	Boendestod; Matdistribution
	Sarskild boendeform

 

[ReeceBmp]

It might appear to work, but it doesn't. It will only return the 1st word from each cell.

How many rows of data do you have?

Sorry I only tried with one row of data

 

[ausswe]

Hi @Fluff, the spreadsheet contains approx. 5600 rows.

 

[Peter_SSs]

the spreadsheet contains approx. 5600 rows.

Perhaps then you could consider a user-defined function like this? (It will not work on your MacOS platform though, only Windows)

Function UniqueList(rng As Range, Optional Delim As String = ";") As Variant
  Dim d As Object
  Dim a As Variant, itm As Variant
  Dim i As Long
 
  a = rng.Value
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  For i = 1 To UBound(a)
    For Each itm In Split(a(i, 1), Delim)
      d(LTrim(itm)) = 1
    Next itm
  Next i
  UniqueList = Application.Transpose(d.keys)
End Function

ausswe.xlsm
	A	B
1	A (unique values from B)	B
2	Sarskild boendeform	Sarskild boendeform; Trygghetslarm; Boendestod; Matdistribution
3	Trygghetslarm	Matdistribution
4	Boendestod	Sarskild boendeform; Trygghetslarm; Matdistribution
5	Matdistribution	Trygghetslarm
6		Boendestod; Matdistribution
7		Sarskild boendeform
Sheet3

Cell Formulas
Range	Formula
A2:A5	A2	=UniqueList(B2:B7)
Dynamic array formulas.

Note that the function has an option to use a different delimiter so it could be used like this as well

ausswe.xlsm
	A	B
1	A (unique values from B)	B
2	Sarskild boendeform	Sarskild boendeform|Trygghetslarm|Boendestod|Matdistribution
3	Trygghetslarm	Matdistribution
4	Boendestod	Sarskild boendeform|Trygghetslarm|Matdistribution
5	Matdistribution	Trygghetslarm
6		Boendestod|Matdistribution
7		Sarskild boendeform
Sheet4

Cell Formulas
Range	Formula
A2:A5	A2	=UniqueList(B2:B7,"|")
Dynamic array formulas.

 

[ausswe]

Thanks Peter, I did some testing and it seems like the earlier formula from @ReeceBmp is working
(=UNIQUE(FILTERXML("<root><s>"&SUBSTITUTE(A:A,"; ","</s><s>")&"</s></root>","//s")).
The #VALUE! error seems to come from the fact that some of the cells are empty and as such returning the error message.

 

[Peter_SSs]

I did some testing and it seems like the earlier formula from @ReeceBmp is working

@Fluff stated earlier

It might appear to work, but it doesn't. It will only return the 1st word from each cell.

I agree with Fluff, it only returns a unique list of first words from the cells. That is not what you asked for.
Here is a small example with that formula (& it did take a few minutes to calculate!)
For this sample, didn't you want the formula to also return "def" and "ghi" etc?

ausswe.xlsm
	A	B
1	abc; def; ghi; jkl	abc
2	mno; pqr; stu	mno
3	vwx; yza; bcd; efg	vwx
4	abc; jkl	#VALUE!
5
Sheet

Cell Formulas
Range	Formula
B1:B4	B1	=UNIQUE(FILTERXML("<root><s>"&SUBSTITUTE(A:A,"; ","</s><s>")&"</s></root>","//s"))
Dynamic array formulas.

 

[Alex Blakenburg]

This is actually from @JvdV @ VSTACK (and TEXTSPLIT) on dynamic ranges
but seems to work.

=LET(x,B2:B6000,
y,UNIQUE(TRIM(TOCOL(TEXTAFTER("; "&TEXTBEFORE(x&";",";",SEQUENCE(,MAX(LEN(x))),,,NA()),";",-1),3))),
SORT(FILTER(y,y<>"")))

I would probably use Power Query to do it.

 

[Fluff]

Another formula option

=UNIQUE(DROP(REDUCE("",B2:B6000,LAMBDA(x,y,IF(y="",x,VSTACK(x,TEXTSPLIT(y,,"; "))))),1))

 

[Alex Blakenburg]

Another formula option

=UNIQUE(DROP(REDUCE("",B2:B6000,LAMBDA(x,y,IF(y="",x,VSTACK(x,TEXTSPLIT(y,,"; "))))),1))

The same post I got the formula above from indicated that the use of Lamda made the performance quite slow.
I tried your formula and that seems to be the case. The other formula is almost instant.

 

[Fluff]

the use of Lamda made the performance quite slow

I have found much the same, but did not test either formula as I don't have any data.