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

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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 <news:<#[email protected]>

> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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 <news:<#[email protected]>
>
> > 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