If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
create pivot table from excel table
I have a table of quarterly shipments by vendor/product and want to convert
this to a pivot table. Right now I have 12 quaters of data, with includes 8 quaters of actual data and 4 of estimates. If I get how pivot tables work, I would need to add 12 rows for each vendor-product combination and have one column that includes "data." Is there an automated way to convert an excel table, where data is in multiple columns, into a pivot table? And then update each quarter. Please tell me if I am going about this the wrong way. I have only used, never set up, pivot tables, Ellen |
#2
|
|||
|
|||
create pivot table from excel table
Ellen,
Select one cell in your cross-tab data table, and run the macro below. It will create a new sheet with a database table suitable for use with pivot tables. HTH, Bernie MS Excel MVP Sub MakeTable2() Dim myCell As Range Dim newSheet As Worksheet Dim mySheet As Worksheet Dim i As Long Dim j As Integer Dim k As Long Dim l As Integer Dim mySelection As Range Dim RowFields As Integer Set mySheet = ActiveSheet Set mySelection = ActiveCell.CurrentRegion RowFields = Application.InputBox( _ "How many left-most columns to treat as row fields?", _ "CrossTab to DataBase Helper", 1, , , , , 1) On Error Resume Next Application.DisplayAlerts = False Worksheets("New Database").Delete Application.DisplayAlerts = True Set newSheet = Worksheets.Add newSheet.Name = "New Database" mySheet.Activate i = 1 For j = mySelection(1).Row + 1 To _ mySelection(mySelection.Cells.Count).Row For k = mySelection(1).Column + RowFields To _ mySelection(mySelection.Cells.Count).Column If mySheet.Cells(j, k).Value "" Then For l = 1 To RowFields newSheet.Cells(i, l).Value = _ Cells(j, mySelection(l).Column).Value Next l newSheet.Cells(i, RowFields + 1).Value = _ Cells(mySelection(1).Row, k).Value newSheet.Cells(i, RowFields + 2).Value = _ Cells(j, k).Value i = i + 1 End If Next k Next j End Sub "Pivot Table Creation Help" Pivot Table Creation wrote in message ... I have a table of quarterly shipments by vendor/product and want to convert this to a pivot table. Right now I have 12 quaters of data, with includes 8 quaters of actual data and 4 of estimates. If I get how pivot tables work, I would need to add 12 rows for each vendor-product combination and have one column that includes "data." Is there an automated way to convert an excel table, where data is in multiple columns, into a pivot table? And then update each quarter. Please tell me if I am going about this the wrong way. I have only used, never set up, pivot tables, Ellen |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
pivot table | rmsterling | General Discussion | 5 | November 14th, 2005 04:40 PM |
Add New Field to DB | Karen | Database Design | 7 | October 19th, 2005 08:03 PM |
Multiple Options Group | Patty Stoddard | Using Forms | 19 | August 4th, 2005 02:30 PM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |