Place text from multiple columns into one cell based on another columns value

Hi Guys

Can anyone help me with this as I have a long complicated formula that works, but is a bit of a pain to edit when needed.

I have one column with quantities os speakers being installed in a room with values in from D3 to D39. This may have a number in it or may be blank.

What I want to get to is a formula that goes through this column and only writes a line in a cell if there is a number in this column.

the line that is written in the cell will be from the cells to the left of the quantity column and will also include the quantity column.

So for example the output would be a single cell containing

Ground Floor: Living Room Celing x 2
Ground Floor: Kitchen Celing x 2

I have found that as long as word wrap is turned on the Char(13) will insert a carriage return to nicely put all these all on separate lines.

I hope you can help.

Thanks

Minesh

Screen Shot 2015-03-04 at 21.17.53.png

Hi All,

Please see the screenshot.

Hopefully this makes it easier to se what I am trying to get to.

Thanks

Minesh

Hi All,

A very knowledgeable member of experts exchange Saurabh Singh Teotia wrote the following for me and it works perfectly.

Function combine(r As Range, z As Long, k As Long, a As Long)

Dim c As Range

For Each c In r
If Trim(c.Value) <> "" Then

If combine = "" Then
combine = c.Offset(0, z).Value & ": " & c.Offset(0, k).Value & " " & c.Offset(0, a).Value & " x " & c.Value
Else
combine = combine & Chr(10) & c.Offset(0, z).Value & ": " & c.Offset(0, k).Value & " " & c.Offset(0, a).Value & " x " & c.Value

End If

End If

Next c

End Function


Arkadi - Thanks for trying, yes you were right that it is not easily possible without a very very long messy formula, but this little but of VB made it much easier.