Best research method
 

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

Best research method
 

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" wrote in message
...
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

Best research method
 

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" a écrit dans le message de
...
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

Best research method
 

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


--
Bill_excelforum
------------------------------------------------------------------------
Bill_excelforum's Profile: http://www.excelforum.com/member.php...o&userid=32280
View this thread: http://www.excelforum.com/showthread...hreadid=526993