Selecting a row from one worksheet if it is present in another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

i_eat_pies@hotmail.com - 13 Dec 2006 13:55 GMT

I have two sets of data where dataset 2 is a subset of dataset 1. I
would like to copy all rows in dataset1 where the same identifier is in
dataset2 to a new worksheet.

Dataset 1 has 12500 entries and dataset 2 has approx 2500 entries.

If the copy procedure is very intensive would deleting the rows in
dataset1 where the identifier cannot be found in dataset2 be quicker?

I have had a go this morning but it takes too long to process. What
could i change to improve performance - alternatively is there simply a
better way.

Any suggestions would be gratefully received as this is my first go at
VB.

Thanks.
----------------------------------------------
Sub Main()
Dim cnt As Integer, target As String
cnt = 0
Do While Worksheets(2).Cells(cnt + 1, 1) <> ""
target = Worksheets(2).Cells(cnt + 1, 1)
Call MarkGoodRows(target)
cnt = cnt + 1
Loop

Call ClearUp
End Sub

Sub MarkGoodRows(target)
Dim cnt As Integer
cnt1 = 1
Do While Worksheets(1).Cells(cnt1, 1) <> ""
If Worksheets(1).Cells(cnt1, 1) = target Then
Sheets(1).Cells(cnt1, 8) = "Good Point"
Exit Sub
End If
cnt1 = cnt1 + 1
Loop
End Sub

Sub ClearUp()
Dim cnt As Integer
cnt = 1
Do While Sheets(1).Columns(1).Row(cnt) <> ""
If Sheets(1).Columns(8).Row(cnt) = "Good Point" Then
Sheets(1).Columns(1).Row(cnt).Copy_destination
(Sheets(1).Columns(1).Row(cnt))
Sheets(1).Columns(2).Row(cnt).Copy_destination
(Sheets(1).Columns(2).Row(cnt))
Sheets(1).Columns(3).Row(cnt).Copy_destination
(Sheets(1).Columns(3).Row(cnt))
Sheets(1).Columns(4).Row(cnt).Copy_destination
(Sheets(1).Columns(4).Row(cnt))
Sheets(1).Columns(5).Row(cnt).Copy_destination
(Sheets(1).Columns(5).Row(cnt))
Sheets(1).Columns(6).Row(cnt).Copy_destination
(Sheets(1).Columns(6).Row(cnt))

End If
cnt = cnt + 1
Loop
End Sub

Reply to this Message

Bernie Deitrick - 13 Dec 2006 14:36 GMT

Do you have any columns that contain unique values?

HTH,
Bernie
MS Excel MVP

>I have two sets of data where dataset 2 is a subset of dataset 1. I
> would like to copy all rows in dataset1 where the same identifier is in
[quoted text clipped - 60 lines]
> Loop
> End Sub

Reply to this Message

i_eat_pies@hotmail.com - 13 Dec 2006 14:54 GMT

Nik, I am looking at your suggestion now and it does seem far easier.
Only thing i am now struggling with is getting it to copy a range of
cells at once.

Bernie, the first column in each sheet holds unique values (these are
unique to each list/sheet but some of those in dataset1 will be in
dataset2).

Reply to this Message

Nik - 13 Dec 2006 15:04 GMT

> Nik, I am looking at your suggestion now and it does seem far easier.
> Only thing i am now struggling with is getting it to copy a range of
> cells at once.

You can use MyRange.copy, where Myrange is a range object.

If, for example, you were using a loop, you could do the following:

for MyRow = 1 to dataset1.usedrange.rows.count

if (find a match)
range(dataset1.cells(myrow,1) , dataset1.cells(myrow,8)).copy
end if

next

Reply to this Message

Bernie Deitrick - 13 Dec 2006 15:34 GMT

IF those unique values in column A match between the sheets, you could use:

=NOT(ISERROR(MATCH(A2,Sheet2!A:A,FALSE)))

(assuming you have a header row)

and copy down to match your dataset. Then sort based on that column, and copy the rows where the
value is TRUE. This can be macro-ized if you need... For example, to put it into Column I of
Sheet1, which will move the rows with key values matched to the top of the sheet for easy copying -
which can also be part of a macro:

Sub TryNow()
Dim myCopy As Range
With Worksheets("Sheet1")
.Range("I2", .Range("H65536").End(xlUp)(1, 2)).Formula _
= "=NOT(ISERROR(MATCH(A2,Sheet2!A:A,FALSE)))"
.Range("I2").CurrentRegion.Sort Key1:=.Range("I2"), _
Order1:=xlDescending, Header:=xlYes
Set myCopy = .Columns("I:I").Find(What:="False", After:=.Range("I1"), _
LookIn:=xlValues, LookAt:=xlWhole)
Set myCopy = .Range("A1", myCopy(0, 0))
myCopy.Copy
End With
End Sub

HTH,
Bernie
MS Excel MVP

> Nik, I am looking at your suggestion now and it does seem far easier.
> Only thing i am now struggling with is getting it to copy a range of
[quoted text clipped - 3 lines]
> unique to each list/sheet but some of those in dataset1 will be in
> dataset2).

Reply to this Message

Nik - 13 Dec 2006 14:38 GMT

> I have two sets of data where dataset 2 is a subset of dataset 1. I
> would like to copy all rows in dataset1 where the same identifier is in
[quoted text clipped - 8 lines]
> could i change to improve performance - alternatively is there simply a
> better way.

One quick and easy way to improve data manipulation speeds in the
worksheet is to set application.screenupdating=false at the start of the
macro and back to =true at the end.

This stops Excel re-drawing the screen every time it moves a row of data
and can have dramatic effect on run times.

That said, I think there is probably a better way to process your data -
I would try

Loop through each row in dataset 1
if countif(appropriatecolumn in sheetsheet2,identifier) >0 then
copy whole row to sheet 3
end if
next

HTH

Nik

Reply to this Message