Concatenate row with variable number of columns

[Abgar]

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
	A	B	C	D	E
1	Row 1		Data1	Data2
2	Row 2		Data1
3	Row 3		Data1	Data2	Data3
4	Row 4		Data1	Data2
5	Row 5		Data1	Data2	Data3
6	Row 6		Data1	Data2
7	Row 7		Data1
...

with the desired output to be:

Excel Workbook
	A	B	C	D	E
1	Row 1	Data1, Data2	Data1	Data2
2	Row 2	Data1	Data1
3	Row 3	Data1, Data2, Data 3	Data1	Data2	Data3
4	Row 4	Data1, Data2	Data1	Data2
5	Row 5	Data1, Data2, Data 3	Data1	Data2	Data3
6	Row 6	Data1, Data2	Data1	Data2
7	Row 7	Data1	Data1
...

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

 

[Rick Rothstein]

Give this macro a try...

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

 

[Abgar]

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
	A	B	C	D	E
1	Row 1	Data1 Data2	Data1	Data2
2	Row 2	Data1	Data1
3	Row 3	Data1 Data2 Data3	Data1	Data2	Data3
Sheet1

Excel 2007

Thanks so much again

 

[Rick Rothstein]

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.

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).

 

[Abgar]

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