A Pivot Table is a useful tool for summarizing data created from Data -> Pivot Tables, and then using some drag and drop techniques.
A B C D E F 1 Name QTY Size Hooded Color Logo 2 Abe 1 S Y Red Team 3 Bob 1 XL N Blue School 4 Chuck 2 L N Blue Team 5 Chuck 2 M Y Red Team 6 Dwain 1 S Y Red Team 7 Evan 1 M N Blue School 8 Frank 2 XXL Y Blue School 9 Fred 2 M Y Red Team 10 George 3 S N Blue School 11 Hal 1 S Y Red Team 12 Harvey 1 XL Y Blue School 13 Jim 2 XXL Y Blue Team 14 Paul 1 M N Blue Team The following is a Text table suitable for use in a newsgroup posting where attachments are not allowed which was created by reworking the data into cells of equal width per column in a selection with a macro. Name QTY Size Hooded Color Logo Abe 1 S Y Red Team Bob 1 XL N Blue School Chuck 2 L N Blue Team Chuck 2 M Y Red Team Dwain 1 S Y Red Team Evan 1 M N Blue School Frank 2 XXL Y Blue School Fred 2 M Y Red Team George 3 S N Blue School Hal 1 S Y Red Team Harvey 1 XL Y Blue School Jim 2 XXL Y Blue Team Paul 1 M N Blue Team
To Create a Pivot Table
Select all of the table i.e. A1:F14
Data, Pivot Tables and .
Create on separate sheet
drop Color, Hooded, and Logo on left side for rows
drop Size into Columns
drop Qty into Data
make sure you Sum of Qty and not Count of Qty
Sum of QTY Size Color Logo Hooded L M S XL XXL Grand Total Blue School N 1 3 1 5 Y 1 2 3 School Total 1 3 2 2 8 Team N 2 1 3 Y 2 2 Team Total 2 1 2 5 Blue Total 2 2 3 2 4 13 Red Team Y 4 3 7 Team Total 4 3 7 Red Total 4 3 7 Grand Total 2 6 6 2 4 20 In the above table there are unwanted subtotals. The subtotals were colored to separate them from the parts that were wanted.
the subtotals can be suppressed after the Pivot Table has been generated using this tip from Ken Wright: right click on each of the subtotals (gray pulldowns), select field settings and change option selected from automatic to none.
Sum of Qty Size Color Logo Hooded L M S XL XXL Grand Total Blue School N 1 3 1 5 Y 1 2 3 Team N 2 1 3 Y 2 2 Red Team Y 4 3 7 Grand Total 2 6 6 2 4 20
To get the sizes in the correct order (S, M, L, XL, XXL), first create a custom order (tools, options, custom lists), then select the area to be sorted left to right (D4:H15), then Data, Sort, Options, Order: S, M, L,..., then Ascending.
Sum of QTY Size Color Hooded Logo S M L XL XXL Grand Total Blue N School 3 1 1 5 Team 1 2 3 Y School 1 2 3 Team 2 2 Red Y Team 3 4 7 Grand Total 6 6 2 2 4 20
Sub Equal_columns()
'David McRitchie, 2004-01-27, create equal width columns
'Reformat the content in a slection so that each cell in the column
' has an equal width. Results can be pasted into a posting
' and should be viewd in a fixed font (View, font, fixed).
Dim c As Long, r As Long, l As Long
Dim kon30 As String
kon30 = " "
For c = 1 To Selection.Columns.Count
l = 0
For r = 1 To Selection.Rows.Count
l = Application.Max(l, Len(Selection(r, c).Text))
Next r
For r = 1 To Selection.Rows.Count
If Selection.Cells(r, c).HorizontalAlignment = -4152 Or _
IsNumeric(Selection.Cells(r, c)) Then
Selection(r, c) = "'" & _
Left(kon30, l - Len(Selection(r, c).Text)) _
& Selection(r, c).Text & " "
Else
Selection(r, c) = "'" & Selection(r, c).Text & _
Left(kon30, l - Len(Selection(r, c).Text)) & " "
End If
Next r
Next c
End Sub
If not familiar with installation and use of macros, see Getting Started with Macros and User Defined Functions
Please send your comments concerning this web page to: David McRitchie Send Email Comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved