Compressing and transposing pivot data

[gmlopez275]

I would like to re-arrange my pivot table into a more compressed state (NOT compact layout). I have a table with row/column headers. Notice in the attached workbook, there is no data in values area - for purposes of this post, I don't need the sum/count/avg/etc values, I need the actual text in the rows:
Is there any way to pull from the pivot table or source data with VBA or a formula? If there is, I can then apply the code/formula to many other workbooks with variable number of Serial and category combinations.

Using a pivot table creates a vertically long table. I need a more compressed table, such as what I have in the attached workbook. A standard PT consumes too much real estate when inserted into a Word document.

I thought about getpivotdata, vlookup, if(isblank...)... all to no avail. Seems like it should be a simple formula. I just don't know what it is. The table conveys the general structure of the data. The ideal solution will concatenate the categories when there are multiple values associated with a single Serial. Also, I would like to account for Serials of variable length (A-C, A-F, or A-Z, etc).

 

[NARAYANK991]

Hi ,

Given that the serial items can repeat a variable number of times in any category , I doubt that a formula-based solution is possible ; even if it is possible , it may work for a particular set of data ; the moment you change the number of serial items or the number of categories or the number of repetitions of a serial within a category , the formula will need to be changed.

The only solution that will work under all circumstances would be a VBA-based solution. Is this acceptable ?

Narayan

 

[gmlopez275]

Yes. In actuality, I suspected VBA would be the key.

 

[NARAYANK991]

Hi ,

Since it is late tonight , I will get back on this tomorrow.

Narayan

 

[p45cal]

In the attached a user defined worksheet function approach requiring only the raw data in coluns A:B of the Data sheet.
You will have to create your own unique list using Advanced Filter of unique Serials to another location as I have in the vicinity of cell N7 of the Pivot Data sheet.
Formula in cell O7 copied down and across.

 

[gmlopez275]

Very nice. Elegant solution.

I adjusted the code a bit:
Function join(myArray, ser, xyz) As String
Dim result As String
vals = myArray.Value
serval = ser.Value
xyzval = xyz.Value
For i = 1 To UBound(vals)
If vals(i, 1) = serval And InStr(vals(i, 2), xyzval) > 0 Then
If result = "" Then
result = vals(i, 2)
Else
result = result & ", " & vals(i, 2)
End If
End If
Next i
join = result
End Function


I did not need the lowercase values. So I adjusted to suit more generic values. I could not have done this without your help.

 

[p45cal]

actually, the instances of lcase were to make it more generic - it doesn't matter whether the values on the sheet are upper or lower case, it will find them all.

 

[gmlopez275]

My code skills are barely basic.
I tried to modify the code to suit my needs at work. I suppose I need to be more detailed with the actual data structure.
Instead of applying the filter to an X, Y, or Z, how do I adjust the code to fit a string as in the updated spreadsheet (attached) with
111*, 122*, 234* categories?

 

[p45cal]

I'm not very clear.
1. Could you confirm your raw data is just 2 columns, like columns A and C on sheet Data in your most recent file?
If not could you supply only the raw data, without calculations on same.

2. You want to categorise them into the first 3 characters of column C (on the Data sheet).

 

[gmlopez275]

Updated small portion of code in the middle. This now works perfectly!

serval = ser.Value
xyzval = Cstr(xyz.Value)
For i = 1 To UBound(vals)
If vals(i, 1) = serval And left(vals(i, 2), len(xyzval)) = xyzval) Then
If Len(result) = 0 Then
result = vals(i, 2)


Thanks for all of the help