How many groups can you make in a pivot table?

[amalfaro]

I have a co-worker trying to simplify another departments report. He needs to be able to create more than three groups. Is this possible? We can't find anything online about it and so far he can't make it happen. What he had was a bunch of nested IF formulae that seriously bogged down the file. He really needs another solution since processing time is part of what they want him to improve.

TIA,
ama

[oeldere]

I advice you to take a look at a pivot table (solution).

[JosephP]

are you referring to grouping items within a field or number of fields you can group?

[amalfaro]

He's decided to make due in order to meet a deadline, but it was grouping within a field. Oeldere, I'm not sure if you meant for us to try looking at a pivot table and attempt the grouping to see how many times it could be done? If that's what you meant, we did that and got stuck at three.

[JosephP]

you should definitely be able to do more than three-have you checked if it's a problem with just that file? what happens when you try and do more grouping?

[VKS]

Hi, In my opinion there is no limitation on group numbers(Have done much more than 3 groups) as long as we dont try and take any item in more than one group.
Do you think you can post a sample PT?
Best Regards/VKS

[amalfaro]

He doesn't want to post a sample but I had him walk me through how he was grouping. When he right clicks and selects grouping, he gets a dialog that asks for "Start with", "End with", and "By". "Start with" seems to be min, "End with" max, and "By" gets added to "Start with", so he ends up with <[start], [start] to [end], and >([by]+[start]) as his categories. He has no other options in the grouping dialog.

VKS - I don't understand what you mean by "as long as we don't try and take any item in more than one group"

[JosephP]

sounds like you're grouping numbers in which case the By is the banding size-eg if you group by 5 you get bands of 5

[amalfaro]

Hi Joseph,

So I didn't understand what you meant by banding and found this: http://support.microsoft.com/kb/214259. Neither my coworker nor I were using the grouping correctly. With the range he had set up, he was never going to have more than three groups. He's going to re-evaluate.

Thanks so much for the help everyone.

[VKS]

Hello amalfaro,
For the sake of understanding, lets say I have a list of countries in the row labels. I decide to make some groups (more than 3)by right clicking some randomly selecting countries . I create 6 groups randomly selecting few countries for each group. I get one more column in my Pivot Table after creating the groups called Countries 2 (Ref. attached pic).
After creating groups I change my mind and think I should have India(Group 6) and China(Group 3) in one group and I again select both of them (Currently under different groups) and group them by right clicking. Pivot table puts them in one group called Group 3(China’s original group) and throws out the other two members from original group 3 (Cyprus and Czech republic in this case).
I hope I was able to explain myself.
Best Regards/VKS

[amalfaro]

Yep, that makes sense now. Thanks for your help :D