Find unique with formula (no VBa)

[nemmi69]

Hi all,
This is taken from a previously asked question but that allowed VBa. In this case I am trying to do it via formula and not use multiple columns to get the result. IE only the cell in "C" has a formula. This should keep all guru's busy for awhile I sure as heck have not got far.


[TABLE="class: cms_table_grid, width: 600"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]chocolate, strawberry[/TD]
[TD]banana, chocolate, strawberry[/TD]
[TD]banana[/TD]
[/TR]
[TR]
[TD]vanilla, blueberry[/TD]
[TD]licorice, raspberry, vanilla, fudge, blueberry[/TD]
[TD]licorice, raspberry, fudge[/TD]
[/TR]
</tbody>[/TABLE]

 

[sadboy309]

You want replace so you can use SUBSTITUTE
[C1]=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,LEFT(A1,FIND("$",SUBSTITUTE(A1,",","$",1))-1),"",1),RIGHT(A1,LEN(A1)-FIND("$",SUBSTITUTE(A1,",","$",1))),""),","," "))," ",", ")

 

[nemmi69]

Thanks Sadboy309 but that just duplicates the "B" cell. It does not return the unique item(s).

 

[KOKOSEK]

Thanks Sadboy309 bu that just gives me #value !

drag it down to C2 and C3 and will be fine

 

[nemmi69]

Sorry, post got edited. It just duplicates B

 

[nemmi69]

Suspect it will need concatenate or something (cannot use TEXTJOIN [thanks Microsoft for not sending out updates to 2016])

 

[KOKOSEK]

Sorry, post got edited. It just duplicates B

Put this into C2:

=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,LEFT(A2,FIND("$",SUBSTITUTE(A2,",","$",1))-1),"",1),RIGHT(A2,LEN(A2)-FIND("$",SUBSTITUTE(A2,",","$",1))),""),","," "))," ",", ")

Try again. It works fine.

 

[FlexMind]

Variant from sadboy309 in the Excel file:
https://yadi.sk/i/bIb1ffSXkiQuqQ

 

[Fluff]

@FlexMind
Can you please post the formula to the board, so that members don't have to download the file.
Thanks

 

[FlexMind]

@Fluff
I have used the formula from post #2 , and uploaded file for @nemmi69, which says that the formula does not work.

You want replace so you can use SUBSTITUTE
[C1]=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,LEFT(A1,FIND("$",SUBSTITUTE(A1,",","$",1))-1),"",1),RIGHT(A1,LEN(A1)-FIND("$",SUBSTITUTE(A1,",","$",1))),""),","," "))," ",", ")