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]

 

[Fluff]

In your post you said that you had used a variant of sadboy309 formula, which is why I asked you to post the formula.
Please bear in mind our rules especially rule#4

Likewise, please do not answer questions by creating solutions elsewhere and then referencing those solutions via file links. We ask that you answer the question within the thread itself (note: providing links to existing articles on the internet may be acceptable if not breaching other rules).

To that effect, do not simply create a file with a solution, and provide a link to that. Some of the issues with providing links to files uploaded to the internet are: those links often expire, many users cannot download files from file-sharing sites due to network security restrictions, many users are reluctant to download files for personal security reasons and the ability to search the forum effectively is reduced.

 

[FlexMind]

OK, understand.

 

[FlexMind]

My formula for C1

=IF(RIGHT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,LEFT(A1,FIND(",",A1)-1),""),RIGHT(A1,LEN(A1)-FIND(",",A1)-1),""),", ,",",")))=",",LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,LEFT(A1,FIND(",",A1)-1),""),RIGHT(A1,LEN(A1)-FIND(",",A1)-1),""),", ,",",")),LEN(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,LEFT(A1,FIND(",",A1)-1),""),RIGHT(A1,LEN(A1)-FIND(",",A1)-1),""),", ,",",")))-1),TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,LEFT(A1,FIND(",",A1)-1),""),RIGHT(A1,LEN(A1)-FIND(",",A1)-1),""),", ,",",")))

https://yadi.sk/i/Q9zze6mYH9kwOw

 

[KOKOSEK]

My formula for C1

=IF(RIGHT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,LEFT(A1,FIND(",",A1)-1),""),RIGHT(A1,LEN(A1)-FIND(",",A1)-1),""),", ,",",")))=",",LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,LEFT(A1,FIND(",",A1)-1),""),RIGHT(A1,LEN(A1)-FIND(",",A1)-1),""),", ,",",")),LEN(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,LEFT(A1,FIND(",",A1)-1),""),RIGHT(A1,LEN(A1)-FIND(",",A1)-1),""),", ,",",")))-1),TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,LEFT(A1,FIND(",",A1)-1),""),RIGHT(A1,LEN(A1)-FIND(",",A1)-1),""),", ,",",")))

Your formula has 500 characters, Sadboy's 181 characters and both give the same results.