Microsoft Office Tutorials and References
In Depth Information
Creating a Reverse Pivot Table
Next i
‘ Replace numbers with descriptive text
With Range(“A:A,F:F”)
.Replace “1”, “Strongly Disagree”
.Replace “2”, “Disagree”
.Replace “3”, “Undecided”
.Replace “4”, “Agree”
.Replace “5”, “Strongly Agree”
End With
End Sub
Notice that all these pivot tables were created from a single PivotCache object.
The pivot tables are created within a nested loop. The Col loop counter goes from 1 to 6 by
using the Step parameter. The instructions vary a bit for the second column of pivot tables.
Specifically, the pivot tables in the second column do the following:
h Display the count as a percent of the column.
h Do not show grand totals for the rows.
h Are assigned a number format.
h Display format conditioning data bars.
The Row variable keeps track of the starting row of each pivot table. The final step is to replace the
numeric categories in columns A and F with text. For example, 1 is replaced with Strongly Agree .
Creating a Reverse Pivot Table
A pivot table is a summary of data in a table. But what if you have a summary table, and you’d
like to create a table from it? Figure 17-8 shows an example. Range B2:F14 contains a summary
table — similar to a very simple pivot table. Columns I:K contain a 48-row table created from the
summary table. In the table, each row contains one data point, and the first two columns describe
that data point. In other words, the transformed data is normalized. (See the sidebar, “Data
appropriate for a pivot table,” earlier in this chapter.)
Excel doesn’t provide a way to transform a summary table into a normalized table, so it’s a good
job for a VBA macro. After I created this macro, I spent a bit more time and added a UserForm,
shown in Figure 17-9. The UserForm gets the input and output ranges and also has an option to
convert the output range to a table.
This workbook, named reverse pivot table.xlsm , is available on the companion
Search JabSto ::

Custom Search