Changing values within a huge table

Hi wondering2
Try this code in the attached workbook. It works between the list of numbers and names on sheet1 and changes all the numbers to names in sheet2.

Please Login or Register  to view this content.

Writing code seems to work for me tonight. Sometimes it takes much longer. enjoy.

If you have a number corresponding to a name, do you have the names and numbers in a table?

Any time you loop through a matrix of cells it is slow!

It might be a good idea to post a small section of your data (perhaps a 10x10 grid), and if you have a list of names (change them if they are peoples or sensitive data)

I am guessing that the quickest way is to read the names and numbers into arrays and the table and work with it all in memory, I think <5 seconds would be a good estimate of the time of a routine!

Depending on your data layout and the numbers/names format of course!

if the numbers are abitary then it will be slower if they are all sequential then it would be very fast!

edit: below composed prior to above post - apologies for the repetition within

I think with 4m cells to process you should be looking to work with VBA Arrays - the above routine would I think be quite slow on a large volume of data (even if you optimise App settings etc)

It's not clear if the ID# are sequential etc or not - using MarvinP's sample file but extended to a 2000 x 2000 matrix we could run something like:

Please Login or Register  to view this content.

That said there are plenty out there better at working with Arrays far more efficiently than I - the above takes around 10 seconds on my machine but that's with 4GB RAM etc... so it will fluctuate.

My guess is the better programmers around here will be able to improve upon that significantly - whilst also catering for the fact that the name table may not be in order or follow 1 to n pattern.

Note: the above also assumes all Number entries are valid - ie no missing names - again this can be catered for if necessary.

Yikes

So to be clear: you're saying you have a 2000 x 2000 matrix with each cell containing (potentially) a string detailing any number of "terms"
(with each "term" to be translated per table)

If so I have to say - though you can do this with VBA without too much difficulty (you can Split the terms easily enough [with an Application.Trim to remove superfluous spaces]) - it won't be particularly fast over that large a range.

We can give you a working version for your sample but again when you run it in your "real" file you might as well go for a long lunch.

At least that's my opinion... others may see it differently.

On a final note: a number of "names" are blank in your sample - is this the case in your real file or did you simply get bored dummying values ?

Final question:

you have some IDs listed that don't appear in your list
eg A1 contains 4680 yet names end at 3592

is this the reality and if so what should happen to those "missing" ID values in the strings ?

Attached is your sample slightly revised (name listing so all populated)
Where the "term" does not exist the "term" persists in the final string.

I've added a BEFORE sheet so you can replace the results and test again etc... run "EXAMPLE" to translate SHEET1

On a tiny sample file this will be pretty instantaneous, however, on a matrix containing 4 million cells it won't be ... though as mentioned before I'm sure there are big efficiency gains to be had with more thought / skill.

Please Login or Register  to view this content.

What's the spec of the machine you're working on ?

I took the sample 10 x 10 matrix and it's content and replicated that over a 2000 x 2000 matrix.

Having created the 4million cell matrix I then ran the last routine I posted against it and the completed results were returned in around 140 seconds.

Not great performance by any means but I did not encounter any issues myself.

You must be running XL2007+ so I'd have a presumed a relatively new client with decent RAM etc...