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

 

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

 

[Fluff]

option 1 (UNIQUE(FILTERXML("<root><s>"&SUBSTITUTE(A:A,"; ","</s><s>")&"</s></root>","//s")) seems to work

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

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

How many rows of data do you have?