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

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

On Thu, 22 Sep 2011 08:37:57 -0700 (PDT), cardan
wrote:

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

On Sep 22, 9:57*am, Gord wrote:
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

On Thu, 22 Sep 2011 08:37:57 -0700 (PDT), cardan



wrote:
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- Hide quoted text -

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

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

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

In a cell enter =concat(B1:B6)


Gord

On Thu, 22 Sep 2011 10:35:49 -0700 (PDT), cardan
wrote:

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.

On Sep 22, 10:55*am, Gord wrote:
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-clickinsertmodule

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

In a cell enter *=concat(B1:B6)

Gord

On Thu, 22 Sep 2011 10:35:49 -0700 (PDT), cardan



wrote:
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.- Hide quoted text -

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

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


On Thu, 22 Sep 2011 11:58:03 -0700 (PDT), cardan
wrote:

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.