| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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, |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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, |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot table page field filtering | Excel Discussion (Misc queries) | |||
| Pivot Table - Filtering Data Field | Excel Discussion (Misc queries) | |||
| Pivot table auto field selection | Excel Programming | |||
| How to make a field created a part of the Pivot Table Field List? | Excel Discussion (Misc queries) | |||
| Pivot Table - Filtering Page Field | Excel Discussion (Misc queries) | |||
Hybrid Mode
