Get value if "1"

[diogocarvalho95]

Hi,
How can i get the first line value of the collumn with "1" value and print the result on the last column like in example above:

<img src="https://i.ibb.co/R9WBhzq/Capturar.png" alt="Capturar" border="0">

Thank you very much.

 

[Special-K99]

Try

=INDEX(A1:G2,1,MATCH(1,A2:G2,0))&","&INDEX(A1:G1,1,MAX(IF(A2:G2=1,COLUMN(A2:G2))))
Array formula, use Ctrl-Shift-Enter

 

[Special-K99]

Or non array formula

=INDEX(A1:G2,1,MATCH(1,A2:G2,0))&","&INDEX(A1:G1,1,AGGREGATE(14,6,(A2:G2=1)*COLUMN(A2:G2),1))

 

[Special-K99]

Your output is incorrect in I2, it should be Titulo3, Titulo5

Also, I made a mistake in the formulas

in I2
=INDEX(A$1:G$1,1,MATCH(1,A2:G2,0))&","&INDEX(A$1:G$1,1,AGGREGATE(14,6,(A2:G2=1)*COLUMN(A2:G2),1))
and copy down the column

 

[diogocarvalho95]

it works, awesome!!
But i have a problem, in the row number 5 i have more than two "1".

printscreen:
https://cdn1.imggmi.com/uploads/2019/8/7/d9c183aad21d177e240b39b9aea4d398-full.png

Thank you

 

[Special-K99]

This is turning from a "how can i get the first and last non zeros in a row" to
"How can I concatenate all the non zeros in a row"
which is much more complicated process.

Whats your expected output for that row?

Title1,2,3,4,5,7 ?

If so then unless you have TEXTJOIN, you would need VBA to do this and not formulas (unless you know there will only be 7 columns).

Will there only be 7 columns? Or more?

 

[diogocarvalho95]

Yes, that's the output i want.
will be more than 7 columns, around 40

​

• 
  [*=center]
  
  
  [*=center]
  

 

[Special-K99]

Formulas are not good for this, suggest you use VBA, I'm not an expert in that I'm afraid

 

[diogocarvalho95]

i found this example https://stackoverflow.com/questions/28679758/concatenate-top-row-cells-if-column-below-has-1
i think is this way.

Thank you for your help . have a good day!!