anyone pls. help to pivot ???

[driller]

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.

 

[Bernie Deitrick]

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]

thanks for the reply...would you pls tell me the proper column arrangement...

 

[Bernie Deitrick]

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]

Thanks Bernie,...i will get back to you after some run-offs.
regards