Union, intersection, join


S

swchee

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 sam
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 valu
and second column indicate the source file. Since A and B found in bot
S1 and S2, so just leave blank.

Thank you very much for your kind help. Thank
 
Ad

Advertisements

G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top