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

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Saurabh Singh TeotiaCommented:
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

Your issues matter to us.

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

Start your 7-day free trial
Happy_ComputingAuthor Commented:
Awesome answer, will will save me hours of time.
0
Saurabh Singh TeotiaCommented:
Glad to help .. :-)

Saurabh...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.