• Status: Solved
• Priority: Medium
• Security: Public
• Views: 108

# 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
0
Happy_Computing
• 3
• 3
1 Solution

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

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

Commented:

Saurabh...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.