How to get 2 sets of grouping levels for a set of data

Resolved · Low Priority · Version 2016

Fri 15 Jul 2016, 10:58 Delegate Michael said...

Michael has attended:
Excel Advanced course
Excel Dashboards for Business Intelligence course

How to get 2 sets of grouping levels for a set of data

Hi,

I'm looking for a way to get 2 sets of grouping ranges for a set of data.

For example if I have a set of distribution data for email marketing going from 1-30,000, and a number of results for open rates sent to varying database sizes, could I look at;

- Smaller more targetted sends in the 1-10,000 in grouping intervals of 1000 (1000, 2000, 3000.... 90000).
- And then the larger more general sends in the 10,000 - 30,000 in larger intervals of 5000 (10,000, 15,0000, 20,0000, 25,000)

Currentley when I try to create two pivot tables and change the grouping levels in one it applies to both.

Thanks,
Michael

Fri 15 Jul 2016, 11:55 Trainer Doug said...

RE: How to get 2 sets of grouping levels for a set of data

Hi Michael

There is a way to have two pivot tabels with separate grouping levels.

1 Give your data a range name (If it already has a name then create a second name for the same data range).

2. Create a second PivotTable based on the name. (Select Insert, Pivot Table, click in the source range and you can press F3 for Names).

3. Place the second pivot table under the first one.

The two pivot tables will refresh together but can have their own grouping intervals.

The only downside is that this will increase the file size as the data is copied two separate cashes. May be a problem for when pivoting very large amounts of data.

Hope that helps tho and thanks for your question Michael.

Regards
Doug
STL

Fri 15 Jul 2016, 11:57 Delegate Michael said...

RE: How to get 2 sets of grouping levels for a set of data

Great thanks!

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Recently used file list

Under the File menu, you may find a list of files at the bottom of the menu. These files represent the most recently used Excel spreadsheets. This file list provides a quick way for you to access your files.

You can disable the file list feature of Excel. This is done by

1. Choose Tools > Options menu. You will see the Options dialog box.
2. Ensure the General tab is selected.
3. Make sure the Recently Used File List check box is cleared.
4. Click on OK.

View all Excel hints and tips