Function or Code Needed

[Jennifer]

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
?

 

[Bernard Liengme]

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

 

[Jennifer]

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]

Very different problem!
With just four states we would get away with
=IF(C2="Y",C$1&",","")&IF(D2="Y",D$1&",","")&IF(E2="Y",E$1&",","")&IF(F2="Y",F$1&",","")
We would need some way to remove the final comma
Clearly with 50 states, the formula would grow to become unmanageable

I think a VBA approach is the only answer. As your ready to enter that
universe?
best wishes

 

[Bernard Liengme]

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

 

[Jennifer]

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

 

[Bernard Liengme]

It is called a 'counter' It starts at 1, the code between FOR & NEXT get
done, J increases by 1.
This loop goes non until J is > the number of cells in B1:AZ1, I could have
used 50 for this but it was easier to test with a smaller data set
best wishes