# Get value if "1"

#### diogocarvalho95

##### New Member
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:
HTML:
``<img src="https://i.ibb.co/R9WBhzq/Capturar.png" alt="Capturar" border="0">``
Thank you very much.

### Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Special-K99

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
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

#### Special-K99

##### Well-known Member
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:

#### diogocarvalho95

##### New Member
Yes, that's the output i want.
will be more than 7 columns, around 40
:banghead:

• [*=center]

[*=center]

#### Special-K99

##### Well-known Member
Formulas are not good for this, suggest you use VBA, I'm not an expert in that I'm afraid

Threads
1,102,678
Messages
5,488,218
Members
407,632
Latest member
varunwalla

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...