Combine like fields in PivotTable and combine not-like fields into one field separated by commas

[PurplGirl]

Hello
(Windows7 / Excel 2013)
I have a PivotTable we have created that is used to narrow down information, this is the current table that we use.
As you can see there are multiple occurrences of the same 'Item Code', variables being the 'Spec' and the 'Qty' field.
Does anyone know how I can get Excel to combine the 'Spec' into one column separated by commas, while combining the 'Qty' of the like rows?
I have tried a number of various 'IF' formulas and macros but I cannot seem to get the result I am looking for. (There is another post with a similar title and the exact same issue, but there is not response from April2015)

[table="width: 500, class: grid, align: left"]
[tr]
[td][/td]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[/tr]
[tr]
[td]1[/td]
[td]Item Code[/td]
[td]Description[/td]
[td]Spec[/td]
[td]Qty[/td]
[/tr]
[tr]
[td]2[/td]
[td]BR102[/td]
[td]Ball Valve 2"[/td]
[td]BR-1A[/td]
[td]8[/td]
[/tr]
[tr]
[td]3[/td]
[td]BR102[/td]
[td]Ball Valve 2"[/td]
[td]BR-2A[/td]
[td]3[/td]
[/tr]
[tr]
[td]4[/td]
[td]BR103[/td]
[td]Ball Valve 3"[/td]
[td]BR-1A[/td]
[td]2[/td]
[/tr]
[tr]
[td]5[/td]
[td]BR103[/td]
[td]Ball Valve 3"[/td]
[td]BR-3A[/td]
[td]1[/td]
[/tr]
[tr]
[td]6[/td]
[td]BR104[/td]
[td]Ball Valve 4"[/td]
[td]BR-2A[/td]
[td]2[/td]
[/tr]
[tr]
[td]7[/td]
[td]BR104[/td]
[td]Ball Valve 4"[/td]
[td]BR-4A[/td]
[td]3[/td]
[/tr]
[tr]
[td]8[/td]
[td]BR105[/td]
[td]Ball Valve 5"[/td]
[td]BR-1A[/td]
[td]36[/td]
[/tr]
[tr]
[td]9[/td]
[td]BR105[/td]
[td]Ball Valve 5"[/td]
[td]BR-2A[/td]
[td]1[/td]
[/tr]
[tr]
[td]10[/td]
[td]BR105[/td]
[td]Ball Valve 5"[/td]
[td]BR-3A[/td]
[td]1[/td]
[/tr]
[tr]
[td]11[/td]
[td]BR106[/td]
[td]Ball Valve 6"[/td]
[td]BR-3A[/td]
[td]15[/td]
[/tr]
[/table]


This is an example of what I am trying to achieve

[table="width: 500, class: grid, align: left"]
[tr]
[td][/td]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[/tr]
[tr]
[td]1[/td]
[td]Item Code[/td]
[td]Description[/td]
[td]Spec[/td]
[td]Qty[/td]
[/tr]
[tr]
[td]2[/td]
[td]BR102[/td]
[td]Ball Valve 2"[/td]
[td]BR-1A, BR-2A[/td]
[td]11[/td]
[/tr]
[tr]
[td]3[/td]
[td]BR103[/td]
[td]Ball Valve 3"[/td]
[td]BR-1A, BR-3A[/td]
[td]3[/td]
[/tr]
[tr]
[td]4[/td]
[td]BR104[/td]
[td]Ball Valve 4"[/td]
[td]BR-2A, BR-4A[/td]
[td]5[/td]
[/tr]
[tr]
[td]5[/td]
[td]BR105[/td]
[td]Ball Valve 5"[/td]
[td]BR-1A, BR-2A, BR-3A[/td]
[td]38[/td]
[/tr]
[tr]
[td]6[/td]
[td]BR106[/td]
[td]Ball Valve 6"[/td]
[td]BR-3A[/td]
[td]15[/td]
[/tr]
[/table]

Thank you for help!!
PurplGirl

 

[PurplGirl]

Does anyone have any thoughts on this issue?
Is it too easy? Or... am I asking too much of Excel?

 

[PurplGirl]

Just another bump!
Any takers? Even if it's just to tell me it is not possible?

 

[XLfriend777]

Hi PurplGirl - Welcome to the forum. What you are trying to do is certainly possible. The code below is a start. It looks at your table and combines the results as you desire. The next step would be to remove the empty rows, but I have not done that yet. Take a look and let me know if you have any questions.

Sub PurplGirl()
Dim item As String
Dim desc As String
Dim spec As String
Dim qty As Integer
i = 3

'Set Initial Values
item = Cells(i, 2).Value
desc = Cells(i, 3).Value
spec = Cells(i, 4).Value
qty = Cells(i, 5).Value

While Cells(i, 2).Value <> ""

Cells(i, 2).Select 'Set active cell
'Print results in Column H-J if the next Item Code is different
    If Cells(i, 2).Value <> ActiveCell.Offset(1, 0).Value Then
        Cells(i, 7).Value = item
        item = Cells(i + 1, 2).Value
        Cells(i, 8).Value = desc
        desc = Cells(i + 1, 3).Value
        Cells(i, 9).Value = spec
        spec = Cells(i + 1, 4).Value
        Cells(i, 10).Value = qty
        qty = Cells(i + 1, 5).Value
    
    Else 'If Item Code is the same, combine results for Spec and Qty
        item = Cells(i, 2).Value
        desc = Cells(i, 3).Value
        spec = spec & ", " & Cells(i + 1, 4).Value
        qty = qty + Cells(i + 1, 5).Value
    
    End If

i = i + 1
Wend
End Sub

 

[PurplGirl]

Good afternoon goesr!
I hope Im doing this right... I have put the code you sent into a macro that I ran on the pivot table, I got some strange results. Im not overly familiar with the code you have written here and I am not able to modify it myself without repeated errors.
Is that the steps you were expecting me to take, or should I try a different method to get a different result?

This is a screen shot of what I got from running the macro, it does look like it is trying...
I need column 'H' to identify all the specs relating to that ball valve (i.e for item BR102 - show as 'BR-1A, BR-2A') and I need the qty column to actually sum up the qtys not be separated by a comma.

I really appreciate your help with this!!

https://app.box.com/s/c3upuzzyb64v7hgo4jo78i1medobqj7c

 

[XLfriend777]

Hi PurplGirl - Glad to help in any way I can. Try just putting your sample data shown in a worksheet and then running the macro against that data. The macro assumes the data is just like you have it shown with Cell A1 containing "Item Code" and Cell D11 containing "15". I'm sure with some testing we can make this work. All the best

 

[XLfriend777]

Hi PurplGirl - I was not able to access your screenshot so I couldn't see what you got. You might try just putting your sample data A1 to D11 into a .xlsm worksheet and then running the macro I created. You should then be able to see what the macro is doing, especially if you run the macro one line at a time using the F8 key. Hope this helps.

 

[PurplGirl]

This worked wonderfully! Thank you for your help!

 

[XLfriend777]

Hi PurplGirl - I'm so glad this worked and thanks so much for the feedback. Very often we pose solutions and never hear what happened. It is great to get some positive feedback. Have a Merry Christmas!