COLLUMNS COUNT IN EXCEL 2010 TAKES A VERY LONG TIME

Sorry I forgot to add the files in my previous post.

Here they are.

Blueyes727
COUNT-1.xlsm
COUNT-2.xlsm

Blueeyes727 - sorry you are having delay problems.  There's more than one way to "skin a cat", and there are several things going on that could be sped up, I believe.  However, when I run on my machine (Vista 4 GB Ram, QUAD Core) it runs in 36 seconds (Count1, sheet 11).

I'm not sure what the difference in Count1 and Count2 are...

I'm addressing item #1, for now.  Changes made include:

1) removing the dynamic/volatile functions on the far right that count colors - this is only needed when you "Initialize", correct?  So, after the values are set, nothing would change them - please confirm.  The "Initialize" function was re-written to delete (if the exist) the summary functions to the right, then regenerate them, setting values (instead of leaving the volatile functions which costs time).  If data is being changed and the values need updating, you can merely run "Initialilze" again.

2) I put a timer in, so it will tell you how long a sort tool.  Initialize takes my machine about 15 seconds.  My last sort on sheet 11 went from (my time) 36 seconds to 8 seconds, with the changes mentioned.

please try this and advise timing.  Also, if its looking good, try with your very large file.  There's a lot of range movement in the sort - to change that would get complicated on my end - let's see how this goes, first.

Here's the revised file, unsorted, so you can try it (I only kept Sheet 11 with the most data).

Please run "Initialize", then run "sort", and advise.

Dave
COUNT-1-r1.xlsm

That is correct, once the values are set nothing will change.

The file you sent me worked very well, initialize took 4 seconds and the sort also took 4 second.  Now I am going to try the larger file and will let you know how it went in a few minutes.

Thank you,

Blueyes727

I tried to sort with a file that has over 4,000 lines.  It initialized in 11 seconds when I ttried the sort it ran for over 10 minutes then I terminated the program.

I attached the file with the contents.

Thank you,

Blueyes727
COUNT-1-r1-TEST.xlsm

>>2.Sometimes in the final count, some of the columns wind up being just 1 or 2 rows high instead of 3 rows high (See "Count 2" file attached). In this case I can't sort because the way the count was setup, Excel is looking for columns of 3 rows high and when there are rows which are smaller it just gets all messed up.  Would it be possible to get Excel to delete the columns which are not 3 rows high as I do not need these when they happen to be in the final count.


I could not find an example of what you were talking about in Count 2, though perhaps the 1 or 2 column high blocks are the problem?.  The algorithm is independent of how high the blocks are (apparently you have some that are 1 to I think 5 rows high in some examples in Count 2).  However, I can't imaging how it would sort on the 1 row high items.  I've added a macro to "clean < 3 high" to remove 1 and 2 row high blocks.  This macro will run as the first step of the "sort" routine, so there's no need to call it, independently.

See attached...

This one has "Initialize", and then as part of the "sort" routine, it eliminates < 3 row high blocks.

PS - I'll look at your latest post, now

Dave

COUNT-2-r1.xlsm

COUNT 2 r1 ran perfectly, it initialized in 4 seconds and the sort took only 79 seconds.  It deleted all small columns and kept only the columns with 3 rows.

This is awesome.

Thank you,

Blueyes 727

Blueeyes727 - how many blank lines need to be between each block?  Will 1 suffice?  Otherwise the logic gets a bit dizzy trying to preserve what you have - some have > 3 blank rows, etc...

Dave

I would prefer 3 but if need be 1 will be fine.  I guess 3 lines between the blocks does add a lot of space after a while.

Thank you,

Blueyes727

Ok Blueeyes727 - It currently pares down to one blank line between rows.  I spent my time focusing on improving the sort time, and finally came up with an (obvious?) brainstorm - let Excel do the sorting, instead of doing it in VBA!

I created a NEW TAB that you need, which has a named range, called "holdFormulas" and its on the HOLDFORMULASHT tab (which can be hidden).  To save time (as opposed to coding these crazy formulas in VBA) I copy/paste these formulas in at the right time and they give me what Excel needs to sort all the rows at one time.

Here's what you're waiting to read:

Short tab (1000K records): Initialize - 12 secs, Sort/block - 8 secs
Large tab (4000K records): Initialize - 51 secs, Sort/block - 162 secs (2.7 minutes)

I'm not sure you can do it any faster than that, as Excel is doing the sorting.

Diagnosis:  I was using a bubblesort, copy/pasting the ranges as values were swapped out - that played heck with memory and speed.  I needed an alternative, so setup the math logic in those holdformulas to determine the value of each block (and its successive blank rows) that was unique and ordered it in a way to support a standard Excel Data Sort.  Once I was able to do it manually, coding it was fairly simple.

Also, the speedup is helped by not having those dynamic/volatile functions in the worksheet which I learned based on your input, they're not needed after "Initialize"

Process: Run "Initialize", do what you need to do, then Run "Sort" which gets rid of blocks < 3 high, and eliminates all the extra rows.  Frankly, its another mental leap I didn't have, today, to get your 3 rows back, and that would also cost additional processing time.  If its any consolation, I could give you one more boot by making the blank row between blocks BLACK or something (let me know).

Here's the attached workbook, with original Count1, and Count2 tabs, and test Count1 and Count2 tabs so you can see starting/ending point after running the macros.

As a reminder (plus bonus #3 you asked for), I changed the ctrl's to make them easier and to not override excel control sequences you might need:
ctrl-i = "Initialize", ctrl-y = "Sort & Box Yellow Blocks", ctrl-w = "Sort & Box White Blocks"

However, you can setup any shortcut you want, but clicking MACROS on the Developer ribbon, and selecting the macro, clicking OPTIONS for the control sequence.

PS - you can alternate between boxing Yellow or White as many times as you like, after the "Initialize" is done...  The blocking is part of the sort algorithm, so you have to wait for that - I didn't have the time to pull all that out and separate it...

In the spirit of process as fast as possible, I left off status messages during the run, but if its taking a while and you need that type of messaging, just pop in another question to be advised every 1000 records of processing or something like that :)

Here's the file.


COUNT-2-r5.xlsm

It works perfectly the only thing is when I sort by white blocks, the last line of the last block is highlighted in blue so I wind up with 2 rows with  yellow highlights and 1 row with blue highlights.

I attached the file so you  can see what I mean.

Thank you so much,


Blueyes727 COUNT-2-r5.xlsm

I was thinking, is there any way I can keep both white and yellow blocks with a border and count at the same time if I need to?  I can see this woudl be useful in the near future.

Then maybe be able to sort it by white or yellow count when I wish.  I think I'm pushing it here but I thought I would ask anyway.

Thank you,

Blueyes727

Thank you for all your help.

Blueyes727

Blueeyes - let me know when you're going to post - I have a few ideas on the items you'd like added.  Should not be too big a deal.

Cheers,

Dave

I just posted it, sorry it took me a while I got busy.

Thank you,


Blueyes727