pivot tables

[baggarwal]

Hi All:

I have a dilemna I need your guidance on. When I use Excel XP for pivot tables I get the option of "Add to" when I click finish. This Add to function allows me to add to columns and rows.

The only problem is when I try to create pivot tables in Excel 2000 it doesn't have that feature.

How can I add this feature to Excel 2000, or is there another way of adding to rows and columns using some menu.

Thanks in advance for your help,

BA

 

[Mark W.]

I'm not familiar with XL2002.

 

[baggarwal]

Does anyone out there know how I can achieve the result I am looking for?

 

[baggarwal]

Could it just be that I need an add-in to Excel 2000 to enable the feature in Excel 2002?

 

[attc]

baggarwal- quick clarification question. how is your source data formatted?

anthony

ps. what I mean is, what is the structure of your source data list.
This message was edited by attc on 2002-10-26 12:55

 

[baggarwal]

Hi Anthony:

Here is how the source data is structured:

SAP_Download_coding2.xls
	A	B	C	D	E	F	G	H	I
5	Order	CostElement	Description	Budget/Actual	Apr	May	Jun	Jul	Aug
6	700027	5133000	COS:contractclean	Budget	270	270	270	1670	270
7	700027	5133000	COS:contractclean	Actual	1510	0	417	417	1217
8	700027	5133300	COS:kaizenimpleme	Budget	0	0	0	0	0
9	700027	5133300	COS:kaizenimpleme	Actual	417	0	0	0	0
10	700028	5133000	COS:contractclean	Budget	540	540	540	540	540
11	700028	5133000	COS:contractclean	Actual	0	0	417	417	417
12	700028	5133300	COS:kaizenimpleme	Budget	0	0	0	0	0
13	700028	5133300	COS:kaizenimpleme	Actual	417	0	0	0	0
DesiredResult

Thanks again for your help,

BA

 

[baggarwal]

Is there another feature other than pivot tables that would allow me to achieve what I am looking for?

 

[attc]

hi Baggarwal,

Yeh, the problem you are having is partly because of the structure of the data. (Not that the structure is bad, it's just difficult for the pivot table to interpret correctly. Seems they corrected this in Excel XP).

One way to overcome this is to convert the data (either manually or via macro) into the format:

Col A: Order
Col B: Cost Element
Col C: Description
Col D: Budget / Actual
Col E: Month
Col F: Amount

When you have in this format, the pivot table will work beautifully.

I'm not sure of any other way?

 

[baggarwal]

I am not very familiar with VBA.

Is there anyone out there that can show me how to build a macro that will take data in multiple columns (as above) and put it into one column.

Thanks again for all of your help,

Bharat

 

[attc]

This is a very similar problem to one I've had before, so rather than try to explain, I've modified my existing code for you. Try this out:


Sub JustDoIt()
'

Cells.Copy

Set outputW = Workbooks.Add
Selection.PasteSpecial xlValues

Set dataS = ActiveSheet

Range("A1:D1").Copy

Set outputS = Worksheets.Add
Selection.PasteSpecial xlValues
Range("E1").Value = "Month"
Range("F1").Value = "Amoutn"

Range("A2").Select

dataS.Select

Set basedata = Range(Range("A2"), Range("D65536").End(xlUp))

Let RecordCount = basedata.Rows.Count
Let monthscount = Range(Range("E1"), Range("E1").End(xlToRight)).Columns.Count

For A = 1 To monthscount

dataS.Select
Let M = Cells(1, A + 4).Value

basedata.Select
Selection.Copy

outputS.Select

Cells((A - 1) * RecordCount + 2, 1).Select

Selection.PasteSpecial xlValues
Selection.Offset(0, 4).Resize(, 1).Select
Selection.FormulaR1C1 = M

Selection.Offset(0, 1).Select
basedata.Offset(0, A + 3).Resize(, 1).Copy Selection

Next

End Sub


Assumptions and Caveats:
(i) probably not the most elegant code! but it works for me and fast enough

(ii) the source data is structured:
col A: Order
col B: Cost Element
col C: Description
col D: Budget / Actual
col E onwards: different months

data is contiguous

(iii) the macro is run from the source data sheet as it just starts copying immediately
This message was edited by attc on 2002-10-27 08:39

 

[baggarwal]

Thanks so much Anthony for all of your help,

BA