CountIF and Sort Function

[keerthivasan.u]

Hi,


This is keerthivasan From Chennai, I face few problems in my Excel 2007, I have 5 Lakh customer's Mail ID's in Excel 2007, Using CountIF function i find the required result, then if i choose the save option, but it took more than 2 hours to save the file and then i am using sort function(more than 2 hours to sort) to the values from small to large based on the count result, then once again if i choose save option means it also took more than 2 hours to save, then based on the count i have to delete the '>1' values (E-Mail's) then once again i have to save, so again it took more than 2 hours....

I'm Totally frustrated to do this in Excel 2007, why this much time delay caused, It's too annoying for me, Kindly please help me to find a solution for this issue, Thanks in Advance. Please someone help me soon.


Thanks & Regards,
keerthivasan.U

[etaf]

how big is the file and number of rows you are processing - sounds like a huge file or a very underpowered PC

2hours to save - is this to a remote location or just onto the pc harddrive

if you open a new file and add a few entries and calcs - does that save quickly ?

whats the spec of the PC
CPU type and how much RAM (memory) do you have
what windows version are you running

[keerthivasan.u]

Hi,


Thanks for your reply, I did all these things from my Laptop only, not in any remote location, i'm using windows 7 OS , 360 GB HDD, 2GB Ram and I3 Processor,
total rows=5,17,035 and file size is 7 Mb

Yes, i checked in new excel file, those calculation and save speed is normal only, even in this file also before i choose the count option the file saving speed is normal only, after did the count function only its very very slow, while saving in the bottom of Excel shows
Calculating : (4 Processor(s)): 1% (it starts from 1% to 100%) i have to wait till it reaches 100 %.

kindly suggest how to do it soon.

Thanks & Regards,
Keerthivasan.U

[etaf]

rows=5,17,035

max rows 1,048,576 - so thats 517,035 rows you have

that will take some time - i'm running on a i5 with 6gb of memory and just put 1,2,3,4,5,6,7,8,9 into cells a1:a5170 and in B1 countif(a1:a517000,9) and clicked to copy the formula down and its been running for 25mins and showing about 6% complete

sounds like its also doing a calc on save as well

if you change the options to manual calc and deselect the calc on save - that may help - then you use F9 to do the calc - which may take two hours - but is should just save straight away

otherwise you may be better off using access to do the manipulation and export to excel

[keerthivasan.u]

Hi,


Thank You Very Much... I think its working good, because the automatic calculation only took too much time... I will check.. Anyhow Really Thank for your Help.


Thanks & Regards,
Keerthivasan.U