3

I have a PIVOT table report like the one below:

Customer Name   Category  Date Met
-----------------------------------
Customer 1       Core        9
                             10

Customer 2       VIP         2
                             12
                             23

Can I get the following output by any means in pivot table (excel 2007)?

Customer Name   Category   Date Met
Customer 1      Core       9,10
Customer 2      VIP        2, 12, 23
Improve this question
1
  • Your question is not very clear. What exactly are you trying to do? I cleaned up your tables a little bit. You want to take numbers that are adjacent vertically and combine them into one cell separated by a comma? In a pivot table?
    – Raystafarian
    Commented Feb 29, 2012 at 17:47

2 Answers 2

Reset to default
1

This macro will do that without looping using an empty column to the right... for reference, the formula being put into D2 and copied down is:

=IF($C3="", $C2, IF($A3="", $C2&","&$D3, $C2))

Sub ReduceRows()
Dim LR As Long

LR = Range("C" & Rows.Count).End(xlUp).Row

With Range("D2:D" & LR)
    .FormulaR1C1 = "=IF(R[1]C3="""", RC3, IF(R[1]C1="""", RC3&"",""&R[1]C4, RC3))"
    .Value = .Value
    .Copy Range("C2")
    .ClearContents
End With

Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

enter image description here

Improve this answer
0

This VBA will do what you want, but it's not a pivot table

Sub test()
'define variables
Dim RowNum as long, LastRow As long
'turn off screen updating
Application.ScreenUpdating = False
'start below titles and make full selection of data
RowNum = 2
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Range("A2", Cells(LastRow, 3)).Select
'For loop for all rows in selection with cells
For Each Row In Selection
    With Cells
    'if date isn't blank
    If Cells(RowNum, 3) <> "" Then
        'and if customer name blank
        If Cells(RowNum, 1) = "" Then
            'and if category blank
            If Cells(RowNum, 2) = "" Then
                'concatenate and delete empty line
                Cells(RowNum - 1, 3) = Cells(RowNum - 1, 3) & ", " & Cells(RowNum, 3)
                Rows(RowNum).EntireRow.Delete
                'since we deleted a row, go back a row
                RowNum = RowNum - 1
            End If
        End If
    End If
    End With
'increase rownum for next test
RowNum = RowNum + 1
Next Row
'turn on screen updating
Application.ScreenUpdating = True

End Sub

and yes, I did reuse some of my code from this problem

Improve this answer
1
  • 1
    I would recommend the RowNum variable which has no vartype (making it into a Variant by default) and the LastRow variable (which was declared as Integer) both be changed to LONG. Excel 2007 could easily have data well past row 32657, better safe than sorry. Because of this limitation, I never use Integer anymore, Long does the job safely. (and leaving the vartype off defaults to Variant, the most expensive variable type there is.
    – Jerry Beaucaire
    Commented Mar 1, 2012 at 16:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .