Get value if "1"

diogocarvalho95

New Member
Joined
Aug 7, 2019
Messages
4
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:
kxF93Qt
kxF93Qt
HTML:
<img src="https://i.ibb.co/R9WBhzq/Capturar.png" alt="Capturar" border="0">

Thank you very much.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
Formulas are not good for this, suggest you use VBA, I'm not an expert in that I'm afraid
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top