Thread: Alternative to Pivot Table

I have a worksheet that has 5 columns of data, all of which are text. I am looking for a way to present/display the data in a manner similar to that of a pivot table. I'm pretty sure an actual pivot table is no good to me since I'm dealing with text, but I'm looking for something that is functionally the same.

In other words, I would love to be able to "pivot" my data and display the different relationships between the different columns. If a pivot table would display text in the "data items" field, that would be perfect.

Anyone have any suggesstions as to how to approach this problem?

TIA

per DonkeyOte's suggestion, I've attached an example file. The desired output is the pivot table, except where you see 1s, 2s, 3s, etc., I would like to actually see the product (rather than the count of). Again, I'm pretty sure a pivot table will not be the answer, but I'm looking for something that will mirror the display/presentation and functionality of a pivot table.

So where you have 1:many relationship in terms of Products to Person:Place combination(s) what is the desired output in the matrix - the products concatenated ?

For ex. for Jim:North there are 3 unique Products comprising the 4 Products in total - Food, Beverage, Dish ... what do you want to display instead of the "4" exactly ?

Originally Posted by papaexcel

Just like you said, the products concatenated would be a good start. Although I'm not certain I would want them all listed in the same cell.

I will take a look at this in depth tomorrow (if not resolved beforehand) but I would ask the question that if not concatenated in one cell then what exactly would you like to happen (optimally) ?

It would be helpful I think if, based on your original upload, you outlined (via manual means) exactly what your desired output should look like.

Thanks

I've had a look at this and came up with the attached, could be streamlined I'm sure but I was a little pressed for time - I intend to revisit so will post back with amendments I think worthwhile.

Basic premise is that the user selects Year (if required) from the Data Validation list on Output sheet (pre-populated via Dynamic Named Range based upon values on Source sheet) ... on selection of year the code is invoked to generate the Pivot-Table esque output as per your "desired results" in the last sample.

Code is shown below for reference (and as I say I'm sure it could be improved -- I tried to avoid iteration as I was not sure how much data you had to process in real life)


Code1: Sheet Object Change Event

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" And Target.Count = 1 Then Call PopulateTable(Target.Value, IsNumeric(Target.Value))
End Sub

Code2: Code to Populate Pivot Table-esque view (invoked by above)

Code:

Option Explicit
Public Sub PopulateTable(vYR As Variant, boolYR As Boolean)
Dim wsSource As Worksheet, wsOutput As Worksheet
Dim xlCalc As XlCalculation
Dim vNames As Variant, vPlaces As Variant, vData As Variant
Dim bList As Byte
Dim lngPlace As Long, lngName As Long, lngMaxProducts As Long
On Error GoTo Handler
With Application
    xlCalc = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With
Set wsSource = Sheets("Source")
Set wsOutput = Sheets("Output")
'Clear Existing Data
With wsOutput
    .Range(.Cells(4, "B"), .Cells(4, .Columns.Count)).ClearContents
    .Range(.Cells(5, "A"), .Cells(.Rows.Count, .Columns.Count)).ClearContents
End With
'Establish Unique Names / Places etc
With wsSource
    For bList = 1 To 2 Step 1
        With .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)).Offset(, Columns.Count - 2)
            .FormulaR1C1 = IIf(boolYR, "=REPT(RC1&"":""&RC" & IIf(bList = 1, 4, 3) & ",RC1=" & vYR & ")", "=RC" & IIf(bList = 1, 4, 3))
            With .Offset(, 1)
                .FormulaR1C1 = "=IF(COUNTIF(R1C[-1]:RC[-1],RC[-1])=1,RC" & IIf(bList = 1, 4, 3) & ",0)"
                .Value = .Value
                .SpecialCells(xlCellTypeConstants, xlNumbers).Delete
                .Sort Key1:=.Cells(1, 1), order1:=xlAscending
                Select Case Application.CountA(.Cells)
                    Case 1
                        vData = .Resize(1).Value
                    Case Is > 1
                        vData = Application.Transpose(.SpecialCells(xlCellTypeConstants, xlTextValues).Value)
                End Select
                If bList = 1 Then vNames = vData Else vPlaces = vData
                .Clear
            End With
            .Clear
        End With
    Next bList
    'List Unique Names
    With wsOutput.Cells(4, "B")
        Select Case VarType(vNames)
            Case 8
                .Value = vNames
            Case Else
                .Resize(, UBound(vNames)).Value = vNames
        End Select
    End With
    'List Unique Places
    With wsOutput.Cells(5, "A")
        Select Case VarType(vPlaces)
            Case 8
                .Value = vPlaces
            Case Else
                .Resize(UBound(vPlaces)).Value = Application.Transpose(vPlaces)
        End Select
    End With
    'Revise Places / Names (so always UBound) & Subsequently Iterate - Applying Filters - Retrieving Data
    With wsOutput
        vPlaces = Application.Transpose(.Range(.Cells(4, "A"), .Cells(.Rows.Count, "A").End(xlUp)).Value)
        vNames = Application.Transpose(.Range(.Cells(4, "A"), .Cells(4, .Columns.Count).End(xlToLeft)).Value)
        .Range(Cells(5, "A"), Cells(.Rows.Count, "A")).ClearContents
        With Range("_Data")
            If boolYR Then .AutoFilter Field:=1, Criteria1:=vYR
            For lngPlace = 2 To UBound(vPlaces) Step 1
                wsOutput.Cells(Rows.Count, "A").End(xlUp).Offset(Application.Max(1, lngMaxProducts)).Value = vPlaces(lngPlace)
                lngMaxProducts = 0
                .AutoFilter Field:=3, Criteria1:=vPlaces(lngPlace)
                For lngName = 2 To UBound(vNames, 1) Step 1
                    .AutoFilter Field:=4, Criteria1:=vNames(lngName, 1)
                    On Error Resume Next
                    With .Offset(1).Columns(5).SpecialCells(xlCellTypeVisible)
                        .Copy Destination:=wsOutput.Cells(wsOutput.Cells(Rows.Count, "A").End(xlUp).Row, lngName)
                        lngMaxProducts = Application.Max(lngMaxProducts, .Cells.Count - 1)
                    End With
                    On Error GoTo Handler
                Next lngName
            Next lngPlace
        End With
    End With
End With

ExitPoint:
Range("_Data").AutoFilter
Set wsSource = Nothing
Set wsOutput = Nothing
With Application
    .Calculation = xlCalc
    .ScreenUpdating = True
    .EnableEvents = True
End With

Exit Sub

Handler:
MsgBox "Error Has Occurred" & vbLf & vbLf & _
        "Error Number: " & Err.Number & vbLf & vbLf & _
        "Error Desc.: " & Err.Description, _
        vbCritical, _
        "Fatal Error"
Resume ExitPoint

End Sub

Thanks. Very nice code. Not quite exactly what I was looking for, but certainly better than what I have.

It does exactly what my example file suggested. The only thing I wish it did was allow me to change the other filters...just like I would be able to in a pivot table. I'm guessing what I want isn't really possible. And probably not even practical.

thanks again though. your code will be put to good use!

You're welcome (I think..?!)

Re: making adaptable -> there's no reason as to why with some thought the code could not be adapted to become more flexible - ie making row/column fields interchangable but to be honest it's a sufficiently large piece of work it would make sense for you to do it as and when you have the time - I'm presuming of course you're getting paid whilst doing this

There will be other approaches but PT's are not trivial beasts if you think about what they do, how they operate and the immense flexibility they offer... I've spent many hours of my life trying to replicate PT functionality on web clients and it ain't at all easy... it's always worth considering why something that you think should be a blatantly obvious piece of functionality for XL to offer does not exist...

Perhaps I'm missing something but simply adding a 2nd row Field to the PT does not in any way generate a text matrix.

Moreover there is no need for the additional "number" column given you can add "Product" as both a Row Field and Data Field simultaneously - setting the latter to COUNT would generate exactly the same output as your version.

Categorically, no.

Use your file, remove the SUM of NUMBER field from your DATA field, drag PRODUCT into DATA Field and it will default to COUNT - you can use the same field more than once.

PapaExcel's desired output is not to have a matrix with data field populated by count of product, rather it is to have the product text strings listed within the data field ie where the number would normally be present: see your attachment G14:M21...

As you do correctly point out XL can not present text strings within the data field bar the odd simple exception where one can use a Custom Format to provide a few alternate text strings - this approach is severely limited and certainly not applicable in this instance.

I appreciate I may be sounding terse but my point is quite a simple one...

Originally Posted by papaexcel

I have a worksheet that has 5 columns of data, all of which are text. I am looking for a way to present/display the data in a manner similar to that of a pivot table. I'm pretty sure an actual pivot table is no good to me since I'm dealing with text, but I'm looking for something that is functionally the same.

In other words, I would love to be able to "pivot" my data and display the different relationships between the different columns. If a pivot table would display text in the "data items" field, that would be perfect.

Nothing that has been posted thus far today bears any relevance to the actual question, ie how to generate a matrix populated entirely with Text.

I made the point that building something that generates PT-esque output with Text in the data field is possible but building in the same level of flexibility as a numeric based PT is non-trivial given the complexities it hand. You then disagreed with this stating that actually by simply adding another row field and an index of 1 all can be resolved which with all due respect is categorically not the case.

Originally Posted by richsoby

As you state - the limitation of text in the data field is a hinderance to what papaexcell is trying to do - and both your and my suggestion will accomplish getting to the info.

So I ask, how is your PT is generating the desired output ?
If I am missing something I apologise profusely but as I see it your PT is simply a numerical output with an additional row label... I can get the data in text form in the same layout how exactly ?

If all you want is a sum of product - then yes you can use the product in the data field (BTW thanks - was not aware you could have the same data in two places).

But if you then want to play with the other data fields as column (say place, code or year as examples here) - under your solution you must remember to replace the data field also.

Agreed, however, given this question is in no way concerned with generating a numerical output this is totally irrelevant.

As I have implemented OLAP solutions....

Yes, remarkably I have experience of OLAP too (SAP) but given we're not talking about OLAP Cubes nor even numerical output I fail to see the significance / relevance of this point.