Thank you , Benard. This is exactly what I was looking for. Can yoiu explain
what the "J" stands for in the code?

"Bernard Liengme" wrote:

Here is a VBA solution
You need to open the VB Editor, insert a module and paste the code below
into the module window
Then in B2 enter =YNString(B2:AZ2) and copy that formula down the column

If you need help with VBA begin with a visit to the site below and then come
back with questions
David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes


Option Base 1
Function YNstring(myrange)
Set mystates = Range("C1:AZ1")
mycount = myrange.Count
myflag = True
For J = 1 To mycount
If myrange(J) = "Y" Then
If myflag Then
YNstring = YNstring & mystates(J)
myflag = False
Else
YNstring = YNstring & "," & mystates(J)
End If
End If
Next J
End Function

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jennifer" wrote in message
...
Each state has its own column, 50 columns in all. I would like to, lets
say,
in column B, have the states that = "Y" in column B.

Example:
columns B C D E F
Row 1 AL AZ CA DE
Row 2 AL, CA, DE Y N Y Y
Row 3 AZ,CA N Y Y N



"Bernard Liengme" wrote:

Assuming states in A, yes-no in B
Insert new B column (right click the B heading and use Insert)
Now Y/N are in column C
In B1 =IF(C1="Y",A1,"") and copy this down the column
The quick way to do the copy is to double click B1's fill handle which is
the small black square in lower right corner when call is selected.
That is a pair of double-quotes " with nothing between them
If you want to pick up that list and use elsewhere, you can change the
formula to text: select all of the entries in B; use Copy; with them
still
selected use Edit | Paste Special with Vales specified
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jennifer" wrote in message
...
If I have 51 columns,each column is a state and each row has a status
for
each state, "Y" or "N", is there a way I can insert a column prior to
first
state column that could pull which state has a "Y" and list in the new
column
?