Reconciling Data Ranges
 

Dear all,

In an Excel spreadsheet, I have 2 sets of data composed of alpha
strings stored in Range1 and Range2. Initially the data in Range2 is
identical to Range1 but over time items are deleted from Range2.

Periodically, I want to update Range1 to reflect the changes that have
occured in Range2, in other words if an data item in Range1 does not
exist in Range2 I want to delete it from Range1.

Each range contains1000 items. Using VBA what is the fastest,smartest
way to complete the task? - my initial shot at cranking the handle
takes for ever!!

In reality the ranges are on different sheets but let's say that they
are in columns A and C and consist of:

A C
T501 T502
T502 T501
T503 .
.. .
.. .
.. .
.. .
.. .
T1000 T1000

Notice in this simple example that T503 no longer exists in the column
C range and that the data order has also changed. Without sorting any
of the data I want to determine that T503 should now be deleted from
the A range.

My ideal solution would be to read in each item from Range1 in sequence
and check whether it exists in Range2 but it must be quick!

Any Ideas please?

Reconciling Data Ranges
 

Alan,

Here's a version that looks across sheets...

Sub MatchUp()
Dim Range1 As Range
Dim Range2 As Range
Dim myR As Long

With Worksheets("Sheet1")
Set Range1 = .Range(.Range("A1"), .Range("A65536").End(xlUp))
End With

With Worksheets("Sheet2")
Set Range2 = .Range(.Range("A1"), .Range("A65536").End(xlUp))
End With

For myR = Range1.Cells.Count To 1 Step -1
If IsError(Application.Match(Range1.Cells(myR), Range2, False)) Then
Range1.Cells(myR).Delete
End If
Next myR

End Sub



--
HTH,
Bernie
MS Excel MVP


"Alan" wrote in message
ups.com...
Dear all,

In an Excel spreadsheet, I have 2 sets of data composed of alpha
strings stored in Range1 and Range2. Initially the data in Range2 is
identical to Range1 but over time items are deleted from Range2.

Periodically, I want to update Range1 to reflect the changes that have
occured in Range2, in other words if an data item in Range1 does not
exist in Range2 I want to delete it from Range1.

Each range contains1000 items. Using VBA what is the fastest,smartest
way to complete the task? - my initial shot at cranking the handle
takes for ever!!

In reality the ranges are on different sheets but let's say that they
are in columns A and C and consist of:

A C
T501 T502
T502 T501
T503 .
. .
. .
. .
. .
. .
T1000 T1000

Notice in this simple example that T503 no longer exists in the column
C range and that the data order has also changed. Without sorting any
of the data I want to determine that T503 should now be deleted from
the A range.

My ideal solution would be to read in each item from Range1 in sequence
and check whether it exists in Range2 but it must be quick!

Any Ideas please?

Reconciling Data Ranges
 

Thanks Bernie, your suggestion was very helpful.

Bernie Deitrick wrote:
Alan,

Here's a version that looks across sheets...

Sub MatchUp()
Dim Range1 As Range
Dim Range2 As Range
Dim myR As Long

With Worksheets("Sheet1")
Set Range1 = .Range(.Range("A1"), .Range("A65536").End(xlUp))
End With

With Worksheets("Sheet2")
Set Range2 = .Range(.Range("A1"), .Range("A65536").End(xlUp))
End With

For myR = Range1.Cells.Count To 1 Step -1
If IsError(Application.Match(Range1.Cells(myR), Range2, False)) Then
Range1.Cells(myR).Delete
End If
Next myR

End Sub



--
HTH,
Bernie
MS Excel MVP


"Alan" wrote in message
ups.com...
Dear all,

In an Excel spreadsheet, I have 2 sets of data composed of alpha
strings stored in Range1 and Range2. Initially the data in Range2 is
identical to Range1 but over time items are deleted from Range2.

Periodically, I want to update Range1 to reflect the changes that have
occured in Range2, in other words if an data item in Range1 does not
exist in Range2 I want to delete it from Range1.

Each range contains1000 items. Using VBA what is the fastest,smartest
way to complete the task? - my initial shot at cranking the handle
takes for ever!!

In reality the ranges are on different sheets but let's say that they
are in columns A and C and consist of:

A C
T501 T502
T502 T501
T503 .
. .
. .
. .
. .
. .
T1000 T1000

Notice in this simple example that T503 no longer exists in the column
C range and that the data order has also changed. Without sorting any
of the data I want to determine that T503 should now be deleted from
the A range.

My ideal solution would be to read in each item from Range1 in sequence
and check whether it exists in Range2 but it must be quick!

Any Ideas please?