Pivot Table Problem - 'Cannot group that selection'.

[kanuvas]

Hi all,

I have a problem with Pivot Tables and I believe something to do with the format of data.

I have attached a sheet which contains the problem.

Basically there is a set of data I pull from a macro I devised. It prints out all information as a 'String' variable.

This information is displayed in columns A to C.

If I select all values from columns A to C and make a pivot table, assign month to the drop row and To-Pay and Paid to the Drop Data. I then try and group the month in the drop row but an error occurs 'Cannot group that selection'.

I tried the same thing with a set of data I wrote in columns F to H and it works fine.

Any ideas how I can solve this?

Cheers

Simon

[DonkeyOte]

Not all of the values in B are dates... to illustrate:

A6: =ISNUMBER(B6)
copy down

Where A is FALSE values are not Dates (given dates are numeric) - you need to ensure all values in A are date values - enter in appropriate date format to your region.

EDIT:

Having looked at the sequence of dates you may find it easier to use a helper column to correct, eg:

A5: =Mth
A6: =IF(ISTEXT(B6),DATEVALUE(B6),DATE(YEAR(B6),DAY(B6),MONTH(B6)))
copied down

Revise PT source to include Column A and use this column as your Row Field against which you can group.

[kanuvas]

Not all of the values in B are dates... to illustrate:

A6: =ISNUMBER(B6)
copy down

Where A is FALSE values are not Dates (given dates are numeric) - you need to ensure all values in A are date values - enter in appropriate date format to your region.

Thanks for that.

How do I make those values dates? Is there a function in excel to make this happen as my macros got all sorts of issues if I change them from String.

[DonkeyOte]

I did a bad and edited my last post after you most probably read it... see note re: use of "helper" column for use with PT.

[kanuvas]

I did a bad and edited my last post after you most probably read it... see note re: use of "helper" column for use with PT.

Great that worked well. Thanks for the help.