Get value if "1"
Results 1 to 9 of 9

Thread: Get value if "1"
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Get value if "1"

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

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Get value if "1"

    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

  3. #3
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Get value if "1"

    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))

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Get value if "1"

    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

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get value if "1"

    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...4d398-full.png

    Thank you

  6. #6
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Get value if "1"

    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 by Special-K99; Aug 7th, 2019 at 06:35 AM.

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get value if "1"

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





  8. #8
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Get value if "1"

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

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get value if "1"

    i found this example https://stackoverflow.com/questions/...mn-below-has-1
    i think is this way.

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •