Comparing Lists
 

Hello

I need to get my hands on two macros which I hope will save me hours of work
every week (assuming I can edit them to suit my needs).

MACRO 1
I need a macro which compares the data in one worksheet with that in another
worksheet (by comparing a column which has a unique identifier) and provides
me with a third worksheet with only the rows from the first worksheet which
weren't in the second worksheet?


MACRO 2
Similar to above, except that this macro would provide me with a third
worksheet containing all the rows from the first worksheet which DID appear
in the second worksheet but had a later "end date".

If one macro could do both, that'd be great - but I'm happy to use two
macros to do the job.

I have limited knowledge of Visual Basic, hence why I wanted to start with
an existing macro that I may be able to edit (hopefully).

Thanks for your help!

Joe.

Comparing Lists
 

Sub comparesheets()

Sh1RowCount = 1
Sh3RowCount = 1
Sh4RowCount = 1
With Sheets("Sheet1")
Do While .Range("A" & Sh1RowCount) < ""
SearchItem = .Range("A" & Sh1RowCount)
With Sheets("Sheet2")
Set c = .Columns("A:A").Find(what:=SearchItem, _
LookIn:=xlValues)
End With
If c Is Nothing Then
.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Sheet3").Rows(Sh3RowCount)
Sh3RowCount = Sh3RowCount + 1
Else
'compare end dates
If .Range("B" & Sh1RowCount) _
c.Offset(0, 1) Then

.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Sheet4").Rows(Sh4RowCount)
Sh4RowCount = Sh4RowCount + 1

End If
End If

Sh1RowCount = Sh1RowCount + 1
Loop
End With
End Sub


"Joe" wrote:

Hello

I need to get my hands on two macros which I hope will save me hours of work
every week (assuming I can edit them to suit my needs).

MACRO 1
I need a macro which compares the data in one worksheet with that in another
worksheet (by comparing a column which has a unique identifier) and provides
me with a third worksheet with only the rows from the first worksheet which
weren't in the second worksheet?


MACRO 2
Similar to above, except that this macro would provide me with a third
worksheet containing all the rows from the first worksheet which DID appear
in the second worksheet but had a later "end date".

If one macro could do both, that'd be great - but I'm happy to use two
macros to do the job.

I have limited knowledge of Visual Basic, hence why I wanted to start with
an existing macro that I may be able to edit (hopefully).

Thanks for your help!

Joe.

Comparing Lists
 

Hi Joel

Thanks very much for your help. It is greatly appreciated! I was able to use
your code with some slight modification and it has done exactly what I
needed.

Thank you!

:)


On 12/1/08 9:14 PM, in article
, "Joel"
wrote:

Sub comparesheets()

Sh1RowCount = 1
Sh3RowCount = 1
Sh4RowCount = 1
With Sheets("Sheet1")
Do While .Range("A" & Sh1RowCount) < ""
SearchItem = .Range("A" & Sh1RowCount)
With Sheets("Sheet2")
Set c = .Columns("A:A").Find(what:=SearchItem, _
LookIn:=xlValues)
End With
If c Is Nothing Then
.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Sheet3").Rows(Sh3RowCount)
Sh3RowCount = Sh3RowCount + 1
Else
'compare end dates
If .Range("B" & Sh1RowCount) _
c.Offset(0, 1) Then

.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Sheet4").Rows(Sh4RowCount)
Sh4RowCount = Sh4RowCount + 1

End If
End If

Sh1RowCount = Sh1RowCount + 1
Loop
End With
End Sub


"Joe" wrote:

Hello

I need to get my hands on two macros which I hope will save me hours of work
every week (assuming I can edit them to suit my needs).

MACRO 1
I need a macro which compares the data in one worksheet with that in another
worksheet (by comparing a column which has a unique identifier) and provides
me with a third worksheet with only the rows from the first worksheet which
weren't in the second worksheet?


MACRO 2
Similar to above, except that this macro would provide me with a third
worksheet containing all the rows from the first worksheet which DID appear
in the second worksheet but had a later "end date".

If one macro could do both, that'd be great - but I'm happy to use two
macros to do the job.

I have limited knowledge of Visual Basic, hence why I wanted to start with
an existing macro that I may be able to edit (hopefully).

Thanks for your help!

Joe.