Merging 2 columns

Posted by Tony on August 27, 2001 3:52 AM

I have 2 columns of data, A & B. What I want to end up with is a column with data unique to A, a column with data unique to B & a column of data common to both.
eg.
COLA COLB -----> COLC COLD COLE
1 1 2 7 1
2 3 4 9 3
3 5 5
4 7
5 9

Posted by Aladin Akyurek on August 27, 2001 11:12 AM

I'll assume that the numbers in A and B start in row 2.

In C2 enter: =IF(NOT(ISNUMBER(MATCH(A2,$B$2:$B$6,0))),A2,"") [ copy down as far as needed ]

In D2 enter: =IF(NOT(ISNUMBER(MATCH(B2,$A$2:$A$6,0))),B2,"") [ copy down as far as needed ]

In E2 enter: =IF(LEN(C2)+LEN(D2)=0,A2,"") [ copy down as far as needed ]

Although it's possible to use additional formulas in order to transport non-blank rows to new columns, you can better use AutoFilter for that purpose.

Another solution would be to sollicite for task-specific VBA code.

Aladin