need to change excel columns of data into one string of comma delimited data, help

[wnorrick]

I have a spreadsheet that has 16 columns of basic data (names, addresses, total rent, etc) and i need to change to a spreadsheet with one column of the data that is a string of comma delimited data. i have to save as text then and upload to a program. Is there a way to combine the columns into one as a string of data using VBA or something else? i have attached a sample spreadsheet with row 1 our data and down a few rows what it needs to look like. we will have a few columns that are blank and they are just indicated by a comma as you will see in the sample.
Any help or suggestions will be appreciated. There are 492 rows of data that need to be converted.
Thank you,

 

[Smallman]

Hi Wnorrick

Best way would be to use formula down the side in col Q and concatenate with comma space join. Then just copy formula down.

If you want VBA to do it this should get you started.

Option Explicit
 
Sub JonIt()
Dim ar As Variant
Dim var As Variant
Dim i As Long
Dim j As Long
Dim str As String
 
    ar = Sheet1.Cells(1).CurrentRegion.Value
    ReDim var(1 To UBound(ar, 1) - 1, 1 To 1)
        For i = 2 To UBound(ar, 1)
            For j = 1 To UBound(ar, 2)
                str = str & ", " & ar(i, j)
            Next j
            var(i - 1, 1) = Mid(str, 3, 500): str = ""
        Next i
    Sheet3.Range("A2:A" & UBound(var, 1) + 1).Value = var
End Sub

Will post a workbook to show workings.

Take care

Smallman

 

[wnorrick]

Thank you. I will check out both methods. I really appreciate the help.