how to extract duplicated data from two seperate worksheets?

[simon steel]

Ok,

Suppose I have 2 worksheets.

Sheet 1 has 1000 lines of data spanning xNumber of columns.

Sheet 2 has 500 lines of data, also spanning the same number of columns.
An unknown number of identical lines of data appear on both sheets.

How can I get to a point where I can build one worksheet that is a composite
of both sheets - such that no identical line of data appears twice on the
final sheet?


Thanks in advance, Simon.

 

[Jim Cone]

Also posted in the public.excel group




"simon steel"
wrote in message
Ok,
Suppose I have 2 worksheets.
Sheet 1 has 1000 lines of data spanning xNumber of columns.
Sheet 2 has 500 lines of data, also spanning the same number of columns.
An unknown number of identical lines of data appear on both sheets.
How can I get to a point where I can build one worksheet that is a composite
of both sheets - such that no identical line of data appears twice on the
final sheet?
Thanks in advance, Simon.

 

[ryguy7272]

I found this macro on this DG a while back:

Sub matchsheets()

Sh1RowCount = 1
Sh3RowCount = 1
With Sheets("Sheet1")
Do While .Range("A" & Sh1RowCount) <> ""
SSN = .Range("A" & Sh1RowCount)
With Sheets("Sheet2")
Set c = .Columns("A:A").Find(what:=SSN, _
LookIn:=xlValues)
If Not c Is Nothing Then
With Sheets("Sheet3")
..Range("A" & Sh3RowCount) = SSN
Sh3RowCount = Sh3RowCount + 1
End With
End If
End With
Sh1RowCount = Sh1RowCount + 1
Loop
End With
End Sub

All matches in Sheet1 and Sheet2, go to Sheet3.

Hope that gives you what you want.

Regards,
Ryan---

 

[simon steel]

My macro experience is limited, so not sure if it does what i want.

I want to end up with one set of data that consists of data from both sheets
on one sheet with no duplicated lines.
Is that what you're saying this will do?

[Can't trial it right now and see the effect 'cos the data is at work and
I'm at home.

simon

 

[ryguy7272]

Try it at work tomorrow. Try it with a backup file or a copy of your file!!
Always test code on backup files; if you obtain undesired results you could
cause irrecoverable damage to your only data source.

Regards,
Ryan--

--
RyGuy

My macro experience is limited, so not sure if it does what i want.

I want to end up with one set of data that consists of data from both sheets
on one sheet with no duplicated lines.
Is that what you're saying this will do?

[Can't trial it right now and see the effect 'cos the data is at work and
I'm at home.

simon