Union, intersection, join

Dear macro experts:

Hi, I have a workbook with 2 worksheets, S1 and S2.
The content in S1 is

A
B
C
100

and content in S2 is

X
Y
Z
A
B

(1) How do merge these 2 worksheets into a new worksheet in the same workbook and display the unique value of S1 and S2, (Union)
expected output, Let say S3 :

100
A
B
C
X
Y
Z

(2) To find the intersection......
expected output, Let say S4 :

A
B


(3) to find the join of two worksheets and indicate the source...
expected output, let say S5:

100 | S1
A
B
C | S1
X | S2
Y |S2
Z | S2


* Question 3 will have 2 columns, the first column indicate the value and second column indicate the source file. Since A and B found in both S1 and S2, so just leave blank.

Thank you very much for your kind help. Thanks

Hi
Maybe something like
Sub Macro1()
Dim rng As Range
Sheets("S1").Select
Range("A:A").Copy
Sheets("S3").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Offset(0, 1) = "S1"
Selection.End(xlDown).Offset(1, 0).Select
Sheets("S2").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Name = "MYR"
For Each CELL In Range("MYR")
Sheets("S1").Select
Set rng = Cells.Find(What:=CELL, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
If Not rng Is Nothing Then
Sheets("S4").Select
ActiveCell = CELL.Value
ActiveCell.Offset(1, 0).Select
Else
Sheets("S3").Select
ActiveCell = CELL.Value
ActiveCell.Offset(0, 1) = "S2"
ActiveCell.Offset(1, 0).Select
End If
Next CELL

End Sub
This copies everthing from s1 to s3 then looks at s2 anf if can not be found
in s1 copies to s3 else to s4

Tina

"swchee" wrote:

>
> Dear macro experts:
>
> Hi, I have a workbook with 2 worksheets, S1 and S2.
> The content in S1 is
>
> A
> B
> C
> 100
>
> and content in S2 is
>
> X
> Y
> Z
> A
> B
>
> (1) How do merge these 2 worksheets into a new worksheet in the same
> workbook and display the unique value of S1 and S2, (Union)
> expected output, Let say S3 :
>
> 100
> A
> B
> C
> X
> Y
> Z
>
> (2) To find the intersection......
> expected output, Let say S4 :
>
> A
> B
>
>
> (3) to find the join of two worksheets and indicate the source...
> expected output, let say S5:
>
> 100 | S1
> A
> B
> C | S1
> X | S2
> Y |S2
> Z | S2
>
>
> * Question 3 will have 2 columns, the first column indicate the value
> and second column indicate the source file. Since A and B found in both
> S1 and S2, so just leave blank.
>
> Thank you very much for your kind help. Thanks
>
>
> --
> swchee
> ------------------------------------------------------------------------
> swchee's Profile: http://www.excelforum.com/member.php...o&userid=24279
> View this thread: http://www.excelforum.com/showthread...hreadid=382671
>
>