Fastest Way To Count Unsorted Column

[litrelord]

Hi,

I have a column in my sheet which has values between 1 and 200. I need to produce a summary but am using code so wondered whether it was more efficient to create a pivot table and take the values from that or whether to load the values into a collection.

Does anyone know the pros/cons of each method?

Thanks

Nick

 

[Stormseed]

Your question is not clear. You are doing a sum total summary based on something ? If there is something then what is that something ?

Also, could you post an example or a screen shot of how your data looks like alongwith your expected results ?

 

[litrelord]

I shoudl have been clearer. The summary would be a count of each of the number so for example:

Number Frequency
...
154 20
155 62
156 89
...

where the number 155 appears 62 times in the column I'm looking at.

If I was using a pivot table I would drop the column into the Row Labels and Values and choose the field setting to count instead of sum.

Alternatively I'd load the data from the column into memory and loop through it adding to a collection to count the values.

Or I could use an advanced filter to show unique values and then use a countif against this to show me the frequency.

I wondered whether anyone knew the performance advantages to each approach and when it was best to use each method.

Thanks

Nick

 

[schielrn]

IMO, the pivot table would be the quickest and easiest, but usually makes the file size the largest, when compared to formulas or VBA. If you are talking about using VBA to do the adding via using a collection then I would say, why not have VBA do the advanced filter and input the countif formula?

Personally I would go with the pivot table. It is very quick and easy to use and if someone ever has to take this over in the future it is much easier to show someone how to use a pivot table then to debug code if it ever crashed.

Hope that helps.

 

[litrelord]

Shielrn,

That makes sense. I realised shortly after I posted that I'd probably need to create a pivot table for a later stage in the process anyway but then it became a piece of curiosity more than anything.

Hadn't thought about the file size but it doesn't seem as bad in 2007 as previous versions thankfully (well there had to be something good to justify the rest of it!)

Thanks
Nick

 

[Richard Schollar]

If file size is an issue, you can always copy + pastespecial>values over the Pivot Table which effectively destroys it (but leaves the data).