• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cannot Group the Selection

C

cyliyu

Member
A pivot table was created and when I tried to group the no of days (Row Labels A4 to A10),it show me "cannot group that selection. I checked no empty cell, changed from text to no still the sample.

Can anyone help to direct me where are the errors?

upload_2017-9-20_10-24-15.png
 

Attachments

  • Book1.xlsx
    70.3 KB · Views: 7
Last edited:
@cyliyu I kid you not, I had this problem just 10 minutes ago. Pivot tables cannot group number columns if raw data has either blanks or text values in that column. It doesn't matter even if you filter them out thru slicers / report filters. One option is to create a mirror column in your source that has only numbers (for blanks you can use a very low value like 0 or -999 and for text use very large number). Then when grouping this in report, give group range to exclude either of them. Hope that helps.
 
I am trying to group the no of days (8,19,40,63, 87,157,218), so that I can assign an interval of 30 days, but and errors occurred.
If I highlighted 8 to 218, I managed to group it manually.
 
Last edited:
cyliyu said:
I am trying to group the no of days (8,19,40,63, 87,157,218), so that I can assign an interval of 30 days, but and errors occurred.
If I highlighted 8 to 218, I managed to group it manually.
Click to expand...
Hi ,

When I select the first two labels 8 and 19 , I am able to group them into a < 30 group.

When I select the fourth and fifth labels 63 and 81 , I am able to group them into a 61 - 90 group.

However , when I select 40 alone , Excel gives me the Cannot group the Selection error message.

Narayan
 
I am able to group the series of data manually. But I was looking to do it automatically as shown in the picture.
It is alright as I can do it manually. Thanks Narayan for your help & advise.

upload_2017-9-20_16-24-54.png
 
Part of your issue is that your Row Label field is mix of numeric and string value.
Excel is unable to interpret string into numeric grouping and thus requires you to do manual selection and into grouping.

There are few ways to work around this.
1. Clean your data to contain only numeric value in the row label column
2. Add helper column in data range to serve as grouping header.
3. Load data to PQ and use custom M function to assign grouping
4. Create separate table for group lookup. Create data relationship with original table.
 
You must log in or register to reply here.
Back
Top