How to get 100% similar cells as specific scenario in column b

[amir9888]

can u help please for example

a1 contains 22 strings
a2 contains a1 strings + 13 more strings....
a3 contains only 20 strings of a1 and 10 more.
.... i want this.....

In b1 it display the message "contain all string in a2"
in b2 it dispplay "unique"
in b3 it also display "unique" (because it does not contain all strings in any cell)

If there is arrange issue in column A, it should also count.
(I want to get column b data using formula,)

Screenshot 2022-11-04 212046.jpg

another example:

Screenshot 2022-11-04 212558.jpg

[Glenn Kennedy]

Next time, please remember that pictures are pretty much useless. We cannot edit them! Attach an Excel file instead.


=IF(A2="","",IFERROR("String contained in A"&MATCH(1,(--ISNUMBER(SEARCH(A2,$A$2:$A$14))=1)*(ROW($A$2:$A$14)<>ROW()),0),"Unique"))

[Glenn Kennedy]

Ooops.

=IF(A2="","",IFERROR("String contained in A"&ROW($A$2)-1+MATCH(1,(--ISNUMBER(SEARCH(A2,$A$2:$A$14))=1)*(ROW($A$2:$A$14)<>ROW()),0),"Unique"))

[amir9888]

thank you for this ...... it working.... but when i apply them to more then 10k cells. its go very slow... if i go more further like 100k cells. my pc run for 4 hours and excel 365 says (in bottom line) its only 2% calculation done.. then i close it. is there any way to speed up it. ?

i am using i7 (10th gen) cpu