I have two cells and I need to get the combined data. Eg: Cell A1 has data as AS,CC,DP,SS (count 4) and Cell B1 has CC,SS,EI,IT (count 4); total 8 and I want my output as AS,CC,DP,SS,EI and IT (count 6 with two overlapping terms appearing once)...how to get this in excel? (no vba)

CodePudding user response：

You can achieve this with a combination of functions. First, you can use the CONCATENATE function to join the two cell values together. Then, you can use the SUBSTITUTE function to replace any duplicate values with a blank string. Then, you can use the TRIM function to remove any additional spaces. Finally, you can use the FILTER function to filter out any blank values.

=FILTER(TRIM(SUBSTITUTE(CONCATENATE(A1,B1),","," ")),TRIM(SUBSTITUTE(CONCATENATE(A1,B1),","," "))<>"")

This formula should give you the result you are looking for.

CodePudding user response：

Using TEXTPLIT and TEXTJOIN:

=TEXTJOIN(",",,SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(",",,A1:B1),,","))))