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

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

Screen-Shot-2015-03-04-at-21.17.53.png      


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
Asked:
Happy_Computing
  • 3
  • 3
1 Solution
 
Saurabh Singh TeotiaCommented:
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

Open in new window


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
 
Happy_ComputingAuthor 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
 
Happy_ComputingAuthor 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Saurabh Singh TeotiaCommented:
Their you go i made some changes in the formula which does what you are looking for...

Saurabh...
combine2.xlsm
0
 
Happy_ComputingAuthor Commented:
Awesome answer, will will save me hours of time.
0
 
Saurabh Singh TeotiaCommented:
Glad to help .. :-)

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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now