CODEWITHSUNDEEP
excelvbaconcatenation
Kateřina Chábová
Kateřina Chábová

Reputation: 61

Concatenate value in each column based on cell value

Hello I need to concatenate the columns depending on whether the rows are null or have any value.
I.e. I have data like this

enter image description here

And I need get data like this

enter image description here

The problem is that the columns I want to join will be more than 50 and for each I have to check if there is a blank or a number in that column and row.

Can you help me with this please?

Upvotes: 1

Views: 83

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

If you asked for a VBA solution, please try the next code. It will process as many Value columns will be, and returns the processing result two columns after the last one. Using arrays, the code will be very fast even for a large range:

Sub ConcatenateWithValues()
   Dim sh As Worksheet, lastR As Long, lastCol As Long, arr, arrH, arrFin
   Dim strConc As String, i As Long, j As Long
   
   Set sh = ActiveSheet   'use here the sheet you need
   lastR = sh.Range("A" & sh.Rows.count).End(xlUp).row          'last row on column A:A
   lastCol = sh.cells(1, sh.Columns.count).End(xlToLeft).Column 'last column on the first row
   
   arr = sh.Range("A1", sh.cells(lastR, lastCol)).Value         'place the whole range in an array, for faster iteration
   arrH = sh.Range("A1", sh.cells(1, lastCol)).Value            'Place headers in an array
   ReDim arrFin(1 To UBound(arr), 1 To 2)                       'Redim the array to contain the processing result
   arrFin(1, 1) = arrH(1, 1): arrFin(1, 2) = "Concatenate_Value"'Place the headers in the array
   For i = 2 To UBound(arr)
        For j = 2 To lastCol 'build the necessary concatenation
            If arr(i, j) <> "" Then strConc = strConc & arrH(1, j) & ":" & arr(i, j) & ";"
        Next
        arrFin(i, 1) = arr(i, 1): arrFin(i, 2) = left(strConc, Len(strConc) - 1) 'remove the last ";" character
        strConc = ""         'reinitialize the string keeping row concatenation
   Next i
   'Drop the processed array content at once:
   sh.cells(1, lastCol + 2).Resize(UBound(arrFin), 2).Value = arrFin
End Sub

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36750

With Microsoft-365 you may try-

=TEXTJOIN("; ",TRUE,FILTER(IF($B$2:$D$5<>"",$B$1:$D$1&":"&$B$2:$D$5,""),$A$2:$A$5=F2))

enter image description here

Upvotes: 0

Related Questions