Concatenate the values on condition

[kumark1]

Hi All,

I have a Requirement where we need to concatenate the cells based on conditions

i have the attachment for the reference . please download the file from the link

https://docs.google.com/uc?export=download&id=0B8qpusIRMN_eWDJ5SC1TYVg2Mzg

Regards
Kumar

 

[cyrilbrd]

Hi, Posting a small sample would be better, some members can't or won't download data from external source.
Would a udf be acceptable?

 

[kumark1]

Hi, Posting a small sample would be better, some members can't or won't download data from external source.
Would a udf be acceptable?

Ya no problem.. but will it provide the desired output.. as i got a udf which concats non blanks cells but the delimiters again its problem.

 

[kvsrinivasamurthy]

Here is the macro

Sub ConcatenateColumns()


Dim LR As Long, TR As Long, LC As Long, TC As Long
Dim Resultstring As String


LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Range("A1").Offset(0, Columns.Count - 1).End(xlToLeft).Column


For TR = 2 To LR
Resultstring = Range("A" & TR) & " " & Range("B" & TR) & ": "


    For TC = 4 To LC Step 2
    
    If Cells(TR, TC) <> "" Then
    Resultstring = Resultstring & Cells(TR, TC - 1) & ": " & Cells(TR, TC) & ";"
    End If
       
    Next TC


If Resultstring <> "" Then Cells(TR, TC) = Left(Resultstring, Len(Resultstring) - 1)
Next TR


End Sub

 

[kumark1]

Here is the macro

Sub ConcatenateColumns()


Dim LR As Long, TR As Long, LC As Long, TC As Long
Dim Resultstring As String


LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Range("A1").Offset(0, Columns.Count - 1).End(xlToLeft).Column


For TR = 2 To LR
Resultstring = Range("A" & TR) & " " & Range("B" & TR) & ": "


    For TC = 4 To LC Step 2
    
    If Cells(TR, TC) <> "" Then
    Resultstring = Resultstring & Cells(TR, TC - 1) & ": " & Cells(TR, TC) & ";"
    End If
       
    Next TC


If Resultstring <> "" Then Cells(TR, TC) = Left(Resultstring, Len(Resultstring) - 1)
Next TR


End Sub

Thank u thanks alot u saved my time..

Really appreciate your help... cheers

 

[kumark1]

Here is the macro

Sub ConcatenateColumns()


Dim LR As Long, TR As Long, LC As Long, TC As Long
Dim Resultstring As String


LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Range("A1").Offset(0, Columns.Count - 1).End(xlToLeft).Column


For TR = 2 To LR
Resultstring = Range("A" & TR) & " " & Range("B" & TR) & ": "


    For TC = 4 To LC Step 2
    
    If Cells(TR, TC) <> "" Then
    Resultstring = Resultstring & Cells(TR, TC - 1) & ": " & Cells(TR, TC) & ";"
    End If
       
    Next TC


If Resultstring <> "" Then Cells(TR, TC) = Left(Resultstring, Len(Resultstring) - 1)
Next TR


End Sub

Hi i am facing a problem in the line

LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Range("A1").Offset(0, Columns.Count - 1).End(xlToLeft).Column

if the change the cell reference to

LR = Range("K" & Rows.Count).End(xlUp).Row
LC = Range("K1").Offset(0, Columns.Count - 1).End(xlToLeft).Column

its shoes Error Run Time Error 1004

 

[kvsrinivasamurthy]

Change this code,

LC = Range("K1").Offset(0, Columns.Count - 1).End(xlToLeft).Column

as

LC = Cells(1, Columns.Count).End(xlToLeft).Column

 

[Peter_SSs]

You could also try this user-defined function, which uses a similar method. Place the code into a standard module then use in your worksheet like a normal function, noting that it is designed for a single-row range input.
Use in your sheet like this (in any cell) and copy down.

=Concat(A2:T2)

Function Concat(r As Range) As String
  Dim i As Long
  Dim tmp As String
  
  For i = 4 To r.Columns.Count Step 2
    If Not IsEmpty(r.Cells(i)) Then tmp = tmp & ";" & r.Cells(i - 1).Value & ": " & r.Cells(i).Value
  Next i
  Concat = r.Cells(1).Value & " " & r.Cells(2).Value & ": " & Mid(tmp, 2)
End Function