+ Reply to Thread
Results 1 to 8 of 8

Matching info with duplicate data

  1. #1
    Registered User
    Join Date
    09-28-2010
    Location
    long beach, california
    MS-Off Ver
    Excel 2007
    Posts
    7

    Matching info with duplicate data

    I maintain a record of golf scores with date & location, continually adding new data at the bottom of the table after I play another round. I want to isolate the info for my lowest 3 rounds in an adjacent table. I've used the SMALL formula to list the 3 lowest scores but how can I match each score with the corresponding date & location, even with 'duplicate' scores? e.g.. in attached file, the lowest and 2nd lowest scores are both 85. Every effort I've made, using MATCH and INDEX, results in matching '85' with the same date & location (15-Nov and 'AAA').
    Attached Files Attached Files
    Last edited by matt azzara; 12-23-2010 at 09:13 PM. Reason: SOLVED

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Matching info with duplicate data

    I typically use a modified RANK formula to differentiate between tied values. It will rank the first encountered low score as 1, second as 2, and so forth.

    In E2 in your example, dragged down
    =RANK(C2,$C$2:$C$6,1)+COUNTIF($C$2:C2,C2)-1

    Then in G3
    =INDEX($C$2:$C$6,MATCH($F3,$E$2:$E$6,0))
    Repeat in H3 and I3 replaced Column C with D and F respectively.
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Matching info with duplicate data

    Edit: deleted
    Last edited by Cutter; 12-23-2010 at 07:45 PM. Reason: Formula does not meet OP's request

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Matching info with duplicate data

    unique rank first then match them
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Registered User
    Join Date
    09-28-2010
    Location
    long beach, california
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Matching info with duplicate data

    ChemistB
    Maybe I missed something but this works fine for fixed data (the 5 posted scores) but when I add a score for a later round (in Row 7) I get an error message in G3. What did I miss? Thks for your time, I've been trying to figure this out a long time... Matt

  6. #6
    Registered User
    Join Date
    09-28-2010
    Location
    long beach, california
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Matching info with duplicate data

    Martin
    Your amended file ID's and matches the data properly but in the wrong order: it ID's the '85' on 29-Nov-10 as the first of the two '85' scores, but it should be the earlier one shot on 15-Nov-10. Thks for all your help... Matt

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Matching info with duplicate data

    making 2 assumptions dates will never be the same and/or never the same score on same day
    then you could do it like this
    Attached Files Attached Files

  8. #8
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Matching info with duplicate data

    Since ChemistB is offline I'll tell you how you can adjust his offering:

    In E3: =IF(C3="","",RANK(C2,$C$2:$C$200,1)+COUNTIF($C$2:C2,C2)-1)
    Drag down to row 200 (or whatever BUT CHANGE the 200 in all the formulas to the whatever)
    It was due to the original formula having a 6 where there is now a 200 that caused the formula to fail when you added more scores.

    In G3: =INDEX($C$2:$C$200,MATCH($F3,$E$2:$E$200,0))
    Drag down to G5

    In H3: =INDEX($B$2:$B$200,MATCH($F3,$E$2:$E$200,0))
    Drag down to H5

    In I3: =INDEX($D$2:$D$200,MATCH($F3,$E$2:$E$200,0))
    Drag down to I5

    One further suggestion:
    In A7: =IF(B7="","",A6+1)
    Drag down to whatever
    Last edited by Cutter; 12-23-2010 at 07:44 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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