Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Research Button | Excel Discussion (Misc queries) | |||
Research | Excel Programming | |||
research bar | Excel Discussion (Misc queries) | |||
Research Popup | Excel Discussion (Misc queries) | |||
using research pane | Excel Programming |