+ Reply to Thread
Results 1 to 7 of 7

INDEX Formula - Want to list names in Numerical Order

  1. #1
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    INDEX Formula - Want to list names in Numerical Order

    Hello,

    I have a worksheet with an INDEX formula in it, it works great for 95% percent of what I want it to do, for some reason though, I can't figure out the last 5%.

    In my attached workbook, I have two sheets (Draft and Sheet 2). I want 'Sheet 2' to pull info from 'Draft' based on what is selected in the drop down menus for cells I13:36 of the draft sheet.

    For example, let's say Jose Abreu, the name listed in cell C19 is selected to Team 1, he should thus end up in cell D8 of Sheet 2 because he was the first player chosen to Team 1. This does happen when you select it, the problem I'm running into is if a player above him on the Draft List is also chosen to Team 1 it bumps down the first guy Jose Abreu to cell D9 when he should stay at cell D8 and the newly selected player goes to cell D9 because he was the second player chosen by that team.

    Does anyone know how to get the first player selected by a team to stay in the 1st slot of that team no matter the list? And likewise with all the other numbers and so on.

    Any help would be greatly appreciated. Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: INDEX Formula - Want to list names in Numerical Order

    I didn't download your file.

    Sounds like you need to record the order in which players are selected. Then you just look for the player that corresponds to 1, 2, 3, etc.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Re: INDEX Formula - Want to list names in Numerical Order

    Quote Originally Posted by Tony Valko View Post
    I didn't download your file.

    Sounds like you need to record the order in which players are selected. Then you just look for the player that corresponds to 1, 2, 3, etc.
    Ya, how do you do that?

  4. #4
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Re: INDEX Formula - Want to list names in Numerical Order

    Can anyone help with this? No luck so far, thanks!

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: INDEX Formula - Want to list names in Numerical Order

    On the Draft sheet in J13:J32 enter the round when the player is selected.

    It would look something like this:

    Data Range
    B
    C
    D
    E
    F
    G
    H
    I
    J
    11
    TM
    PLAYER
    DRAFTED / BOUGHT BY
    Round
    12
    13
    LAA
    Mike Trout
    TEAM 3
    1
    14
    LAD
    Clayton Kershaw
    TEAM 1
    1
    15
    PIT
    Andrew McCutchen
    TEAM 4
    2
    16
    DET
    Miguel Cabrera
    17
    ARZ
    Paul Goldschmidt
    18
    MIA
    Giancarlo Stanton
    19
    CWS
    Jose Abreu
    20
    MIL
    Carlos Gomez
    TEAM 2
    1
    21
    BAL
    Adam Jones
    22
    SEA
    Felix Hernandez
    TEAM 1
    2
    23
    TOR
    Jose Bautista
    24
    TOR
    Edwin Encarnacion
    25
    HOU
    Jose Altuve
    TEAM 2
    2
    26
    SEA
    Robinson Cano
    27
    CLE
    Michael Brantley
    28
    WSH
    Anthony Rendon
    TEAM 3
    2
    29
    CWS
    Chris Sale
    30
    SD
    Justin Upton
    31
    SF
    Madison Bumgarner
    TEAM 4
    1
    32
    WSH
    Max Scherzer


    Then, on Sheet2...

    Data Range
    B
    C
    D
    E
    F
    7
    ROUND
    TEAM 1
    TEAM 2
    TEAM 3
    TEAM 4
    8
    1
    Clayton Kershaw
    Carlos Gomez
    Mike Trout
    Madison Bumgarner
    9
    2
    Felix Hernandez
    Jose Altuve
    Anthony Rendon
    Andrew McCutchen
    10
    3


    Enter this array formula** in C8:

    =IFERROR(INDEX(Draft!$C$13:$C$32,MATCH(1,(Draft!$I$13:$I$32=C$7)*(Draft!$J$13:$J$32=$B8),0)),"")

    ** 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.

    Copy across as needed then down as needed.

  6. #6
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Re: INDEX Formula - Want to list names in Numerical Order

    Quote Originally Posted by Tony Valko View Post
    On the Draft sheet in J13:J32 enter the round when the player is selected.

    It would look something like this:

    Data Range
    B
    C
    D
    E
    F
    G
    H
    I
    J
    11
    TM
    PLAYER
    DRAFTED / BOUGHT BY
    Round
    12
    13
    LAA
    Mike Trout
    TEAM 3
    1
    14
    LAD
    Clayton Kershaw
    TEAM 1
    1
    15
    PIT
    Andrew McCutchen
    TEAM 4
    2
    16
    DET
    Miguel Cabrera
    17
    ARZ
    Paul Goldschmidt
    18
    MIA
    Giancarlo Stanton
    19
    CWS
    Jose Abreu
    20
    MIL
    Carlos Gomez
    TEAM 2
    1
    21
    BAL
    Adam Jones
    22
    SEA
    Felix Hernandez
    TEAM 1
    2
    23
    TOR
    Jose Bautista
    24
    TOR
    Edwin Encarnacion
    25
    HOU
    Jose Altuve
    TEAM 2
    2
    26
    SEA
    Robinson Cano
    27
    CLE
    Michael Brantley
    28
    WSH
    Anthony Rendon
    TEAM 3
    2
    29
    CWS
    Chris Sale
    30
    SD
    Justin Upton
    31
    SF
    Madison Bumgarner
    TEAM 4
    1
    32
    WSH
    Max Scherzer


    Then, on Sheet2...

    Data Range
    B
    C
    D
    E
    F
    7
    ROUND
    TEAM 1
    TEAM 2
    TEAM 3
    TEAM 4
    8
    1
    Clayton Kershaw
    Carlos Gomez
    Mike Trout
    Madison Bumgarner
    9
    2
    Felix Hernandez
    Jose Altuve
    Anthony Rendon
    Andrew McCutchen
    10
    3


    Enter this array formula** in C8:

    =IFERROR(INDEX(Draft!$C$13:$C$32,MATCH(1,(Draft!$I$13:$I$32=C$7)*(Draft!$J$13:$J$32=$B8),0)),"")

    ** 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.

    Copy across as needed then down as needed.
    Awesome thanks a lot works great!

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: INDEX Formula - Want to list names in Numerical Order

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] INDEX names in alphabetical order. Horizontally.
    By JasonNeedsHelp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-20-2013, 11:43 AM
  2. Code for Button to Submit Data to List in Numerical and Chronological Order
    By swordswinger710 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-13-2012, 01:20 AM
  3. Replies: 2
    Last Post: 10-13-2011, 04:05 PM
  4. list names in random order in Excel 2002
    By John Murf in forum Excel General
    Replies: 3
    Last Post: 02-24-2006, 10:10 PM
  5. [SOLVED] How do I sort multiple worksheet pages to list in numerical order?
    By Thanksforyourhelp in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-07-2006, 02:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1