Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a row from one worksheet if it is present in another
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a row from one worksheet if it is present in another
Do you have any columns that contain unique values?
HTH, Bernie MS Excel MVP wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a row from one worksheet if it is present in another
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a row from one worksheet if it is present in another
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). |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a row from one worksheet if it is present in another
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a row from one worksheet if it is present in another
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 wrote in message oups.com... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to present a form instead of the worksheet | Excel Discussion (Misc queries) | |||
selecting a worksheet | Excel Worksheet Functions | |||
selecting cell range in other worksheet without switching to worksheet | Excel Programming | |||
WorkSheet Present | Excel Programming | |||
Selecting Last Worksheet | Excel Programming |