Solved

COLLUMNS COUNT IN EXCEL 2010 TAKES A VERY LONG TIME

Posted on 2011-09-18
15
231 Views
Last Modified: 2012-05-12
Hello,

I have an Excel 2010 file which was created by "dlmille" and given to me in order to sort columns and count blocks.  See related post "CREATE THICK BOX BORDER ON COLUMNS FOUND IN MACRO SEARCH".

It works just fine but I have one slight problem and a few additions to help me do my work a little faster.

1.The atached file "Count 1" takes approximately 4 minutes to sort the contents which does not seem too bad but when doing 20 or more in a day it adds up.  

The contents of this file only go to line 1,024.  I have had instances where the file content when as far as line 45,000.  This one took almost 2 hours to sort and that's really bogging me down.

So I was wondering if there is any way to speed up the process.

I have an i7 Q820 processor with 8 Gigs of RAM and Windows 7 Ultimate so I don't really understand why this is taking so long.  If the process can't be speeded up within the Excel file maybe there is something I could do in Windows to speed it up.  I really don't know what to do but I really need help with this.

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.

3.  It also really would be helpful if I could get the columns which are all "white" to have a thick border when I need to only.  I press CTRL B to do the count and also to put a thick border around the Yellow blocks, if it could be arranged so I press CTRL C to but a think border around the white columns it would be great.  That way I could have a border around the white columns but only when needed as I do not need the border around the white collumns all the time.

I wish I could of forseen this when I was geting help with this previously but it's only by working with it that I noticed this.

Thank you,

blueyes727
0
Comment
Question by:blueyes727
  • 9
  • 6
15 Comments
 

Author Comment

by:blueyes727
ID: 36557203
Sorry I forgot to add the files in my previous post.

Here they are.

Blueyes727
COUNT-1.xlsm
COUNT-2.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36557643
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
0
 

Author Comment

by:blueyes727
ID: 36557683
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
0
 

Author Comment

by:blueyes727
ID: 36557741
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36557759
>>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
0
 

Author Comment

by:blueyes727
ID: 36557775
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36557801
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
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:blueyes727
ID: 36557827
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36558429
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
0
 

Author Comment

by:blueyes727
ID: 36559803
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
0
 

Author Comment

by:blueyes727
ID: 36559826
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
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 36561614
I think you should post another question, so we can start fresh and others can jump in if they wish.

Here's your final file, with another pass to ensure no blank lines >3 high, after having removed < 3 high blocks.  No highlighting issues as a result.

Be sure to ASK A RELATED QUESTION in the messagebox, when you create a new one that's related to this one - it helps everyone!

See attached file.

Dave
COUNT-2-r6.xlsm
0
 

Author Closing Comment

by:blueyes727
ID: 36562861
Thank you for all your help.

Blueyes727
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36563773
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
0
 

Author Comment

by:blueyes727
ID: 36564428
I just posted it, sorry it took me a while I got busy.

Thank you,


Blueyes727
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now