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

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

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