Concatenate row with variable number of columns

Abgar

Active Member
Joined
Jun 20, 2009
Messages
265
Hi All,

Hoping to find a way to concatenate rows with variable numbers of columns using a macro to automate the process.
And example is:
Excel Workbook
ABCDE
1Row 1Data1Data2
2Row 2Data1
3Row 3Data1Data2Data3
4Row 4Data1Data2
5Row 5Data1Data2Data3
6Row 6Data1Data2
7Row 7Data1
...


with the desired output to be:
Excel Workbook
ABCDE
1Row 1Data1, Data2Data1Data2
2Row 2Data1Data1
3Row 3Data1, Data2, Data 3Data1Data2Data3
4Row 4Data1, Data2Data1Data2
5Row 5Data1, Data2, Data 3Data1Data2Data3
6Row 6Data1, Data2Data1Data2
7Row 7Data1Data1
...



So in essence, I need a macro to concatenate all the USED cells in a row starting at column C (and moving to the right), and the output of that concatenation entered into column B.
(in my example above, i have concatenated with a separator as well ", "
If this can also be included then that would be fantastic, but not required.

The total dataset is completely variable. There could be 50,000 rows, or there could be 50, and some of the rows could have up to 80 columns.

If anyone can help me with this, it would be greatly appreciated.

Thanks so much all :)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Give this macro a try...
Code:
Sub ConCatFromColumnC()
  Dim X As Long, LastRow As Long, LastCol As Long
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = StartRow To LastRow
    LastCol = Cells(X, Columns.Count).End(xlToLeft).Column
    If LastCol = 3 Then
      Cells(X, "B").Value = Cells(X, "C").Value
    Else
      Cells(X, "B").Value = Join(Application.Index(Range(Cells(X, "C"), Cells(X, LastCol)).Value, 1, 0), ", ")
    End If
  Next
End Sub
 
Upvote 0
Rick that works great :)

Thanks so much :)

One quick question about this - with the separator (", ") is it possible to change that to be a carriage return (CHAR(10)) instead?
So that the result would then turn out as:

Excel Workbook
ABCDE
1Row 1Data1 Data2Data1Data2
2Row 2Data1Data1
3Row 3Data1 Data2 Data3Data1Data2Data3
Sheet1
Excel 2007

Thanks so much again :)
 
Upvote 0
I have revamped the code to make setting the delimiter easier (there is now a variable you can set to any text, one or more characters, that you want). By the way, the reason I used ", " is because that is what you showed you wanted in your output example.
Code:
Sub ConCatFromColumnC()
  Dim X As Long, LastRow As Long, LastCol As Long, Delimiter As String
  Const StartRow As Long = 1
  Delimiter = vbLf
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = StartRow To LastRow
    LastCol = Cells(X, Columns.Count).End(xlToLeft).Column
    If LastCol = 3 Then
      Cells(X, "B").Value = Cells(X, "C").Value
    Else
      Cells(X, "B").Value = Join(Application.Index(Range(Cells(X, "C"), Cells(X, LastCol)).Value, 1, 0), Delimiter)
    End If
  Next
End Sub
Note that I used the built-in VB constant vbLf, but I could have assigned Chr(10) just as easily (the built-in constants are more efficient to use).
 
Upvote 0
Thank you SOOOO much for your help here Rick :)

And apologies, yes my original intentioon was to use the ", " delimiter, but then changed my mind - but thanks so much for the refined coding :)

Cheers
 
Upvote 0

Forum statistics

Threads
1,223,922
Messages
6,175,382
Members
452,639
Latest member
RMH2024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top