Results 1 to 10 of 10

Number of the row with the first occurance

This is a discussion on Number of the row with the first occurance within the Excel Questions forums, part of the Question Forums category; Hi. I need to know the number of the row where 2 different columns specify to specific criteria. Here's an ...

  1. #1
    New Member
    Join Date
    Oct 2011
    Posts
    9

    Unhappy Number of the row with the first occurance

    Hi.

    I need to know the number of the row where 2 different columns specify to specific criteria. Here's an example of my sheet. It is not from the real sheet, but it represents what kind of formula I need.


    ABCDEF
    1NameClassYear
    ClassRow
    2JohnA1
    A
    3JamesA5
    B
    4JackB2
    C
    5JonesC1


    6JimmyA2


    7MarkC6



    In F2 I need to have the number of first the row where B2:B7 matches E2 and where C2:C7 is lower then 4. For F3 it's the same except that it has to match E3 and so on. The value has to be lower than 4 and a 0 can also be a value in this list.

    Thanks in advance.

  2. #2
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Number of the row with the first occurance

    Quote Originally Posted by Ama-Chan View Post
    Hi.

    I need to know the number of the row where 2 different columns specify to specific criteria. Here's an example of my sheet. It is not from the real sheet, but it represents what kind of formula I need.




    ABCDEF
    1NameClassYear


    ClassRow
    2JohnA1


    A


    3JamesA5


    B


    4JackB2


    C


    5JonesC1








    6JimmyA2








    7MarkC6









    In F2 I need to have the number of first the row where B2:B7 matches E2 and where C2:C7 is lower then 4. For F3 it's the same except that it has to match E3 and so on. The value has to be lower than 4 and a 0 can also be a value in this list.

    Thanks in advance.
    Do you want the row number or do you really want the name from column A?

    A = John
    B = Jack
    C = Jones
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  3. #3
    New Member
    Join Date
    Oct 2011
    Posts
    9

    Default Re: Number of the row with the first occurance

    I want the row number.

  4. #4
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Number of the row with the first occurance

    Quote Originally Posted by Ama-Chan View Post
    I want the row number.
    Try this...

    Sheet1

     ABCDEF
    2JohnA1_A2
    3JamesA5_B4
    4JackB2_C5
    5JonesC1___
    6JimmyA2___
    7MarkC6___



    This array formula** entered in F2 and copied down:

    =MIN(IF(B$2:B$7=E2,IF(C$2:C$7<4,ROW(C$2:C$7))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,996

    Default Re: Number of the row with the first occurance

    Quote Originally Posted by Ama-Chan View Post
    Hi.

    I need to know the number of the row where 2 different columns specify to specific criteria. Here's an example of my sheet. It is not from the real sheet, but it represents what kind of formula I need.




    ABCDEF
    1NameClassYear


    ClassRow
    2JohnA1


    A


    3JamesA5


    B


    4JackB2


    C


    5JonesC1








    6JimmyA2








    7MarkC6









    In F2 I need to have the number of first the row where B2:B7 matches E2 and where C2:C7 is lower then 4. For F3 it's the same except that it has to match E3 and so on. The value has to be lower than 4 and a 0 can also be a value in this list.

    Thanks in advance.
    Native row number corresponding to the first matching value...

    F2, control+shift+enter, not just enter, and copy down

    =MIN(IF($B$2:$B$7=E2,IF($C$2:$C$7 < 4, ROW($B$2:$B$7))))

    If you want to return the first corresponding name...

    =INDEX($A$2:$A$7,MIN(IF($B$2:$B$7=E2,IF($C$2:$C$7 < 4, ROW($B$2:$B$7)))))

    If you want some control, these formulas can be wrapped into a CountIf expression...

    =IF(COUNTIF($B$2:$B$7,E2),MIN(IF($B$2:$B$7=E2,IF($C$2:$C$7 < 4, ROW($B$2:$B$7)))),"")

    Use the same set up for the second formula.

    _________________
    Posted from Istanbul.
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    New Member
    Join Date
    Oct 2011
    Posts
    9

    Default Re: Number of the row with the first occurance

    In the text example it worked fine, but in the real sheet it produces some errors.
    I have extended the text sheet for a moment and with the formula you gave me this is the result.

    ABCDEF
    1NameClassYear ClassRow
    2JohnA1 A2
    3JamesA5 B4
    4JackB2 C6
    5AbrahamC4 D0
    6JonesC1
    7JimmyA2
    8MarkC6
    9DanielD4

    Spreadsheet Formulas
    CellFormula
    F2{=MIN(IF(B$2:B$9=E2;IF(C$2:C$9<4;ROW(C$2:C$9))))}
    F3{=MIN(IF(B$2:B$9=E3;IF(C$2:C$9<4;ROW(C$2:C$9))))}
    F4{=MIN(IF(B$2:B$9=E4;IF(C$2:C$9<4;ROW(C$2:C$9))))}
    F5{=MIN(IF(B$2:B$9=E5;IF(C$2:C$9<4;ROW(C$2:C$9))))}
    Formula Array:
    Produce enclosing
    { } by entering


    Now this is exactly what I want, but in the real sheet that I'm working on this is what happens.

    ABCDEF
    1NameClassYear ClassRow
    2JohnA1 A0
    3JamesA5 B4
    4JackB2 C5
    5AbrahamC4 D9
    6JonesC1
    7JimmyA2
    8MarkC6
    9DanielD4






    This is the formula I used in the real sheet and I don't think I did something wrong with it.

    {=MIN(IF($G$2:$G$1000=AD46;IF($U$2:$U$1000<4;ROW($G$2:$G$1000))))}

    Yes, I pressed CTRL + SHIFT + ENTER to enter it and I didn't type the { and the } in the code.

  7. #7
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,164

    Default Re: Number of the row with the first occurance

    Here's another CSE formula that returns the row number.
    =MATCH(E2, REPT($B$1:$B$700, ($C$1:$C$700<4)), 0)

  8. #8
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Number of the row with the first occurance

    Quote Originally Posted by Ama-Chan View Post
    In the text example it worked fine, but in the real sheet it produces some errors.
    I have extended the text sheet for a moment and with the formula you gave me this is the result.

    ABCDEF
    1NameClassYearClassRow
    2JohnA1A2
    3JamesA5B4
    4JackB2C6
    5AbrahamC4D0
    6JonesC1
    7JimmyA2
    8MarkC6
    9DanielD4

    Spreadsheet Formulas
    CellFormula
    F2{=MIN(IF(B$2:B$9=E2;IF(C$2:C$9<4;ROW(C$2:C$9))))}
    F3{=MIN(IF(B$2:B$9=E3;IF(C$2:C$9<4;ROW(C$2:C$9))))}
    F4{=MIN(IF(B$2:B$9=E4;IF(C$2:C$9<4;ROW(C$2:C$9))))}
    F5{=MIN(IF(B$2:B$9=E5;IF(C$2:C$9<4;ROW(C$2:C$9))))}
    Formula Array:
    Produce enclosing { } by entering




    Now this is exactly what I want, but in the real sheet that I'm working on this is what happens.

    ABCDEF
    1NameClassYearClassRow
    2JohnA1A0
    3JamesA5B4
    4JackB2C5
    5AbrahamC4D9
    6JonesC1
    7JimmyA2
    8MarkC6
    9DanielD4












    This is the formula I used in the real sheet and I don't think I did something wrong with it.

    {=MIN(IF($G$2:$G$1000=AD46;IF($U$2:$U$1000<4;ROW($G$2:$G$1000))))}

    Yes, I pressed CTRL + SHIFT + ENTER to enter it and I didn't type the { and the } in the code.
    Why is D = 9?

    You said:

    In F2 I need to have the number of first the row where B2:B7 matches E2 and where C2:C7 is lower then 4.
    So, if D = 9 then you actually want the value that is less than or equal to 4.

    In that case simply change <4 to <=4.

    Still array entered**:

    =MIN(IF($G$2:$G$1000=AD46;IF($U$2:$U$1000<=4;ROW($G$2:$G$1000))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  9. #9
    New Member
    Join Date
    Oct 2011
    Posts
    9

    Default Re: Number of the row with the first occurance

    Quote Originally Posted by mikerickson View Post
    Here's another CSE formula that returns the row number.
    =MATCH(E2, REPT($B$1:$B$700, ($C$1:$C$700<4)), 0)
    Thanks this works perfectly.

    Quote Originally Posted by T. Valko View Post
    So, if D = 9 then you actually want the value that is less than or equal to 4.
    No that is not what I wanted that is what Excel made of it. The first sheet of what I posted in post #6 is what I actually wanted and the second is what Excel made of it.

  10. #10
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Number of the row with the first occurance

    Quote Originally Posted by Ama-Chan View Post
    No that is not what I wanted that is what Excel made of it. The first sheet of what I posted in post #6 is what I actually wanted and the second is what Excel made of it.
    Here's a small sample file that demonstrates this.

    zzzAma-Chan.xls 16kb

    http://cjoint.com/?AJtpGgGeiLG

    As you'll see the formula does return the correct results. If in your file you do not get the correct results then there must be some problem with your data.

    The article at this link describes some common data problems that can cause problems.

    http://contextures.com/xlFunctions02.html#Trouble
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

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
  •  


DMCA.com