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.

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.

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.

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.

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.