Scripting Dictionary to compare two columns and output a result

For starters.

Please Login or Register  to view this content.

Is it safe to presume the rows range we want to process varies based on what you paste into column E? Is the existing data overwritten or do we append the new stuff under what exists in column E?

Arrays and dictionary could certainly help speed up the process. Honestly itd be even quicker to do with formulas and 2-3 helper columns. Formulas could give the results you want and a simple copy/paste values over the old data would give the results you want.

Ill try and update your sample with samples of both approaches and post back

EDIT: and in your descriptions you have "moved out" (D2<E2) and "moved in" (D2>E2) but no third option if they are equal, or if they are both blank. Is there any other condition to consider or are these the only 2 possibilities?

EDIT2: Also, this status in column F I presume should be prior to moving content from E to D, correct? as after D should no longer have blanks and many of the evaluation will be equal as we just copied new date over blank original.

It turns out this is really easy to accomplish with formulas. I cant imagine VBA being faster especially on a large data set. You may consider using the formulas then a simple macro to do the copy over if you want. I did the formula approach, took me less than 5 mins to complete from scratch. Going forward on a large data set you could probably update column D with ~10 seconds of manual effort to copy/paste special.

I started working on a macro as I had an idea of applying a filter to only show blank entries in Col D and then using the specialcells visible property to copy over the values from E to D in 1 move but even thought specialcells says it returns a range, it really doesnt and I realized no matter how you approach this you need to load all of the data from D into a range, array, dictionary or collection and then loop it and evaluate the blanks, which takes time (with ranges being the slowest, then collections, then likely about a tie between arrays and a dictionary in large data sets). The looping will never be faster then the results of the formulas as far as I can tell. I honestly gave up on writing a macro for this as there is a very reasonable and easy approach using formulas (and a simple copy/paste macro if you just cant bring yourself to select a range, copy and select a destination range and paste values).

I put this in F2 and filled down:

Formula:

Please Login or Register  to view this content.

Put this in G2 and filled down:

Formula:

Please Login or Register  to view this content.

Then its simply copy from G2:G# (where # is last row) over D2:D#. Done.

The only case this doesnt account for is in Col F when we just moved the entry from E to D, thus they are equal as you never defined what to return in that scenario as far as I can see.

I honestly think this is your fastest/best approach with a large data set for what you want to accomplish.

Originally Posted by sriley5

Finally, I was hopping to have this processed via macro which adjusts with a dynamic range. The reason for the macro as opposed to a formula is because of a couple reasons. One, the actual workbook is around 20mb, which means any work done within would recalculate those formulas becoming cumbersome to the user who probably has 5 or 6 workbooks open at the same time. Second I try to limit the number of formulas in a workbook since there are several people dinkin' around with the file - past experiences of people messing with the formulas or typing over them causes more grief than anything. Hope this helps, thanks again

To be honest these are all easily solvable issues.

You can use a dynamic named range or a table to allow your ranges to adjust dynamically with your data (thus not needing to update your formulas as the range shrinks/grows).

If calculations are cumbersome in a file, simply switch to manual calculations. Its then no burden on the file (and user) until you decide to recalculate in which case the time to calc is likely less then the time for a macro to run. A very lose, generalized reason why equivalent formulas are faster than code is that VBA is single threaded, formulas can use as many threads as your CPU has. Its not the only reason but one of the main ones. There are even keyboard shortcuts that by default can calculate just the active sheet or the whole workbook, further granting control over calculation times.

If people are goofing up formulas, you should see what people try and do with macros. Especially without proper error handling. I've had many instances over the years where people get a debug prompt when a macros stops working or runs into an issue and they wipe out the entire macro or an entire sub, or simply goof around and break it.

With formulas you can protect the sheet and prevent them from altering the formulas. Sure you can protect the whole VBA project too, but you still dont get the speed and simplicity of using formulas.

I am only pressing the matter because I do really feel its your best and fastest approach to getting the results you want. Your answers to my 2 edits coincide with the formula answer I gave you. You just have to decide what we want it to do when the columns are the same date (unless the formula already gives the desired result in that case).

I would at least encourage you to make 2 copies of the file, one using the formulas as a solution and one using whatever macro based solution you feel is best and compare the time it takes for the macro to run and the time it takes the formulas to calculate. I would be amazed if the times are even close in a 20MB file with 30,000 rows. Maybe someone smarter than I can find a way to shift the data without looping range objects in VBA, if so it may be a reasonable speed, but even looping, manipulating and dumping the array back to sheet will carry a significant run time with it as far as I can tell.