# 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 of 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 the data would look like

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
LVL 3
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Their you go i wrote you a simple function which does what you are looking for...

``````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
``````

Just give the number in series which you want to combine and wrap that cell it will do what you are looking for...

Saurabh...
combine.xlsm
0
Author Commented:
Hi Saurabh

I added some columns and deleted them and broke it.

Can you tell me where I went wrong.

Also please can you tell me how to edit this is I want to add another column on the left?

so it would be like Floor, Sub-Floor, Room, Location.

Thanks

Minesh
combine.xlsm
0
Author Commented:
Ok I fixed the problem I had, I named the module combine and then remembered it needs a different name

Can you just let me know how to add another column? on the left and then I also want to add another options columns to the right like this attachment

Finally I would like the column header as part of the description.

Also I am using this in multiple cells, so I have to click each one and press enter to refresh.

Is there a way to refresh automatically or to add a button to refresh them all?
combine2.xlsm
0
Commented:
Their you go i made some changes in the formula which does what you are looking for...

Saurabh...
combine2.xlsm
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Awesome answer, will will save me hours of time.
0
Commented: