cannot group that selection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DoubleACL - 27 Feb 2006 13:51 GMT

I'm following the FAQ for taking a list of dates produced by my piviot table
and trying to group them by week. When I select the field name, right click
and select grouping, I get "cannot group by the selection". My data source
for this field in the table is all dates (other than the header).

Signature

Thanks!

Reply to this Message

Debra Dalgleish - 27 Feb 2006 14:17 GMT

Maybe you've included some blank rows at the end of the source data, or
some of the dates are entered as text. There are suggestions for fixing
the problem here:

http://www.contextures.com/xlPivot07.html#Problems

> I'm following the FAQ for taking a list of dates produced by my piviot table
> and trying to group them by week. When I select the field name, right click
> and select grouping, I get "cannot group by the selection". My data source
> for this field in the table is all dates (other than the header).

Signature

Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply to this Message

DoubleACL - 27 Feb 2006 15:40 GMT

Got it. My data has a lot of blanks, and if I delete the rows containing the
blank fields, I can build a grouping selection. Normally, I can remove the
blanks from being reported in my Pivot Table by deselecting blanks in the
field options box. However for grouping, I find that merely deselecting
blanks does not then allow grouping; the data itself cannot have blanks,
which is going to be a pain. I'm going to have to create some temporary
tables to accomplish the grouped report, unless you know of any other
solution.

Signature

Thanks!

> Maybe you've included some blank rows at the end of the source data, or
> some of the dates are entered as text. There are suggestions for fixing
[quoted text clipped - 6 lines]
> > and select grouping, I get "cannot group by the selection". My data source
> > for this field in the table is all dates (other than the header).

Reply to this Message

Ken Wright - 28 Feb 2006 00:19 GMT

I often get a similar issue when using a data dump form our pricing system,
but the blanks are usually associated with zero value items, so what i do is
select the entire range of dates, do edit / Go to / Special / Blanks and
then enter in the lowest date (can be any date) that exists in my data and
use CTRL+ENTER to enter it. This puts it in every blank cell, and then all
you need to do is refresh the PT. Because they are zero value items, it
doesn't matter that these now get included in the grouping.

Don't know if it will work for you, depends on your data, but worth
mentioning.

Signature

Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

> Got it. My data has a lot of blanks, and if I delete the rows containing
> the
[quoted text clipped - 20 lines]
>> > source
>> > for this field in the table is all dates (other than the header).

Reply to this Message