Id months inlcuded in data set for text string

[Greg]

Hi,

I am having some trouble coming up with a way to create a text string
that ids the months included into a custom data set. My structure is
as follows:

Jan Feb Mar Apr ---> Dec
1 3

In the example above the data set contains Jan & Mar. I want to create
a title that lists the months used.

ex. Months Included: Jan, Mar

Any thoughts as best way to elminate spaces and get what I am looking
for?

Thanks in advance,

Greg

 

[Bill Kuunders]

I assume your 1 and 3 values are in cells a2 and c2

In A3 enter...........(You may have to insert a row)
=IF(A2="","",A1) and extend this across to L3

enter in a cell where the header is the following.....
="Months Included: "&A3&" "&B3&" "&C3&" "&D3&" "&E3&" "&F3&" "&G3&" "&H3&"
"&I3&" "&J3&" "&K3&" "&L3

 

[Greg]

Thanks Bill,

I thought of that, but added spaces come accross even if there isn't a
value. For example in your formula if there is not value in B3, I
still get a space via the &" " statement. So it could look like this:

Jan Apr May Nov

I guess that could work, but was hoping to find a solution that gave
me:

Jan, Apr, May, Nov

Cheers from San Francisco,

Greg

 

[Greg]

Just pullting the used months together I could get the data to look
like this:

JanAprMayNov

If I did that how would I seperate and add commas between?

Thanks,

Greg

 

[Bill Kuunders]

Change the if function in a3 and across to

=IF(A2="","",A1&",")

Greetings
Bill K

 

[Bill Kuunders]

You could even have a space after the comma
=IF(A2="","",A1&", ")

Bill K

 

[Greg]

Hmm... Yes that works. Not perfect, because the last month always has
a trailing comma.

I guess I can live with that versus diong gymnastics with if then
statements.

Thanks for hte help.

Greg

 

[Ron Rosenfeld]

Hi,

I am having some trouble coming up with a way to create a text string
that ids the months included into a custom data set. My structure is
as follows:

Jan Feb Mar Apr ---> Dec
1 3

In the example above the data set contains Jan & Mar. I want to create
a title that lists the months used.

ex. Months Included: Jan, Mar

Any thoughts as best way to elminate spaces and get what I am looking
for?

Thanks in advance,

Greg

With your data structure, it seems as if you are looking at examining each row.

You could use a UDF.

To enter this, <alt-F11> opens the VB Editor.
Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

In some cell, enter the formula =MonthsIncluded(rg) where rg is a 12 cell row
of data underlying your month names.

==========================
Function MonthsIncluded(data As Range) As String
Dim i As Long
Dim c As Range
Dim Mnths As Variant

If data.Count <> 12 Then
MonthsIncluded = "Invalid Range"
Exit Function
End If

Mnths = Array("Jan", "Feb", "Mar", "Apr", "May", _
"Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

For Each c In data
If Len(c.Text) > 0 Then
MonthsIncluded = MonthsIncluded & Mnths(i) & ", "
End If
i = i + 1
Next c

MonthsIncluded = "Months Included: " & _
Left(MonthsIncluded, Len(MonthsIncluded) - 2)

End Function
================================

Have fun!

--ron

 

[Bill Kuunders]

I'm sure Ron's solution will work.

This one works as well.

=IF(A2="","",IF(AND(A2>0,SUM(B2:$L2)>0),A1&", ",A1))
extend across
Only in the cell for Dec you leave the simple formula without a comma or the
AND bit.

Regards
Bill K