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
Merging comments in 4 columns into one
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Merging comments in 4 columns into one
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Merging comments in 4 columns into one
Here is a custom function you can use:
Use it in row 2 with the formula
=Combined(B2:E2)
and fill down. See the attached version (now a .xlsm)
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
=Combined(B2:E2)
and fill down. See the attached version (now a .xlsm)
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Merging comments in 4 columns into one
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 ?
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 ?
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Merging comments in 4 columns into one
It is always better to provide a realistic example at the outset, instead of slowly adding new requirements.
Here is a new version:
Use like this (for example):
=Combined(B2,E2,D2,C2)
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
=Combined(B2,E2,D2,C2)
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Merging comments in 4 columns into one
Thanks Hans.. and apologies, even i realised this later when i used this one in some other files.