I was trying to help solving this question.
My proposal was based on the idea to get unique identifiers, using the Unique() worksheet function.
So, in cell "B1" I put the value "1,2,3,4,5".
In cell "B2" I put the value "2,3,4,5,6".
Using the formula =UNIQUE(TEXTSPLIT(TEXTJOIN(",";TRUE;B1;B2);",")), I was hoping to get a list of unique identifiers, but this did not work out.
I have written this as an answer (formulating an approach, and hoping somebody would react in a comment), but as another answer has already been accepted, I fear the question in my answer will not be regarded anymore, and I'm really curious: why is my formula not working? (I did use formula auditing and it seems that it's really the Unique() worksheet function, causing the problem. I have also tried sorting the entries first, but as sorting seems not to work, also that seems not to be a solution)
Ok, I have finally found a solution: apparently the whole thing needs a conversion into columns in order to make it work:
=UNIQUE(SORT(TEXTSPLIT(TEXTJOIN(",";TRUE;B1:B2);",")))
Result : "1, 2, 3, 4, 5, 2, 3, 4, 5, 6" => NOK
=TOROW(UNIQUE(SORT(TOCOL(TEXTSPLIT(TEXTJOIN(",";TRUE;B1:B2);",")))))
Result : "1, 2, 3, 4, 5, 6" => OK
Is this a bug? Are there any plans to solve this issue?
Try this:
=UNIQUE(SORT(TEXTSPLIT(TEXTJOIN(",";TRUE;B1:B2);","));TRUE)
Like said in the comments, you can even drop the SORT():
=UNIQUE(TEXTSPLIT(TEXTJOIN(",";TRUE;B1:B2);",");TRUE)
Check the UNIQUE arguments list to see why that TRUE matters.
EDIT: if you want to actually use your outputted numbers as actual numbers, you have to add in VALUE() as such:
=UNIQUE(VALUE(TEXTSPLIT(TEXTJOIN(",";TRUE;B1:B2);","));TRUE)
Otherwise the output will give numbers formatted as TEXT.
Alternatively, you can use TRANSPOSE instead of TOCOL and TOROW, but that only works if you use only one row output (so in this case it would work), can even double transpose to return a row again.
=TRANSPOSE(UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(",";TRUE;A10;A11);","))))