Combining Text based on Values

[cardan]

Hi,
I am working with text and trying to create a string of text based on
values within a certain range and I am having trouble combining it.

In column A (A1:A6), I have products, say A, B, C, D, E, F. In column
B (B1:B6) are corresponding numbers that equal the number of that
product. These numbers are either equal to or greater than zero and
are always changing. Sometimes only one product will be greater than
zero, or it can be any combination of the products; for example, A, C,
& F will have values greater than zero, or just B & C, or just “E”
will be the only one with values greater than zero.

What I would like to do is write a formula that will combine and
return the product name(s) based on the values in column B. For
example, if product A is the only one greater than zero, then have it
equal “A”, if say A,C,& F have values, have it return “A/C/F” or “B/C”
etc… The slashes would be an ideal separator but not necessary.
I have tried concatenate but had no luck with combining formulas or
accounting for the numerous variations and realized very quickly, I
have no clue where to start. Any feedback would be most helpful.
Thanks

 

[Gord]

Function concat(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If cell.Value > 0 Then sbuf = sbuf & cell.Offset(0, -1).Text & "/"
Next
concat = Left(sbuf, Len(sbuf) - 1)
End Function

=concat(B1:B6)


Gord Dibben Microsoft Excel MVP

 

[cardan]

Function concat(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
    For Each cell In CellBlock
    If cell.Value > 0 Then sbuf = sbuf & cell.Offset(0, -1).Text & "/"
    Next
    concat = Left(sbuf, Len(sbuf) - 1)
End Function

=concat(B1:B6)

Gord Dibben    Microsoft Excel MVP





- Show quoted text -

Hi Gord, Thank you for the quick reply. Would there be a way to do
this with just formulas? My VBA skills are not that great and I am
not familiar (and confident) with inputting code. Thanks again.

 

[Gord]

You posted in programming group so assumed you could handle
programming.

I can't think of any worksheet formula solution at the moment. I'm
sure someone will jump in with a worksheet function solution for you.

In the meantime this might be a good time to start to learn VBA which
is a powerful adjunct to Excel and would make your Excel life so much
easier IMO.

What I posted was a User Defined Function.

To set it up.........................

With your workbook open hit Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Select your workbook/project and right-click>insert>module

Paste the UDF into that module. Save the workbook. Alt + q to return
to Excel.

In a cell enter =concat(B1:B6)


Gord

 

[cardan]

You posted in programming group so assumed you could handle
programming.

I can't think of any worksheet formula solution at the moment.  I'm
sure someone will jump in with a worksheet function solution for you.

In the meantime this might be a good time to start to learn VBA which
is a powerful adjunct to Excel and would make your Excel life so much
easier IMO.

What I posted was a User Defined Function.

To set it up.........................

With your workbook open hit Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Select your workbook/project and right-click>insert>module

Paste the UDF into that module.  Save the workbook.  Alt + q to return
to Excel.

In a cell enter  =concat(B1:B6)

Gord



- Show quoted text -

Hi Gord, Thank you for the input. I usually do some complex formula
writing and found I get the best reponses here, so I usually asked my
questions here under the programming group. Also sometimes I have to
share the models and sometimes others don't know anything about macros
(less than I do) so I will stay away from them (and sometimes arrays
too).

I followed your instructions and it works great. One question I do
have, is if I wanted to copy that formula over to other columns, how
can I make it so that it will reference the names in Column A, but
adjust based on the values within another column. It is my fault for
not being specific enough. I figured I would keep my question as
simple as possible since I can always copy a formula easily. Thank
you again for your assistance.

 

[Gord]

Sorry for the delay...........hope you're still watching.

Here is a modification that will allow dragging formula across while
keeping column A as reference to names.

Function concat(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If cell.Value > 0 Then sbuf = sbuf & _
Range("A" & cell.Row).Text & "/"
Next
concat = Left(sbuf, Len(sbuf) - 1)
End Function


Gord

 

[cardan]

Sorry for the delay...........hope you're still watching.

Here is a modification that will allow dragging formula across while
keeping column A as reference to names.

Function concat(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
    For Each cell In CellBlock
    If cell.Value > 0 Then sbuf = sbuf & _
         Range("A" & cell.Row).Text & "/"
    Next
    concat = Left(sbuf, Len(sbuf) - 1)
End Function

Gord




- Show quoted text -

Hi Gord, Thank you again for your response. I tried the latest code,
but it does not seem to be taking any differently from the previous
code. I tried to modify it slightly to match my workbook a bit more,
but I it still does the same thing- so it is hard for my novice eyes
see what is happening. Essentially, my names are actually in column D
(D:2024). I am starting my "concat" formula in column E (E7). It
works great in cell E7. but when I copy the formula to F7, it then
returns the values that were are in column E rather than the names in
Column D.

I thought I could change this by renaming the range from "A" to "D",
or possibly changing the -1 in the concat formula but no luck. I am
sure it is an easy fix, I just don't know where to start. Thank you
again for your continued assistance on this! -Dan

 

[GS]

cardan explained :

Hi Gord, Thank you again for your response. I tried the latest code,
but it does not seem to be taking any differently from the previous
code. I tried to modify it slightly to match my workbook a bit more,
but I it still does the same thing- so it is hard for my novice eyes
see what is happening. Essentially, my names are actually in column D
(D:2024). I am starting my "concat" formula in column E (E7). It
works great in cell E7. but when I copy the formula to F7, it then
returns the values that were are in column E rather than the names in
Column D.

I thought I could change this by renaming the range from "A" to "D",
or possibly changing the -1 in the concat formula but no luck. I am
sure it is an easy fix, I just don't know where to start. Thank you
again for your continued assistance on this! -Dan

To reference the column containing names, prefix the column letter with
"$".

So...<snip>

If cell.Value > 0 Then sbuf = sbuf & _
Range("$D") & cell.Row).Text & "/"

 

[Gord]

Names in D2024............got that.

First formula in E7...........got that.

Which column contains the corresponding numbers that are either equal
to or greater than zero and are always changing?

We built the function based on your original description where you had
a column of names and a column of numbers.

I revised it so you could have a fixed column of names and multiple
contiguous columns of numbers to the right of that names column.

You don't have even one column of numbers, much less two or more.

If you want to send me a copy of your workbook and an explanation feel
free to send to my email.................not the group.

Change phnorton to gorddibb


Gord