Here's one formulas play to tinker with ..

Sample construct is available at:
http://www.savefile.com/files/9916738
Compare MultiCol n List Common n Unique Items.xls

The play assumes as a startpoint, that we have combined/stacked up* the
source data in cols A to B in a new sheet, with data from row2 to row15
(say):
*via manual copy pasting from the various sheets,
with the sheetnames filled down in col B (a one-time job)

02-1234-12 Sheet1
04-1234-12 Sheet1
05-1234-12 Sheet1
07-1234-12 Sheet2
03-1234-12 Sheet2
02-1234-12 Sheet2
02-1234-12 Sheet3
02-1234-12 Sheet3
06-1234-34 Sheet3
etc

Then ..

In D2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)1,"",ROW()))
In E2: =INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0))

Sheetnames listed in F1:H1 : Sheet1, Sheet2, Sheet3

In F2, array-entered**, F2 copied to H2:
=IF(ISERROR($E2),"",IF(ISNA(MATCH(1,($A$2:$A$15=$E 2)*($B$2:$B$15=F$1),0)),""
,MATCH(1,($A$2:$A$15=$E2)*($B$2:$B$15=F$1),0)))

**press CTRL+SHIFT+ENTER

In I2: =IF(AND(F2<"",G2<"",H2<""),ROW(),"")
In J2: =IF(AND(F2<"",G2="",H2=""),ROW(),"")
In K2: =IF(AND(F2="",G2<"",H2=""),ROW(),"")
In L2: =IF(AND(F2="",G2="",H2<""),ROW(),"")

In M2, M2 copied to P2
=IF(ISERROR(SMALL(I:I,ROW(A1))),"",
INDEX($E:$E,MATCH(SMALL(I:I,ROW(A1)),I:I)))

Labels placed in M1:P1 :

Common to all
Unique to Sheet1
Unique to Sheet2
Unique to Sheet3

Then just select D2:P2, fill down to P15
Cols M to P will return the desired results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kuansheng" wrote in message
ups.com...
Hi! I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.

BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34

AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12