Need a fast way to do large number of replace all operations

[Chris___]

I need to make systematic changes to all occurrences of large numbers of string values in large spreadsheets - along the lines of

If value is 123Foo modified value is 123Foobar
replace all values of 123Foo with 123Foobar
else
end if

real changes are a little more complex than that and the change depends on the original value.

The problem is that I need to be able to select a column/columns of tens of thousands of rows from a table change each value and then apply that change to every occurrence of that value in sheets of up to 500k rows and 50 columns split into various tables - and the layout and column headings aren't consistent enough to allow me to target specific regions so I need to check the whole sheet. Using the built in replace all function takes about 1.6 seconds per value so doing a whole sheet would take hours as it's searching through large amounts of data thousands of times.

I already have code written that will loop through a selected column and make the required changes rapidly and have already tried turning off screen updating, events et.c.

Any ideas for reducing the number of searches being done or massively speeding them up?

[TMS]

Maybe dump one or more columns into an array, process the elements in the array, and dump it back where you found it.

[Chris___]

considered that but was looking for other options as the overhead of setting up and rediming arrays might stack up quickly.

[xladept]

Have you tried something like this:

Please Login or Register  to view this content.

[scottiex]

well you can give it a go and see. Maybe doing sections at a time if necessary.

However I do wonder if you want to use something other than excel... 500,000 * 50 would be pretty big for excel even if you weren't doing anything else, let alone what you seem to be trying to do here.
Maybe you could save as a text file (or similar), edit with something fast like sed, PowerGrep or "Find And Replace Text", then open it in excel again.

[Chris___]

I know there's other tools out there that would do this better unfortunately I'm limited to whats installed on the machine.

[scottiex]

"the layout and column headings aren't consistent enough to allow me to target specific regions so I need to check the whole sheet."

By the way - I expect this isn't true. You can probably use whatever rules do exist regarding how the sheet is laid out to find some specific thing in each table then find the used range of that table. We would have to see the sheet to know if that works or how much time it would save.

[Chris___]

Have done that previously and it's worked but there's multiple (20+) different sheet layouts I have to deal with any of which are subject to change at any time so it's would mean regularly updating the specific ranges and I'm not always going to be available to do that.

[scottiex]

No not specific ranges.
I am thinking (just as an example) that there are a series of words that at least one of them is in all of the tables once (maybe a sample of the column headers).
If so you can

search the sheet for those items
find all the used ranges for those cells
from this list remove duplicate ranges if they exist (now you have all the ranges)
make the data arrays
process then
put them back in the sheet

[xladept]

Have you looked at post #4??? It might work!

Here's a version for all the sheets in a book:

Please Login or Register  to view this content.

[TMS]

considered that but was looking for other options as the overhead of setting up and rediming arrays might stack up quickly.

See post #4 from xlAdept .... no setting up required.

[xladept]

@ Trevor - Thanks

[Chris___]

Found partial solution

dump selection into array
Make array 1 dimension
convert to collection using error-resume to reduce to unique values
create dictionary of original values and modified values
loop over all cells in used range testing if value exists in dictionary and if so replacing

now replacing at 6500 cells/second which is lot better than 1.6 seconds per replacement

[scottiex]

What happened when you tried Xladept's code?
It would be polite to address that post.

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.