PC Review


Reply
Thread Tools Rate Thread

Text in Pivot Tables

 
 
Gregc.
Guest
Posts: n/a
 
      10th Jan 2007
Hi

When doing a Pivot Table is it possible to have text instead of numbers
in the table? If so, how can this be done.

Greg

 
Reply With Quote
 
 
 
 
Dave Peterson
Guest
Posts: n/a
 
      10th Jan 2007
Still no.

"Gregc." wrote:
>
> Hi
>
> When doing a Pivot Table is it possible to have text instead of numbers
> in the table? If so, how can this be done.
>
> Greg


--

Dave Peterson
 
Reply With Quote
 
 
 
 
Jason Lepack
Guest
Posts: n/a
 
      10th Jan 2007
I can, but I created code that creates my pivot tables. It's at work
though, so you'll have to wait until I have some free time this week.

Cheers,
Jason Lepack

Dave Peterson wrote:
> Still no.
>
> "Gregc." wrote:
> >
> > Hi
> >
> > When doing a Pivot Table is it possible to have text instead of numbers
> > in the table? If so, how can this be done.
> >
> > Greg

>
> --
>
> Dave Peterson


 
Reply With Quote
 
Jason Lepack
Guest
Posts: n/a
 
      10th Jan 2007
Ah hah!

One of my spreadsheets here at home, had this macro, so you're in luck!
I suggest backing up eveything before testing this code in any shape,
way, or form.

Public Sub textPivotTable(colRng As String, rowRng As String, dataRng
As String, dataSht As String, pivotCell As String, pivotSht As String)
' Feel free to modify this as you wish.

' this sub will create a new pivot table at the cell listed in pivot
range
' the caveat is that it expects a one-to-one-to-one relationship of the
data.
' for each (col,row) there must only be one data or else it won't get
the right result.

' colRng = the range that the data to be used a column is in
' rowRng = the range that the data to be used a row is in
' dataRng = the range that the data to be used a data is in
' dataSth = the name of the worksheet with the data
' pivotCell = the cell that is the top left corner of the pivot table
' pivotSht = the worksheet where the pivot table will be

' Example:

Dim wb As Workbook
Dim wsData As Worksheet, wsPivot As Worksheet, wsTemp As Worksheet
Dim rTemp As Range, rPivot As Range, rRow As Range, rCol As Range

Set wb = ActiveWorkbook
Set wsData = wb.Sheets(dataSht)
Set wsPivot = wb.Sheets(pivotSht)
Set wsTemp = wb.Sheets.Add

wsData.Range(colRng).Copy
wsTemp.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
wsData.Range(rowRng).Copy
wsTemp.Range("B1").PasteSpecial xlPasteValuesAndNumberFormats
wsData.Range(dataRng).Copy
wsTemp.Range("C1").PasteSpecial xlPasteValuesAndNumberFormats

' sort the data for the column headers
wsTemp.Columns("A").Sort Key1:=wsTemp.Range("A1"),
Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal

' create the column headers
Set rTemp = wsTemp.Range("A1")
Set rPivot = wsPivot.Range(pivotCell)
Do While Not rTemp.Value = ""
If Not rTemp.Value = rPivot.Value Then
Set rPivot = rPivot.Offset(0, 1)
rPivot.Value = rTemp.Value
End If
' create a temporary column for searching later
rTemp.Offset(0, 3).Value = rTemp.Value & "|" & rTemp.Offset(0,
1).Value
Set rTemp = rTemp.Offset(1, 0)
Loop

' sort the data for the row headers
wsTemp.Columns("A").Sort Key1:=wsTemp.Range("B1"),
Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal

' create row headers
Set rTemp = wsTemp.Range("B1")
Set rPivot = wsPivot.Range(pivotCell)
Do While Not rTemp.Value = ""
If Not rTemp.Value = rPivot.Value Then
Set rPivot = rPivot.Offset(1, 0)
rPivot.Value = rTemp.Value
End If
Set rTemp = rTemp.Offset(1, 0)
Loop

' fill in the table
Set rCol = wsPivot.Range(pivotCell).Offset(0, 1)
Do While Not rCol.Value = ""
Set rRow = wsPivot.Range(pivotCell).Offset(1, 0)
Do While Not rRow.Value = ""
Set rPivot = Intersect(rRow.EntireRow, rCol.EntireColumn)
Set rTemp = wsTemp.Range("D").Find(rCol.Value & "|" &
rRow.Value)
If Not rTemp Is Nothing Then rPivot.Value = rTemp.Offset(0,
-2).Value
Set rRow = rRow.Offset(1, 0)
Loop
Set rCol = rCol.Offset(0, 1)
Loop

Application.DisplayAlerts = False
wsTemp.Delete
Application.DisplayAlerts = True
End Sub

Jason Lepack wrote:
> I can, but I created code that creates my pivot tables. It's at work
> though, so you'll have to wait until I have some free time this week.
>
> Cheers,
> Jason Lepack
>
> Dave Peterson wrote:
> > Still no.
> >
> > "Gregc." wrote:
> > >
> > > Hi
> > >
> > > When doing a Pivot Table is it possible to have text instead of numbers
> > > in the table? If so, how can this be done.
> > >
> > > Greg

> >
> > --
> >
> > Dave Peterson


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Building pivot tables in Excel 2007 based on existing pivot tables? jj.jigga.johns@gmail.com Microsoft Excel Misc 4 26th Dec 2007 09:05 PM
(Tom?) Pivot tables, code to refer to all pivot tables on template =?Utf-8?B?a2x5c2VsbA==?= Microsoft Excel Programming 0 20th Jul 2007 09:32 PM
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. =?Utf-8?B?c3R2ZXJtb250?= Microsoft Excel Misc 1 17th Feb 2005 02:34 AM
Pivot tables on pivot tables! tomo999 Microsoft Excel Misc 1 15th Jan 2004 11:21 AM
Pivot tables-controlling user interaction with pivot tables Sindhura Microsoft Excel Programming 0 27th Aug 2003 02:10 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:52 AM.