Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Large Table Matching
Hi All
In xl2003 - I have two worksheets, the first contains a list of 17,000 rows, whilst the second contains a list of 7,000 rows. The first list needs to be updated with a value from the second list, the matching is based on two columns a date field and string text field. There are no duplicate combinations of the match key pair in either table. Scanning the first list (17k rows) and looking up the value in the second list (7k rows) is obviously very slow. Despite sorting the lists, exiting loops earlier as each record match is found etc. Does anyone have a match strategy to achieve the above. Cheers |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Large Table Matching
The only thing that may spped things up is if the data was on the same
worksheet. Switching between two worksheet is slowing down the program. You can always copy the data back to two seperate worksheets afrter the replacement is completed. "Nigel RS" wrote: Hi All In xl2003 - I have two worksheets, the first contains a list of 17,000 rows, whilst the second contains a list of 7,000 rows. The first list needs to be updated with a value from the second list, the matching is based on two columns a date field and string text field. There are no duplicate combinations of the match key pair in either table. Scanning the first list (17k rows) and looking up the value in the second list (7k rows) is obviously very slow. Despite sorting the lists, exiting loops earlier as each record match is found etc. Does anyone have a match strategy to achieve the above. Cheers |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Large Table Matching
Perhaps the SUMPRODUCT function is what's required here. Take a look at
www.contextures.com/xlFunctions01.html for a great explanation on how this function works. "Nigel RS" wrote: Hi All In xl2003 - I have two worksheets, the first contains a list of 17,000 rows, whilst the second contains a list of 7,000 rows. The first list needs to be updated with a value from the second list, the matching is based on two columns a date field and string text field. There are no duplicate combinations of the match key pair in either table. Scanning the first list (17k rows) and looking up the value in the second list (7k rows) is obviously very slow. Despite sorting the lists, exiting loops earlier as each record match is found etc. Does anyone have a match strategy to achieve the above. Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting a value from large table. | Excel Worksheet Functions | |||
Please help matching data from one table to another | Excel Worksheet Functions | |||
How do I link information from a large table into new table | Excel Worksheet Functions | |||
Matching table rows | Excel Discussion (Misc queries) | |||
matching data from table | Excel Programming |