grouping in pivot table

hughp

New Member
Joined
Sep 9, 2002
Messages
4
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

New Member
Joined
Sep 9, 2002
Messages
4
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

Well-known Member
Joined
Mar 2, 2002
Messages
1,934
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

New Member
Joined
Sep 9, 2002
Messages
4

ADVERTISEMENT

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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Book1
ABCDEFGHI
1YearYrGroupApproach1Approach2
220012001
320022002CountofYearCountofYrGroup
420032003YearTotalYrGroupTotal
5200420042001-2005520011
6200520052006-2010520021
7200620062011-2015520031
8200720072016-2020520041
9200820082021-2025520051
10200920092026-2030420061
1120102010GrandTotal2920071
1220112011-201520081
1320122011-201520091
1420132011-201520101
1520142011-20152011-20155
1620152011-20152016-20205
1720162016-20202021-20255
1820172016-20202026-20304
1920182016-2020GrandTotal29
2020192016-2020
2120202016-2020
Sheet1

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

hughp

New Member
Joined
Sep 9, 2002
Messages
4
Mark W.

Thanks for an elegant solution.

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

hughp
 

Forum statistics

Threads
1,144,311
Messages
5,723,643
Members
422,508
Latest member
Lordkit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top