Thanks for perfoming your tests! Yes, given the simple actions required of the macro, I kind of figured the second one I posted would be faster than the first one. That percentage time difference (more than 3 times quicker) may not hold when you expand the data to the full amount you indicated was possible. The reason is the loop in the first macro only interates as many times as there are items in the unique list, but it physically interacts with the worksheet containing the data column, once per item... this direct interaction tends to be slow, but remember, we are only doing the interaction once per unique item. The second macro, on the other hand, gains it speed by doing almost all its work totally in memory, interacting only three times with the worksheet... twice to read in the values in the unique and data columns and a third time to blast the calculated data back out to the worksheet. I know that sounds like it should always be much faster to do, but the second macro does far more loop iterations than the first macro... it iterates more times by a multiplicative factor equal to the number of items in the data column. So, for you ulitmate limits, the first macro iterates 20 thousand times whereas the second one would iterate four billion times! It is hard to judge if the constant interaction with the worksheet for the first one would still eat up enough time to overcome the huge number of extra iterations being performed wholly in memory by the second macro. I think it would so I expect the second macro to still be faster, but I don't know that for a fact.Originally Posted by PcMax
Reply With Quote
Bookmarks