grouping in pivot table

[hughp]

I'm having trouble gruoping years,stored as number,in pivot table. Excel help says that some numeric data can't be grouped. I've grouped a small test file but the large (1000 recordswith 10 fields) won't gruop on "years".

Any suggestions?

hughp

 

[DonkeyOte]

Change it to a text field before creating the pivot - eg date field A1 = 25/08/02

Change to = TEXT(A1,"YYMM") or whatever and use this field in your pivot.

 

[hughp]

Thanks for the suggestion.I'd like to keep the year data numeric rathar than date or text.I have removed blanks from raw data but still the grouping defies me.

hughp

 

[eliW]

On 2002-09-10 03:15, hughp wrote:
Thanks for the suggestion.I'd like to keep the year data numeric rathar than date or text.I have removed blanks from raw data but still the grouping defies me.

hughp

Hi,
Please provide some information.

How is your YEARS field expressed?
Is it just a 4 digit number (eg 1999)?
And also what kind of "grouping" do you need?

Eli

 

[hughp]

Hi Eli

the date is stored as yyyy. The range is from 2001to 2076.

I would like to group in 5 year groups.

It would be great if the years 2001 to 2010 were not grouped but the later years were.

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I
1	Year	YrGroup		Approach1			Approach2
2	2001	2001
3	2002	2002		CountofYear			CountofYrGroup
4	2003	2003		Year	Total		YrGroup	Total
5	2004	2004		2001-2005	5		2001	1
6	2005	2005		2006-2010	5		2002	1
7	2006	2006		2011-2015	5		2003	1
8	2007	2007		2016-2020	5		2004	1
9	2008	2008		2021-2025	5		2005	1
10	2009	2009		2026-2030	4		2006	1
11	2010	2010		GrandTotal	29		2007	1
12	2011	2011-2015					2008	1
13	2012	2011-2015					2009	1
14	2013	2011-2015					2010	1
15	2014	2011-2015					2011-2015	5
16	2015	2011-2015					2016-2020	5
17	2016	2016-2020					2021-2025	5
18	2017	2016-2020					2026-2030	4
19	2018	2016-2020					GrandTotal	29
20	2019	2016-2020
21	2020	2016-2020
Sheet1

This message was edited by Mark W. on 2002-09-10 18:47

 

[hughp]

Mark W.

Thanks for an elegant solution.

I am trying to group in a pivot table and it just won't work.

hughp