Auto filtering pivot table field

[Guest]

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,

 

[Guest]

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

 

[Guest]

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

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

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]

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..

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

:

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


:

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,