Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question
    
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 Feb 29 '12 at 17:47
add comment

2 Answers 2

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

share|improve this answer
add comment

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

share|improve this answer
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 Mar 1 '12 at 16:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.