searching data thru 20000 records
 

Hi,

I have 2 sheets with several fields.
on sheet1, i have new data and on sheet2 i have old data.

i would like to update 2 fields of sheet1 with data from sheet2.

for that i want to compare 3 fields, let's say C, D, E.

if C, D, E are equal on sheet1 and sheet2, so i want to copy data from
fields A and B (from sheet2) to fields A and B of sheet1.

for now i use selection.autofilter .....
on the 3 fields to compare, but with my 20,000 records, it's really slow
(on 3.1 Ghz CPU and 1 Ghz ram).

So i would like to know if exists another way how to do it and for sure
faster ?
thanka a lot for help.

maileen

searching data thru 20000 records
 

Hi, try this code, it finds the first match on sheet 2 and copies columns A
& B to sheet 1
change references to suit your needs.

'-----------------------------------------------------------------
Sub Update()
Application.ScreenUpdating = False

Dim wsNew As Worksheet, wsOld As Worksheet
Dim xLastRowNew As Long, xLastRowOld As Long
Dim xRN As Long, xRO As Long, XUpdates As Long

Set wsNew = Sheets(1)
Set wsOld = Sheets(2)

' use column 3 ("C") to get last row of data on each sheet
xLastRowNew = wsNew.Cells(Rows.Count, 3).End(xlUp).Row
xLastRowOld = wsOld.Cells(Rows.Count, 3).End(xlUp).Row

' scan new data - assumes first data row is row 1
XUpdates = 0
With wsNew
For xRN = 1 To xLastRowNew
For xRO = 1 To xLastRowOld
' check for match
If .Cells(xRN, 3) = wsOld.Cells(xRO, 3) And _
.Cells(xRN, 4) = wsOld.Cells(xRO, 4) And _
.Cells(xRN, 5) = wsOld.Cells(xRO, 5) Then
' copy values
.Cells(xRN, 1) = wsOld.Cells(xRO, 1)
.Cells(xRN, 2) = wsOld.Cells(xRO, 2)
XUpdates = XUpdates + 1
Exit For
End If
Next xRO
Next xRN
End With
Application.ScreenUpdating = True

MsgBox "Completed - Updated: " & CStr(XUpdates)

End Sub


--
Cheers
Nigel



"Maileen" wrote in message
...
Hi,

I have 2 sheets with several fields.
on sheet1, i have new data and on sheet2 i have old data.

i would like to update 2 fields of sheet1 with data from sheet2.

for that i want to compare 3 fields, let's say C, D, E.

if C, D, E are equal on sheet1 and sheet2, so i want to copy data from
fields A and B (from sheet2) to fields A and B of sheet1.

for now i use selection.autofilter .....
on the 3 fields to compare, but with my 20,000 records, it's really slow
(on 3.1 Ghz CPU and 1 Ghz ram).

So i would like to know if exists another way how to do it and for sure
faster ?
thanka a lot for help.

maileen

searching data thru 20000 records
 

Hi Maileen,

Please do not post to more than one newsgroup in more than one posting.

See my answer in other newsgroup

--
Kind regards,

Niek Otten

"Maileen" wrote in message
...
Hi,

I have 2 sheets with several fields.
on sheet1, i have new data and on sheet2 i have old data.

i would like to update 2 fields of sheet1 with data from sheet2.

for that i want to compare 3 fields, let's say C, D, E.

if C, D, E are equal on sheet1 and sheet2, so i want to copy data from
fields A and B (from sheet2) to fields A and B of sheet1.

for now i use selection.autofilter .....
on the 3 fields to compare, but with my 20,000 records, it's really slow
(on 3.1 Ghz CPU and 1 Ghz ram).

So i would like to know if exists another way how to do it and for sure
faster ?
thanka a lot for help.

maileen

searching data thru 20000 records
 

Nigel,
did you test this with 20.000 records?
I doubt it.. this is SLOW code. It will loop 20000 * 20000 times:
.... in my book that is 400'000'000 loops.


Much better to create indexes first and then compare the indexes.
example see http://groups.google.com
search: excel compare two sets of data author:keepitcool
(post Aug 9, 2005)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Nigel wrote in

Hi, try this code, it finds the first match on sheet 2 and copies
columns A & B to sheet 1
change references to suit your needs.

'-----------------------------------------------------------------
Sub Update()
Application.ScreenUpdating = False

Dim wsNew As Worksheet, wsOld As Worksheet
Dim xLastRowNew As Long, xLastRowOld As Long
Dim xRN As Long, xRO As Long, XUpdates As Long

Set wsNew = Sheets(1)
Set wsOld = Sheets(2)

' use column 3 ("C") to get last row of data on each sheet
xLastRowNew = wsNew.Cells(Rows.Count, 3).End(xlUp).Row
xLastRowOld = wsOld.Cells(Rows.Count, 3).End(xlUp).Row

' scan new data - assumes first data row is row 1
XUpdates = 0
With wsNew
For xRN = 1 To xLastRowNew
For xRO = 1 To xLastRowOld
' check for match
If .Cells(xRN, 3) = wsOld.Cells(xRO, 3) And _
.Cells(xRN, 4) = wsOld.Cells(xRO, 4) And _
.Cells(xRN, 5) = wsOld.Cells(xRO, 5) Then
' copy values
.Cells(xRN, 1) = wsOld.Cells(xRO, 1)
.Cells(xRN, 2) = wsOld.Cells(xRO, 2)
XUpdates = XUpdates + 1
Exit For
End If
Next xRO
Next xRN
End With
Application.ScreenUpdating = True

MsgBox "Completed - Updated: " & CStr(XUpdates)

End Sub

searching data thru 20000 records
 

Hi keepITCool

No I did not test with 20,000 rows in both the new and old data, and yes I
know the consequences of a 20k x 20k loop! You probably noted that the
inner loop exits after the search for new data has been completed cutting
down the total searches, I also assumed that the OP has a limited list in
the new dataset and refers to the 20k list in the lookup table. If not then
alternative strategies will be required.


--
Cheers
Nigel



"keepITcool" wrote in message
.com...

Nigel,
did you test this with 20.000 records?
I doubt it.. this is SLOW code. It will loop 20000 * 20000 times:
... in my book that is 400'000'000 loops.


Much better to create indexes first and then compare the indexes.
example see http://groups.google.com
search: excel compare two sets of data author:keepitcool
(post Aug 9, 2005)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Nigel wrote in

Hi, try this code, it finds the first match on sheet 2 and copies
columns A & B to sheet 1
change references to suit your needs.

'-----------------------------------------------------------------
Sub Update()
Application.ScreenUpdating = False

Dim wsNew As Worksheet, wsOld As Worksheet
Dim xLastRowNew As Long, xLastRowOld As Long
Dim xRN As Long, xRO As Long, XUpdates As Long

Set wsNew = Sheets(1)
Set wsOld = Sheets(2)

' use column 3 ("C") to get last row of data on each sheet
xLastRowNew = wsNew.Cells(Rows.Count, 3).End(xlUp).Row
xLastRowOld = wsOld.Cells(Rows.Count, 3).End(xlUp).Row

' scan new data - assumes first data row is row 1
XUpdates = 0
With wsNew
For xRN = 1 To xLastRowNew
For xRO = 1 To xLastRowOld
' check for match
If .Cells(xRN, 3) = wsOld.Cells(xRO, 3) And _
.Cells(xRN, 4) = wsOld.Cells(xRO, 4) And _
.Cells(xRN, 5) = wsOld.Cells(xRO, 5) Then
' copy values
.Cells(xRN, 1) = wsOld.Cells(xRO, 1)
.Cells(xRN, 2) = wsOld.Cells(xRO, 2)
XUpdates = XUpdates + 1
Exit For
End If
Next xRO
Next xRN
End With
Application.ScreenUpdating = True

MsgBox "Completed - Updated: " & CStr(XUpdates)

End Sub