| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
anyone pls. help to pivot ???
I have 299 main items with 4 sub-items each.
See below one of my primitive table established€¦ REF Col. A B C D E Row 1 M. ITEM DESC CAT. 1 CAT. 2 G.TOTAL 2 Item 1 Total of Item 1 $3,000.00 $4,000.00 $7,000.00 3 Sub-item 1.1 $1,000.00 $1,500.00 $2,500.00 4 Sub-item 1.2 $500.00 $1,000.00 $1,500.00 5 Sub-item 1.3 $1,000.00 $500.00 $1,500.00 6 Sub-item 1.4 $500.00 $1,000.00 $1,500.00 7 Item 2 Total of Item 2 $2,100.00 $2,050.00 $4,150.00 8 Sub-item 2.1 $50.00 $500.00 $550.00 9 Sub-item 2.2 $1,000.00 $500.00 $1,500.00 10 Sub-item 2.3 $50.00 $1,000.00 $1,050.00 11 Sub-item 2.4 $1,000.00 $50.00 $1,050.00 398 Grand Total I like to put this in a Pivot Table. Is it possible such that I can have sub-totals. (e.g. all sub-items with same suffix (i.e. 1.1, 2.1, 3.1,€¦and so forth) ) ? Also to pivot it based on a very nice printable - Table type and Chart type €“ brief to complex presentation. Im just new with this dbase type of so-so presentation by excel. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
anyone pls. help to pivot ???
Driller,
You would need to add a column of formulas to extract the suffix values, and then base your row or column of the pivot table on that column. HTH, Bernie MS Excel MVP "driller" wrote in message ... I have 299 main items with 4 sub-items each. See below one of my primitive table established. REF Col. A B C D E Row 1 M. ITEM DESC CAT. 1 CAT. 2 G.TOTAL 2 Item 1 Total of Item 1 $3,000.00 $4,000.00 $7,000.00 3 Sub-item 1.1 $1,000.00 $1,500.00 $2,500.00 4 Sub-item 1.2 $500.00 $1,000.00 $1,500.00 5 Sub-item 1.3 $1,000.00 $500.00 $1,500.00 6 Sub-item 1.4 $500.00 $1,000.00 $1,500.00 7 Item 2 Total of Item 2 $2,100.00 $2,050.00 $4,150.00 8 Sub-item 2.1 $50.00 $500.00 $550.00 9 Sub-item 2.2 $1,000.00 $500.00 $1,500.00 10 Sub-item 2.3 $50.00 $1,000.00 $1,050.00 11 Sub-item 2.4 $1,000.00 $50.00 $1,050.00 398 Grand Total I like to put this in a Pivot Table. Is it possible such that I can have sub-totals. (e.g. all sub-items with same suffix (i.e. 1.1, 2.1, 3.1,.and so forth) ) ? Also to pivot it based on a very nice printable - Table type and Chart type - brief to complex presentation. I'm just new with this dbase type of so-so presentation by excel. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
anyone pls. help to pivot ???
thanks for the reply...would you pls tell me the proper column arrangement...
"Bernie Deitrick" wrote: Driller, You would need to add a column of formulas to extract the suffix values, and then base your row or column of the pivot table on that column. HTH, Bernie MS Excel MVP "driller" wrote in message ... I have 299 main items with 4 sub-items each. See below one of my primitive table established. REF Col. A B C D E Row 1 M. ITEM DESC CAT. 1 CAT. 2 G.TOTAL 2 Item 1 Total of Item 1 $3,000.00 $4,000.00 $7,000.00 3 Sub-item 1.1 $1,000.00 $1,500.00 $2,500.00 4 Sub-item 1.2 $500.00 $1,000.00 $1,500.00 5 Sub-item 1.3 $1,000.00 $500.00 $1,500.00 6 Sub-item 1.4 $500.00 $1,000.00 $1,500.00 7 Item 2 Total of Item 2 $2,100.00 $2,050.00 $4,150.00 8 Sub-item 2.1 $50.00 $500.00 $550.00 9 Sub-item 2.2 $1,000.00 $500.00 $1,500.00 10 Sub-item 2.3 $50.00 $1,000.00 $1,050.00 11 Sub-item 2.4 $1,000.00 $50.00 $1,050.00 398 Grand Total I like to put this in a Pivot Table. Is it possible such that I can have sub-totals. (e.g. all sub-items with same suffix (i.e. 1.1, 2.1, 3.1,.and so forth) ) ? Also to pivot it based on a very nice printable - Table type and Chart type - brief to complex presentation. I'm just new with this dbase type of so-so presentation by excel. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
anyone pls. help to pivot ???
Driller,
If the value Sub-item 1.1 is in cell A2, then in the first blank column, use the formula =VALUE(MID(A2,FIND(".",A2)+1,255)) and copy down to match your data set. That will return the suffix values from column A. HTH, Bernie MS Excel MVP "driller" wrote in message ... thanks for the reply...would you pls tell me the proper column arrangement... "Bernie Deitrick" wrote: Driller, You would need to add a column of formulas to extract the suffix values, and then base your row or column of the pivot table on that column. HTH, Bernie MS Excel MVP "driller" wrote in message ... I have 299 main items with 4 sub-items each. See below one of my primitive table established. REF Col. A B C D E Row 1 M. ITEM DESC CAT. 1 CAT. 2 G.TOTAL 2 Item 1 Total of Item 1 $3,000.00 $4,000.00 $7,000.00 3 Sub-item 1.1 $1,000.00 $1,500.00 $2,500.00 4 Sub-item 1.2 $500.00 $1,000.00 $1,500.00 5 Sub-item 1.3 $1,000.00 $500.00 $1,500.00 6 Sub-item 1.4 $500.00 $1,000.00 $1,500.00 7 Item 2 Total of Item 2 $2,100.00 $2,050.00 $4,150.00 8 Sub-item 2.1 $50.00 $500.00 $550.00 9 Sub-item 2.2 $1,000.00 $500.00 $1,500.00 10 Sub-item 2.3 $50.00 $1,000.00 $1,050.00 11 Sub-item 2.4 $1,000.00 $50.00 $1,050.00 398 Grand Total I like to put this in a Pivot Table. Is it possible such that I can have sub-totals. (e.g. all sub-items with same suffix (i.e. 1.1, 2.1, 3.1,.and so forth) ) ? Also to pivot it based on a very nice printable - Table type and Chart type - brief to complex presentation. I'm just new with this dbase type of so-so presentation by excel. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
anyone pls. help to pivot ???
Thanks Bernie,...i will get back to you after some run-offs.
regards "Bernie Deitrick" wrote: Driller, If the value Sub-item 1.1 is in cell A2, then in the first blank column, use the formula =VALUE(MID(A2,FIND(".",A2)+1,255)) and copy down to match your data set. That will return the suffix values from column A. HTH, Bernie MS Excel MVP "driller" wrote in message ... thanks for the reply...would you pls tell me the proper column arrangement... "Bernie Deitrick" wrote: Driller, You would need to add a column of formulas to extract the suffix values, and then base your row or column of the pivot table on that column. HTH, Bernie MS Excel MVP "driller" wrote in message ... I have 299 main items with 4 sub-items each. See below one of my primitive table established. REF Col. A B C D E Row 1 M. ITEM DESC CAT. 1 CAT. 2 G.TOTAL 2 Item 1 Total of Item 1 $3,000.00 $4,000.00 $7,000.00 3 Sub-item 1.1 $1,000.00 $1,500.00 $2,500.00 4 Sub-item 1.2 $500.00 $1,000.00 $1,500.00 5 Sub-item 1.3 $1,000.00 $500.00 $1,500.00 6 Sub-item 1.4 $500.00 $1,000.00 $1,500.00 7 Item 2 Total of Item 2 $2,100.00 $2,050.00 $4,150.00 8 Sub-item 2.1 $50.00 $500.00 $550.00 9 Sub-item 2.2 $1,000.00 $500.00 $1,500.00 10 Sub-item 2.3 $50.00 $1,000.00 $1,050.00 11 Sub-item 2.4 $1,000.00 $50.00 $1,050.00 398 Grand Total I like to put this in a Pivot Table. Is it possible such that I can have sub-totals. (e.g. all sub-items with same suffix (i.e. 1.1, 2.1, 3.1,.and so forth) ) ? Also to pivot it based on a very nice printable - Table type and Chart type - brief to complex presentation. I'm just new with this dbase type of so-so presentation by excel. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Display the source for a pivot table page field | Excel Worksheet Functions | |||
| Pivot Table external XLS file source change and GETPIVOTDATA refresh | Excel Discussion (Misc queries) | |||
| Derived Columns in Pivot Table | Excel Discussion (Misc queries) | |||
| pivot table created from another pivot table | Excel Worksheet Functions | |||
| Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Linear Mode
