How to convert a Pivot Table into a Flat File format?

lucas.freed@gmail.com · 02-22-2006, 12:35 PM

I am an Excel 2003 user and have a pivot table just the way I want it.
I would like to take that and convert it into a flat file format
(removing all the row totals, etc).

Is that possible?

I see in the formatting section that all you can do is make it look
'pretty'...

What I am trying to do is take the pivot output, convert to a flat file
to insert into another dataset that I will pivoting from.

Debra Dalgleish · 02-22-2006, 01:20 PM

You can turn off the totals, then copy the pivot table, and paste it as
values on another worksheet. There, fill in the blanks, using a
technique shown here:

http://www.contextures.com/xlDataEntry02.html

To turn off the row or column totals, right-click on the pivot table,
and choose Table Options.
Remove the check marks from Grand totals for columns and Grand totals
for rows.

lucas.freed@gmail.com wrote:
> I am an Excel 2003 user and have a pivot table just the way I want it.
> I would like to take that and convert it into a flat file format
> (removing all the row totals, etc).
>
> Is that possible?
>
> I see in the formatting section that all you can do is make it look
> 'pretty'...
>
> What I am trying to do is take the pivot output, convert to a flat file
> to insert into another dataset that I will pivoting from.
>

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

**StraightOuttaCompton** · 11-02-2012, 06:26 PM

Here's some code to take any size basic 2 dimensional cross table and convert it into a list / flat table.

Sub CrossTabToList()
 
Dim wsCrossTab As Worksheet
 Dim wsList As Worksheet
 Dim iLastCol As Long
 Dim iLastRow As Long
 Dim iLastRowList As Long
 Dim rngCTab As Range ‘Used for range in Sheet1 cross tab sheet
 Dim rngList As Range ‘Destination range for the list
 Dim I As Long
 
Set wsCrossTab = Worksheets(“Sheet1″)
 Set wsList = Worksheets.Add
 
‘Find the last row in Sheet1 with the cross tab
 iLastRow = wsCrossTab.Cells(Rows.Count, “A”).End(xlUp).Row
 
‘Set the initial value for the row in the destination worksheet
 iLastRowList = 2
 
‘Find the last column in Sheet1 with the cross tab
 iLastCol = wsCrossTab.Range(“A1″).End(xlToRight).Column
 
‘Create a new sheet and set the heading titles
 wsList.Range(“A1:C1″) = Array(“NAME”, “GRADE”, “VALUE”)
 
‘Start looping through the cross tab data
 
For I = 2 To iLastRow
 
Set rngCTab = wsCrossTab.Range(“A” & I) ‘initial value A2
 Set rngList = wsList.Range(“A” & iLastRowList) ‘initial value A2
 
‘Copy individual names in Col A (A2 initially) into as many rows as there are data columns
 ‘in the cross tab (less 1 for Col A). 
rngCTab.Copy rngList.Resize(iLastCol – 1)
 
‘Move up a I rows less one and across one column (using offset function) to select heading row. Copy. 
rngCTab.Offset(-(I – 1), 1).Resize(, iLastCol – 1).Copy
 
‘Paste transpose to columns in the list sheet alongside the names
 rngList.Offset(0, 1).PasteSpecial Transpose:=True
 
‘Staying on same row (2 initially) copy the data from the cross tab
 rngCTab.Offset(, 1).Resize(, iLastCol – 1).Copy
 
‘Past transpose as column in list sheet
 rngList.Offset(0, 2).PasteSpecial Transpose:=True
 
‘Set the new last row in list sheet to be just below the last name copied
 iLastRowList = iLastRowList + (iLastCol – 1)
 
‘increment I by 1
 Next I
 
End Sub

Mod's Note: code tags added for you - this time

**FDibbins** · 11-02-2012, 06:45 PM

@ straight...

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here