Super Duper Concatenate Question

[John Kelly]

Greetings All, I hope someone can help, I have a monthly report which it delivered in Excel but I then need to change it.

Here is what I get:
A B
500234 Tom
Sarah
Joan
299355 Peter
William
Mary
John

What I need to end up with is
A B
500234 Tom, Sarah, Joan
299355 Peter, William, Mary, John


So it's a kind of a "concatenate" and "while" type statement if such thing exists. I've spent more than enough time on it and haven't gotten anywhere. The report is about 2500 records long and it's produced monthly.

Hopefully some of you might be kind enough to take a look and maybe even offer some suggestions.

Many thanks in advance
John

 

[Joe4]

Welcome to the Board!

If you post data without the image tools or code tags, all formatting/spacing is lost. So we really cannot tell what columns "Sarah", "Joan", etc are in.
Are those in column A or column B?

 

[John Kelly]

Sorry the formatting messed things up a bit. Col A has the figures and Col B has the list of names. Hope this helps

 

[John Kelly]

Thanks Joe, I tried to upload a screen grab but it seems to only want to reference a URL

 

[Joe4]

Section B here list tools you can use to post screen images: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html
So, in those other lines, column A is blank, right?

 

[Joe4]

Assuming my assumption is true, this VBA code should work.
Just adjust the value of firstRow, as needed:

Sub MyConcat()

    Dim firstRow As Long
    Dim lastRow As Long
    Dim myRow As Long
    
    Application.ScreenUpdating = False
    
'   Designate first row with data
    firstRow = 1
    
'   Find last row with data in column B
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through all rows backwards, up to row 1
    For myRow = lastRow To (firstRow + 1) Step -1
        If Cells(myRow, "A") = "" Then
            Cells(myRow - 1, "B") = Cells(myRow - 1, "B") & "," & Cells(myRow, "B")
            Rows(myRow).Delete
        End If
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

 

[John Kelly]

Joe, Thanks a million, I'll give it a go. The answer to your question is that the data is in a pivot table and I could flatten it with or without the blanks - so I have the option to populate if I need it to.

Thanks again

 

[John Kelly]

Joe, It works, you are an absolute Star. Thanks a million.

Kindest regards

John

 

[Joe4]

You are welcome!
Glad I was able to help.