Pivot Table Grouping Problem

EdE · Apr 12th, 2002, 09:24 AM

Can anyone tell me why I cannot group this table by quarter and year? I keep getting "Cannot group that selection" message.

Sum of PCBA Buy
Monday Date Total
3/4/02 300
3/11/02
3/18/02
3/25/02
4/1/02
4/8/02
4/15/02 12
4/22/02 12
4/29/02
5/6/02
5/13/02
5/20/02
5/27/02
6/3/02
6/10/02
6/17/02 461
6/24/02
7/1/02
7/8/02
7/15/02
7/22/02

There is more information but for space left it out. Any ideas? MS knowledge base was no help.
Thx!

Mudface · Apr 12th, 2002, 11:06 AM

Check through your source data and make sure that every entry in your Date column actually is a date. I've had this problem before and found it was due to one blank cell (out of 5000

). You might need to redo your pivot table (or at least remove the Date field and then pop it back in) once you've found and corrected any non-date entries. HTH.

EdE · Apr 12th, 2002, 11:31 AM

That wasnt it. The date is being pulled from a lookup formula based on some other data in the sheet. The dates are all c1+7 where c1 is the first date. Any other ideas?

Mudface · Apr 12th, 2002, 11:39 AM

I just copied and pasted your data into a workbook and at first I couldn't group it, but then I sorted the date column and it grouped OK. Could you try that?

[ This Message was edited by: Mudface on 2002-04-12 10:40 ]

EdE · Apr 12th, 2002, 12:43 PM

Ok that didnt work either. This is how the information is gathered. Column A has calendar week #, which drives monday date(column b), which drives month date (column c). because these are templates, the three columns in a table elsewhere in the sheet, so we can change the years. IE. Lookup in column A to get B and C. I have tried sorting to no avail. What really is weird is that I can group a pivot table based off the detail table. Any more ideas. This is a 3 year rolling item and do not want to have to set up a bunch of formulas just to do a summary.

Thanks!!

I figured it out. To group, you cannot have any, and I mean any, empty cells.

[ This Message was edited by: EdE on 2002-04-12 13:49 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 12th, 2002, 09:24 AM	#1
EdE Board Regular Join Date: Apr 2002 Location: A Mile High!! Posts: 230	Can anyone tell me why I cannot group this table by quarter and year? I keep getting "Cannot group that selection" message. Sum of PCBA Buy Monday Date Total 3/4/02 300 3/11/02 3/18/02 3/25/02 4/1/02 4/8/02 4/15/02 12 4/22/02 12 4/29/02 5/6/02 5/13/02 5/20/02 5/27/02 6/3/02 6/10/02 6/17/02 461 6/24/02 7/1/02 7/8/02 7/15/02 7/22/02 There is more information but for space left it out. Any ideas? MS knowledge base was no help. Thx!

Apr 12th, 2002, 11:06 AM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Check through your source data and make sure that every entry in your Date column actually is a date. I've had this problem before and found it was due to one blank cell (out of 5000 ). You might need to redo your pivot table (or at least remove the Date field and then pop it back in) once you've found and corrected any non-date entries. HTH.

Apr 12th, 2002, 11:31 AM	#3
EdE Board Regular Join Date: Apr 2002 Location: A Mile High!! Posts: 230	That wasnt it. The date is being pulled from a lookup formula based on some other data in the sheet. The dates are all c1+7 where c1 is the first date. Any other ideas?

Apr 12th, 2002, 11:39 AM	#4
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	I just copied and pasted your data into a workbook and at first I couldn't group it, but then I sorted the date column and it grouped OK. Could you try that? [ This Message was edited by: Mudface on 2002-04-12 10:40 ]

Apr 12th, 2002, 12:43 PM	#5
EdE Board Regular Join Date: Apr 2002 Location: A Mile High!! Posts: 230	Ok that didnt work either. This is how the information is gathered. Column A has calendar week #, which drives monday date(column b), which drives month date (column c). because these are templates, the three columns in a table elsewhere in the sheet, so we can change the years. IE. Lookup in column A to get B and C. I have tried sorting to no avail. What really is weird is that I can group a pivot table based off the detail table. Any more ideas. This is a 3 year rolling item and do not want to have to set up a bunch of formulas just to do a summary. Thanks!! I figured it out. To group, you cannot have any, and I mean any, empty cells. [ This Message was edited by: EdE on 2002-04-12 13:49 ]