Text in Pivot Tables

Discussion in 'Microsoft Excel Discussion' started by Gregc., Jan 10, 2007.

  1. Gregc.

    Gregc. Guest

    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
     
    Gregc., Jan 10, 2007
    #1
    1. Advertisements

  2. 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
     
    Dave Peterson, Jan 10, 2007
    #2
    1. Advertisements

  3. Gregc.

    Jason Lepack Guest

    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
     
    Jason Lepack, Jan 10, 2007
    #3
  4. Gregc.

    Jason Lepack Guest

    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:D").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:D").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: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
     
    Jason Lepack, Jan 10, 2007
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. steveski

    Pivot Tables : Restrict data displayed in pivot table

    steveski, Feb 9, 2004, in forum: Microsoft Excel Discussion
    Replies:
    3
    Views:
    668
    steveski
    Feb 10, 2004
  2. Gio Bacareza

    Difference between basing pivot on excel list or other pivot table

    Gio Bacareza, May 7, 2004, in forum: Microsoft Excel Discussion
    Replies:
    1
    Views:
    362
    Dave Hawley
    May 7, 2004
  3. Pivot table from multiple pivot tables

    , Feb 9, 2006, in forum: Microsoft Excel Discussion
    Replies:
    0
    Views:
    237
  4. Exiled
    Replies:
    1
    Views:
    407
    steven1001
    Aug 13, 2006
  5. Gregc.

    Text in Pivot Tables

    Gregc., Jan 10, 2007, in forum: Microsoft Excel Discussion
    Replies:
    4
    Views:
    172
    Gregc.
    Jan 10, 2007
Loading...

Share This Page