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

 

[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.

 

[Skybluekid]

What version of Excel are you using?

 

[ausswe]

Microsoft 365 Apps for enterprise, subscription. Build 2208.

 

[Fluff]

Microsoft 365 Apps for enterprise, subscription. Build 2208.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ReeceBmp]

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

enter the following formula:

=UNIQUE(FILTERXML("<root><s>"&SUBSTITUTE(A:A,"; ","</s><s>")&"</s></root>","//s")

 

[Fluff]

Have you actually tried that formula? If so how long did it take?

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
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
8
Main

Cell Formulas
Range	Formula
A2:A5	A2	=UNIQUE(TEXTSPLIT(TEXTJOIN("; ",,B2:B100),,"; "))
Dynamic array formulas.

 

[Sanjeev1976]

Try using the below :

Book2
	A	B
1	Sarskild boendeform	Sarskild boendeform; Trygghetslarm; Boendestod; Matdistribution
2	Trygghetslarm	Matdistribution
3	Boendestod	Sarskild boendeform; Trygghetslarm; Matdistribution
4	Matdistribution	Trygghetslarm
5		Boendestod; Matdistribution
6		Sarskild boendeform
Sheet1

Cell Formulas
Range	Formula
A1:A4	A1	=UNIQUE(TRIM(TEXTSPLIT(TEXTJOIN(";",,B1:B6),,";")))
Dynamic array formulas.

 

[ausswe]

Thanks all, so option 1 (UNIQUE(FILTERXML("<root><s>"&SUBSTITUTE(A:A,"; ","</s><s>")&"</s></root>","//s")) seems to work, took around 1 minute to calculate with my data and it returned a #VALUE in the middle of the list of values it created, I tried to set the format to General but that didn't help the issue.

Option 2 (UNIQUE(TRIM(TEXTSPLIT(TEXTJOIN(";",,B1:B6),,";")))) returns an #CALC! error saying the function returned text that was longer than the maximum supported length.