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,

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,

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,

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