cannot group that selection

[DoubleACL]

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).

 

[Debra Dalgleish]

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

 

[DoubleACL]

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.

 

[Ken Wright]

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.

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

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------