Pivot Table grouping

[dbrackeen]

Hi, I'm sure this has been asked, but don't even know how to look it up. I have a spreadsheet that needs to have a pivot table created off it. The data looks like the following:

Workflows
Loan #
Age

The Row will be the workflows, the column will be the age and the data will be a count of loan #'s. They want anything over the age of 30 to be all grouped together. So the age will be 0 - 30+

Is there a way to do this and easily? I will have to teach others how to accomplish it on a daily basis.

Thanks
Dianna

 

[SkipVought]

Hi,

Maybe macro record doing it and save macro in your PERSONAL.XLS.

Export for all to IMPORT to their PERSONAL.XLS.

Or if this is ALL in one workbook, save the macro in that ONE workbook for all to use. Attach to a Keyboard Shortcut.

Skip,
[sub]
[red][/red]
[/sub]

 

[dbrackeen]

I'm not sure how to work with macros. I am a very basic excel user.

 

[LNBruno]

Have you tried selecting (from the menu) Data --> PivotTable and PivotChart Report and then tried using the wizard? Or pressing F1 for help and entering pivot table to help get started?

< M!ke >
Acupuncture Development: a jab well done.

 

[SkipVought]

You already know how to group, right?

Turn on the macro recorder and record setting or changing the grouping in your PT.

Turn off the recorder. alt+F11 toggles between the VB Editor and the sheet.

Copy the recorded code and post it, along with your question in Forum707, wher we answer question regarding code.

Skip,
[sub]
[red][/red]
[/sub]

 

[joeny1980]

This would be very easy to do.
Create another column, call it "Group"
And now put in a very simple formula that says:

=IF(B3<30,"30 or Under","Over 30")
Assuming B3 is where your Age is.

Write whatever you want in the quotes.

Now you can sort it or do whatever you want to analyze/group the data.

FYI - the best way to analyze/group/display it would be with pivot tables.

 

[ArnieNZ]

Hi
You can do it in the pivot table using the grouping command.
With Workflows in the ROW area and Ages in the COLUMN area and Loan in the DATA Area, RightClick on Ages group heading > Select Group and Show Detail.In the Grouping command box ,tick Starting at and enter 0 in this field, Untick Ending at and put 30 in the field, in the By: field, type 31. This should give you 0-30 and >31 as field groupings.
I hope this is what you are wanting.

Regards
Andrea

Arnie

 

[dbrackeen]

Thanks everyone. Andrea that was exactly what I was trying to do and couldn't for the life of me remember how to do it.