look for value in cells in a row, return multiple column names

[timbot18]

i have a table from a supplier, that has states listed in the column name, then a Y (for yes) in each corresponding column for restricted shipping to that state. so for instance, there is a product that has restricted shipping to DC, IL, and MA, each have a "Y" in the cell in that column for the product, then blanks in the rest. i want to return all of those column names (states) into one cell for importing into my store for the products description. is there a way to do that? i've had halfway success using the lookup and index functions, but they only return one value. i need it to return all column names where the "Y" is. can anyone help?

[mrice]

Try this user defined function.

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a suitable cell, enter = =GetStates($B$1:$L$1,B2:L2) or similar and copy down.

Remember to save the workbook as a macro enabled workbook .xlsm

[dogberry]

Hi

Can you post a dummy sample spreadsheet so we can see the layout and what you are trying to do.

Thanks

Chris

[timbot18]

this is basically what i would need in the restricted shipping field, but a formula to do it for me as i have around 11k products and various states with restricted shipping

restriced shipping	AL	AK	AR	AZ
AK, AZ		Y		Y
AK		Y
AL,AR	Y		Y
AK		Y

[timbot18]

the vba code worked. i have been working on this for 3 hours. wish i would have found this forum sooner! Martin is a Genius!

[brynbaker]

Hint: When I'm building a list with separators like this, I place the separator before each value rather than after. Then throwing away the spurious one is easier as its always first:

Please Login or Register  to view this content.

The result should be identical.