extract unique and common elements from cells

[loup29]

Hi,
I'd like to be able to compare two (or more) cells and extract unique and common characters between the cells.

For example
from

cell A1: XYZ
cell A2: XYR

What I'd like is three different outputs:

one with what is unique to A1 - in this case Z
one with what is unique to A2 - in this case R
and one with what is common to the cells - in this case XY

thanks to the site I have been able to put commas between the characters making XYZ look like X,Y,Z which may make the problem easier.

Hopefully someone can help - thanks a lot

[kev_]

welcome to the forum
VBA solution
this works provided there are no duplicates as in your example

Please Login or Register  to view this content.

[FlameRetired]

This is untested. It works with your example. In B1 array enter this formula and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

In C1 this non-array formula and fill down.

Please Login or Register  to view this content.

For the comma separated strings array enter this in F1 and fill down.

Please Login or Register  to view this content.

then in G1 this non-array formula and filled down.

Please Login or Register  to view this content.

	A	B	C	D	E	F	G
1	XYZ	Z	XY		X, Y, Z	Z	XY
2	XYR	R	XY		X, Y, R	R	XY
3

[loup29]

Hello Kev - thank you
I screwed up - have said the original data is in A1 and B1 - if I do a simple replacement of all A2 cells and change to B1 will that work?
Also I'm very new to this - I've added modules as FUNCTIONS before - will this run the same way if so how do I run this?
Thank you so very much

[loup29]

Hi I screwed up - meant A1 and B1 as original cells - couldn't get it to work by substituting

[FlameRetired]

Try array entering this in C1 and filling down.

Please Login or Register  to view this content.

	A	B	C
1	XYZ	XYR	Z
2			R