The best way to add to long list of values from multiple sheets

[oliviar]

Hi Guys,
This has no code, just a lot of data.
Each week I receive 20 - 30 workbooks filled with numbers that have to be added to the tally next to names on a masterlist.

There are 6000 names on the masterlist, and each workbook will be adding a few to the tally next to some (but not all) of the names.

Each name on the master list has a unique key number, and each individual workbook also references the master key (as well as the name)
I need a way to copy and paste (or whatever ) match up the master keys on each document and add to the tally.

How would you solve this so that it can be done reliably, and reletively quickly, each week?

 

[Fazza]

Hi. I'd use a database type approach - via VBA & ADO using UPDATE queries. Probably in an add-in file. Loop through the new datafiles; UPDATE queries like below. With none of the files needing to open and using ADO, execution should be quick. HTH, Fazza

UPDATE MasterList ML
INNER JOIN SomeData SD ON ML.Key = SD.Key
SET ML.Tally = ML.Tally + SD.Tally

 

[oliviar]

Hi Fazza,
That looks very impressive, but I don't know how to use it!
Can you elaborate a little more?

 

[Fazza]

Well, VBA is for programming in Excel & ADO is to work with databases. If you're not familiar with these, you might find it better to use different approach.

 

[oliviar]

If I dump the new data into a spare worksheet on the masterfile, can VB then be used to compare the new data to the masterfile, and add the values when the names match?

 

[Fazza]

Yes. Simple might be via temporary extra field on the master list. Such as a SUMIF using the key to return the new data value in a new column. These will be zero where no match was found. Then pastespecial values these over the pre-existing tallys, & erase the temporary field.