Auto filtering pivot table field
 

Hi,
Is there any way to use some of the auto filter options (such as begins with
or ends with) in masking a pivot table field?
I have fields containing GL codes and I want to group using the beginning of
the code:
e.g. 100000, 110000, 101000, 200000, etc... I want to be able to see all
fields starting with 1 or with 2 etc...
Can any one help in that?
Thanks,

Auto filtering pivot table field
 

Perhaps a workaround?...

Try this:

Insert a copy of your AcctNum column next to the original and use the column
heading: AcctGroup

Build the pivot table, with ROW fields in tnis order:
AcctGroup field
AcctNum Field
(then whatever other fields you need)

After the Pivot table is finished...
Right Click on the AcctGroup heading
Select Group from the list
Starting at: 1000000
Ending at: 9999999
By: 1000000
Click [OK]

The end result will look something like this:
AcctGrp Account Name Total
100000-199999 100000 Alpha 101
100010 Bravo 202
200000-299999 200020 Charlie 305
200030 Delta 400
200040 Echo 515

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Karim" wrote:

Hi,
Is there any way to use some of the auto filter options (such as begins with
or ends with) in masking a pivot table field?
I have fields containing GL codes and I want to group using the beginning of
the code:
e.g. 100000, 110000, 101000, 200000, etc... I want to be able to see all
fields starting with 1 or with 2 etc...
Can any one help in that?
Thanks,

Auto filtering pivot table field
 

Thanks Ron,
This workaround worked.
For some reason I couldn't group automatically, however I could manually
select the ranges and group them

Regards,
Karim

"Ron Coderre" wrote:

Perhaps a workaround?...

Try this:

Insert a copy of your AcctNum column next to the original and use the column
heading: AcctGroup

Build the pivot table, with ROW fields in tnis order:
AcctGroup field
AcctNum Field
(then whatever other fields you need)

After the Pivot table is finished...
Right Click on the AcctGroup heading
Select Group from the list
Starting at: 1000000
Ending at: 9999999
By: 1000000
Click [OK]

The end result will look something like this:
AcctGrp Account Name Total
100000-199999 100000 Alpha 101
100010 Bravo 202
200000-299999 200020 Charlie 305
200030 Delta 400
200040 Echo 515

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Karim" wrote:

Hi,
Is there any way to use some of the auto filter options (such as begins with
or ends with) in masking a pivot table field?
I have fields containing GL codes and I want to group using the beginning of
the code:
e.g. 100000, 110000, 101000, 200000, etc... I want to be able to see all
fields starting with 1 or with 2 etc...
Can any one help in that?
Thanks,

Auto filtering pivot table field
 

Only numbers and dates can be grouped automatically. Perhaps your
account numbers are entered as text, so they could include letters, or
start with a zero..

Karim wrote:
Thanks Ron,
This workaround worked.
For some reason I couldn't group automatically, however I could manually
select the ranges and group them

Regards,
Karim

"Ron Coderre" wrote:


Perhaps a workaround?...

Try this:

Insert a copy of your AcctNum column next to the original and use the column
heading: AcctGroup

Build the pivot table, with ROW fields in tnis order:
AcctGroup field
AcctNum Field
(then whatever other fields you need)

After the Pivot table is finished...
Right Click on the AcctGroup heading
Select Group from the list
Starting at: 1000000
Ending at: 9999999
By: 1000000
Click [OK]

The end result will look something like this:
AcctGrp Account Name Total
100000-199999 100000 Alpha 101
100010 Bravo 202
200000-299999 200020 Charlie 305
200030 Delta 400
200040 Echo 515

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Karim" wrote:


Hi,
Is there any way to use some of the auto filter options (such as begins with
or ends with) in masking a pivot table field?
I have fields containing GL codes and I want to group using the beginning of
the code:
e.g. 100000, 110000, 101000, 200000, etc... I want to be able to see all
fields starting with 1 or with 2 etc...
Can any one help in that?
Thanks,



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html