Concatenation of Cell Vaues

[jm25]

Hi,

Apoliges, this is probably very simple, but, I have a spreadsheet, with columns 1-10. In each column will either be blank or has "Yes" in it. What I want, is for each row that does have a yes in it, have that Row number put into its own column. For example, if a row has yes in 1,4 and 7, I would like to see 1,4,7 in its own column. Is there an easy query to achieve this? Please see my hard coded example. The TRUE Fields I would like populated automatically.

Thanks in advance.

Cells.PNG

[sktneer]

Please find the attached sheet to see one of the way to achieve this.

[martindwilson]

for that example
=SUBSTITUTE(TRIM(IF(A2="yes",$A$1&" "," ")&IF(B2="yes",$B$1&" "," ")&IF(C2="yes",$C$1&" "," ")&IF(D2="yes",$D$1&" "," ")&IF(E2="yes",$D$1&" "," ")&IF(F2="yes",$F$1&" "," ")&IF(G2="yes",$G$1&" "," ")&IF(H2="yes",$H$1&" "," ")&IF(I2="yes",$I$1&" "," ")&IF(J2="yes",$J$1&" "," ")&IF(K2="yes",$K$1&" "," ")&IF(L2="yes",$L$1&" "," ")&IF(M2="yes",$M$1&" "," ")&IF(N2="yes",$N$1&" "," ")&IF(O2="yes",$O$1&" "," "))," ",",")