Results 1 to 3 of 3
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Vlookup Help (Duplicate Values) (All)

    The easisest solution might be to NOT have duplicate numbers in Column B.

    You don't have the formulas in them, but if they are always integers, then adding a decimal portion as a "tie-breaker" would work. You could use the row number as the tie breaker.

    Just take your current formula and add something like:
    ="currentformula" +row()/100000

    Now there will NOT be any ties! A "9" in row 5 will be 9.00003, while a nine in row 14 will be 9.00014, and in row 65536 will be 9.65535. Your scheme using vlookup with large will work fine since there are NO longer any duplicates

    FYI, Concerning a Vlookup to the left see <post#=244408>post 244408</post#> for a workaround.


  2. #2
    2 Star Lounger
    Join Date
    Oct 2001
    Not in KC anymore
    Thanked 0 Times in 0 Posts

    Vlookup Help (Duplicate Values) (All)

    Edited by HansV to activate URL - see <!help=19>Help 19<!/help>

    I have a list of persons and a number value for each one.

    I need a way to use something similar to the vlookup function to return my list in a descending order based on the number, and include the name of each person who matches that number value.

    Basically what it is is a "top ten" list, but I'm having trouble because of the duplicate values. These values change frequently and therefore the "top ten" order will change frequently.

    I've attached a spreadsheet with the basic vlookup function in place (which doesn't give me what I need) as well as an "example" of what I need it to look like.

    This is just an example of the spreadsheet I'm working on. I've simplified it quite a bit because it would probably be too large to upload.

    I've inserted comments to provide more information.

    If more info is needed, please let me know.


    Edited to add the following:

    I tried a google search and found this on Chip Pearson's site, but I don't see a way to skip the ranking numbers. Or maybe I'm just not familiar enough with it to know how it works.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    The Hague, Netherlands
    Thanked 0 Times in 0 Posts

    Re: Vlookup Help (Duplicate Values) (All)

    Your data is in A2:B15, consisting of:

    {"Name","Count";"John",5;"Jane",6;"Doug",9;"Alex", 4;"Bob",2;"Jim",5;"Jeff",7;"Debbie",6;"Dana",7;"Mi ssy",1;"Larry",2;"Ray",9;"Kelly",5}

    In C3 enter & copy down:


    In E3 enter & copy down:


    In F3 enter & copy down:

    Microsoft MVP - Excel

Posting Permissions

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