Best research method

[Maileen]

Hi,

I have 2 sheets in excel with around 20.000 records each.
I would like to research on "sheet A" if data on "Sheet B" exists.
for that i want to use 3 criteria from sheet B and search in sheet A.

if found, i will do something from sheet B to Sheet A.

i tried to use autofilter but it is very slow method for 20.000 records. i
tried also to scan 1 by 1 but it is worse...

Have you got some idea how can i speed up this research ?

thanks a lot,
Maileen

[Tim Williams]

If you need to match on >1 column, try creating new columns in SheetA and
SheetB which concatenate the information from the match columns, and then
compare these across the sheets.

Tim


"Maileen" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have 2 sheets in excel with around 20.000 records each.
> I would like to research on "sheet A" if data on "Sheet B" exists.
> for that i want to use 3 criteria from sheet B and search in sheet A.
>
> if found, i will do something from sheet B to Sheet A.
>
> i tried to use autofilter but it is very slow method for 20.000 records. i
> tried also to scan 1 by 1 but it is worse...
>
> Have you got some idea how can i speed up this research ?
>
> thanks a lot,
> Maileen

[.]

I would :


1/ Sort Sheet1 on ColumnA
2/ Sort Sheet2 on ColumnB
3/ Do a loop on both Sheet1 and Sheet2. Something like

dim row1 as long, row2 as long
dim max1 as long, max2 as long

max1 = ubound(array1,1) ' array1 is sheet1.columnA
max2 = ubound(array2,1) ' array2 is sheet2.columnB

row1 = 0
row2 = 0
do
select case true
case (array1(row1,1) = array2(row2,1))
' found a match
' do something special ???

row1 = row1 + 1
row2 = row2 + 1
case (array1(row1,1) > array2(row2,1))
row2 = row2 + 1
case (array1(row1,1) < array2(row2,1))
row1 = row1 + 1
end select
loop until row1 = max1 or row2 = max2




"Maileen" <[email protected]> a écrit dans le message de
news:[email protected]...
> Hi,
>
> I have 2 sheets in excel with around 20.000 records each.
> I would like to research on "sheet A" if data on "Sheet B" exists.
> for that i want to use 3 criteria from sheet B and search in sheet A.
>
> if found, i will do something from sheet B to Sheet A.
>
> i tried to use autofilter but it is very slow method for 20.000 records. i
> tried also to scan 1 by 1 but it is worse...
>
> Have you got some idea how can i speed up this research ?
>
> thanks a lot,
> Maileen

[Bill_excelforum]

I would move all of the data in an Access database. If you need to do something in Excel with the results, it's easy (from Excel) to run a query on the database and pull just the data you need into an Excel sheet.

Bill