Merging comments in 4 columns into one

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Merging comments in 4 columns into one

Post by shreeram.maroo »

Hi,

I have excel sheet where i am having unique Id in first column and there are comments mentioned in the next 4 columns. It is not necessary that the comments are mentioned in all 4 columns against an id. All 4 columns could be blank or some could be blank and some could be non blank.

I need to add a column in last where i want to concatenate the comments and serially number them. I am attaching a sample excel to make the requirement more clear. Can that be done to a formula ? I have tried hard using combination of if and concatenate, but no positive results !!!.

Regards
Shreeram
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Merging comments in 4 columns into one

Post by HansV »

Here is a custom function you can use:

Code: Select all

Function Combined(rng As Range) As String
    Dim cel As Range
    Dim i As Long
    Dim s As String
    For Each cel In rng
        If cel.Value <> "" Then
            i = i + 1
            s = s & vbLf & i & ". " & cel.Value
        End If
    Next cel
    Combined = Mid(s, 2)
End Function
Use it in row 2 with the formula

=Combined(B2:E2)

and fill down. See the attached version (now a .xlsm)

Merge examples.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Merging comments in 4 columns into one

Post by shreeram.maroo »

Thanks Hans, this is working.
I am just facing one challenge - there could be a break in between column, I mean the range need not be strictly B2:E2. It can be even combined(B2,D2,E2,G2,F2) and so on.. How can i capture this ?

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Merging comments in 4 columns into one

Post by HansV »

It is always better to provide a realistic example at the outset, instead of slowly adding new requirements.

Here is a new version:

Code: Select all

Function Combined(ParamArray Args()) As String
    Dim j As Long
    Dim i As Long
    Dim s As String
    For j = LBound(Args) To UBound(Args)
        If Args(j) <> "" Then
            i = i + 1
            s = s & vbLf & i & ". " & Args(j)
        End If
    Next j
    Combined = Mid(s, 2)
End Function
Use like this (for example):

=Combined(B2,E2,D2,C2)

Merge examples.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Merging comments in 4 columns into one

Post by shreeram.maroo »

Thanks Hans.. and apologies, even i realised this later when i used this one in some other files.