How to combine content of multiple cells into one cell

**friend11_6** · 07-09-2009, 10:05 PM

Hi.

I am trying to use mail merge to send out notification to employees on the details their expense claims had been processed for payment.

My database (pivottable) has a column that lists down all the expense claims document numbers and the total to be paid for all these expense claims added together. How do I create a formula to concatenate all the document numbers into one cell, given that the number of cells containing the document numbers vary from employee to employee? I have attached the file showing the format of my database (pivottable).

Thanks.

**JBeaucaire** · 07-10-2009, 02:19 AM

Care to post an example of the results you're looking for?

**friend11_6** · 07-10-2009, 02:53 AM

Hi.

I've added the results that I'm looking for in red bold of the attached file.

Any clue?

Thanks!

**JBeaucaire** · 07-10-2009, 11:25 AM

Run this macro with the pivot report sheet active:

Option Explicit

Sub DocReport()
'JBeaucaire (7/10/2009)
Dim FR As Long, LR As Long, NR As Long, i As Long, MyVal As String
FR = Cells.Find("Vendor ID").Row + 1
LR = Range("A" & Rows.Count).End(xlUp).Row - 1
NR = LR + 3

Range("A" & NR) = "Vendor ID"
Range("B" & NR) = "Document(s)"

    For i = FR To LR
        If Cells(i, "A") Like "*Total" Then
            'Do nothing
        ElseIf IsEmpty(Cells(i, "A")) Then
            Cells(NR, "B") = Cells(NR, "B") & ";" & Cells(i, "D")
        ElseIf Cells(i, "A") Like "EE*" Then
            NR = NR + 1
            Cells(NR, "A") = Cells(i, "A")
            Cells(NR, "B") = Cells(i, "D")
        End If
    Next i

    With Range("A" & LR + 4).CurrentRegion
        .Font.Bold = True
        .Font.ColorIndex = 3
        .Select
    End With
End Sub