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

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

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

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

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