Pivot Table - Easy way to add multiple Columns into Data section?

  • Thread starter Thread starter Johnny_99
  • Start date Start date
J

Johnny_99

As with a previous question, I have a large set of data (20,000 rows and
about 100 coulmns). I wish to select about 60 columns to place in pivot
"data" (along with others into "Rows").

Is there a way to select multiple columns (say all 60?) and move into "Data"
in one step? Are there add-ins that help with this? Doing all 60 is possible
but slow and somewhat error prone.

Thanks in advance.
 
Hi Johnny

The following code should get you started on what you want to do.
In this example the first column of source data is added to Page area, the
second column is added to the Row area then there is a loop to add 60
columns to the Data area, ensuring that each is set to Sum and getting rid
of the annoying "Sum of " which has to appear before each field name, by
appending a space to the original Field name for use in the PT.

Sub CreatePivot()
Dim wss As Worksheet, wsd As Worksheet
Dim i As Long, j As Long, fname As String

Set wss = Sheets("Sheet1")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
wss.Range("A1:CZ20000")).CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1",
DefaultVersion:=xlPivotTableVersion10
Set wsd = ActiveSheet
wsd.PivotTableWizard TableDestination:=wsd.Cells(3, 1)

wsd.Cells(3, 1).Select
' in this case the first 2 columns of source data have been added to
' Row field and Page field respectively
wsd.PivotTables("PivotTable1").AddFields RowFields:=Array("Date", _
"Data"),
PageFields:="Name"

j = 2 ' set the start column as 1 less than where you wish to pick
' up data fields from
For i = 1 To 60
' loop for 60 columns to add fields to the data area
' picking up the field name from the column header
fname = wss.Cells(1, j + i).Value
With wsd.PivotTables("PivotTable1").PivotFields(fname)
.Orientation = xlDataField
.Function = xlSum ' force a Sum
.Name = fname & " " ' get rid of Sum of before field
name
' by appending a space
to the source field Name
.Position = i
End With
Next

' next part allocates the 60 data fields across columns instead
' of appearing under each other (if that is what is required)

With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


--
Regards
Roger Govier

Johnny_99 said:
As with a previous question, I have a large set of data (20,000 rows and
about 100 coulmns). I wish to select about 60 columns to place in pivot
"data" (along with others into "Rows").

Is there a way to select multiple columns (say all 60?) and move into
"Data"
in one step? Are there add-ins that help with this? Doing all 60 is
possible
but slow and somewhat error prone.

Thanks in advance.

__________ Information from ESET Smart Security, version of virus
signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Thanks Roger.

I presume this is not a nonprogramming solution here? I'd love to simply
select column 1, ctrl, select column n (and take the range of columns) and
add them all ... perhaps asking too much?

No prcedure or add-in solution?

Thanks,
 
Sorry, no such feature available.
Code is the only way to automate the procedure

--
Regards
Roger Govier

Johnny_99 said:
Thanks Roger.

I presume this is not a nonprogramming solution here? I'd love to simply
select column 1, ctrl, select column n (and take the range of columns) and
add them all ... perhaps asking too much?

No prcedure or add-in solution?

Thanks,



__________ Information from ESET Smart Security, version of virus
signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Is the code VBA?
It does not seem to compile in VBA. The := outside the scope of a
paramter appears to fail
e.g. using TableDestination:="" as an assignment.
Sorry is this is a silly question. Tom
 
Hi Tom

Yes the code is written in VBA
I suspect that line wrap in your Newsreader is causing the problem.

I have reproduced the code below, with more forced line breaks so hopefully
your newsreader won't mess it up.

Sub CreatePivot()
Dim wss As Worksheet, wsd As Worksheet
Dim i As Long, j As Long, fname As String

Set wss = Sheets("Sheet1")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=wss.Range("A1:CZ20000")) _
.CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

Set wsd = ActiveSheet
wsd.PivotTableWizard TableDestination:=wsd.Cells(3, 1)

wsd.Cells(3, 1).Select
' in this case the first 2 columns of source data have been added to
' Row field and Page field respectively
wsd.PivotTables("PivotTable1") _
.AddFields RowFields:=Array("Date", _
"Data"), PageFields:="Name"

j = 2 ' set the start column as 1 less than where you wish to pick
' up data fields from
For i = 1 To 60
' loop for 60 columns to add fields to the data area
' picking up the field name from the column header
fname = wss.Cells(1, j + i).Value
With wsd.PivotTables("PivotTable1").PivotFields(fname)
.Orientation = xlDataField
.Function = xlSum ' force a Sum
' get rid of Sum of before field name
' by adding a space to the source field Name
.Name = fname & " "
.Position = i
End With
Next

' next part allocates the 60 data fields across columns instead
' of appearing under each other (if that is what is required)

With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


--
Regards
Roger Govier

tlee said:
Is the code VBA?
It does not seem to compile in VBA. The := outside the scope of a
paramter appears to fail
e.g. using TableDestination:="" as an assignment.
Sorry is this is a silly question. Tom




__________ Information from ESET Smart Security, version of virus
signature database 4821 (20100130) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4821 (20100130) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Hi Roger,

I was facing the problem of getting more than one column data in data field area in pivot table from the data available in excel sheet.

On searching the net, I found your suggestion and tried it (bothe the codes) by copying the code in the view code option in excel file.

Then created the pivot table. But failed to get more than one column data in to data fields.

I may be wrong in the mothod of using the code.

May I get some guidance. Some data is given below for reference.

BRNAME REGNM data1 data2 data3 data4 data5
Br1 Reg1 51 3184 110 95 26
Br2 Reg1 66 3183 220 101 33
Br3 Reg1 81 3182 330 107 40
Br4 Reg2 96 3181 440 113 47
Br5 Reg2 111 3180 550 119 54
Br6 Reg4 126 3179 660 125 61
Br7 Reg6 141 3178 770 131 68
Br8 Reg7 156 3177 880 137 75
Br9 Reg5 171 3176 990 143 82
Br10 Reg8 186 3175 1100 149 89
Br11 Reg9 201 3174 1210 155 96
Br12 Reg10 216 3173 1320 161 103
Br13 Reg11 231 3172 1430 167 110
Br14 Reg3 246 3171 1540 173 117
Br15 Reg3 261 3170 1650 179 124
Br16 Reg6 276 3169 1760 185 131
Br17 Reg5 291 3168 1870 191 138
Br18 Reg4 306 3167 1980 197 145
Br19 Reg12 321 3166 2090 203 152
Br20 Reg3 336 3165 2200 209 159

If one excel file with the solution / code and method along with this data is provided, it will make me understand it esily.
 
Back
Top