Results 1 to 9 of 9

Vlookup only pulling first example of duplicates in a column

This is a discussion on Vlookup only pulling first example of duplicates in a column within the Excel Questions forums, part of the Question Forums category; I have a file with 4 columns and about 21 rows (Excel 2010). The first table in the sheet holds ...

  1. #1
    New Member
    Join Date
    Jul 2008
    Posts
    6

    Default Vlookup only pulling first example of duplicates in a column

    I have a file with 4 columns and about 21 rows (Excel 2010). The first table in the sheet holds number of occurances in column A and then column B is the name of the occurance. In column E I use the Large function (=LARGE($A$4:$A$23,1), =LARGE($A$4:$A$23,2), etc.) in each of the rows to create a descending list of the occurances (creating a Pareto diagram). In column D, I am trying to pull in the corresponding name of each occurance using the Vlookup command (=VLOOKUP(E4,$A$4:$B$21,2,FALSE), =VLOOKUP(E5,$A$4:$B$21,2,FALSE), etc). The problem is that when there are multiple occurances with the same number of occurances (15 missing information, 15 stockouts) it is only pulling the first name of the occurance into the D column.

    So, if there's a number 15 in A4 with the occurance name of missing info in B4 and a number 15 in A5 with the occurance name of stockouts in B5, the E column is right, and D4 is right (shows missing information), but D5 doesn't show stockouts, it repeats missing information. Hope that makes sense. Here's a summary of the cells:

    A4 = 15
    B4 = missing info
    A5 = 15
    B5 = stockouts
    D4 = =LARGE($A$4:$A$23,1) ***15 is the largest number in the list
    E4 = =VLOOKUP(E4,$A$4:$B$21,2,FALSE)
    D5 = =LARGE($A$4:$A$23,2)
    E5 = =LARGE($A$4:$A$23,2)

    Since I'm trying to use this info to create a Pareto diagram, I need the name of the occurance to be accurate. Is there a better way to do this or how can I make this work?

  2. #2
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,475

    Default Re: Vlookup only pulling first example of duplicates in a column

    christianna,

    You may be able to use User Defined Function VLookupNth, or and array formula.

    What version of Excel are you using?

    You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

    Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

    This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

    Here are three possible ways to post small (copyable) screen shots directly in your post:

    Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
    http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

    or
    http://RichardSchollarís beta HTML M...om of his post

    or
    Borders-Copy-Paste


    Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  3. #3
    New Member
    Join Date
    Jul 2008
    Posts
    6

    Default Re: Vlookup only pulling first example of duplicates in a column

    Thank you. I apologize for not providing screen shots. I typically try to do that where I can, but I can't seem to get any of the programs to work that will allow me to do screenshots (maybe because I am using Excel 2010 now?). I uploaded the file to http://www.box.net/shared/7pgpge2btj. There are several tabs. The tab that has the data is CS Week Data for Pareto. The Pareto chart that is using the data is on another worksheet labeled Pareto Chart. You will be able to see what it is doing (duplicating the first name it sees when there are duplicate values) by looking at the x axis on the chart. I'm guessing there's something simple that can be used to do what I'm trying to do and I'm just not advanced enough to know. Your help is appreciated.

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,475

    Default Re: Vlookup only pulling first example of duplicates in a column

    christianna,

    Thank you for the workbook.

    I can not see/understand/come up with a solution for what you are trying to accomplish.

    Click on the Post Reply button, and just enter the word BUMP, and then click on the Submit Reply button, and someone else will assist you.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  5. #5
    New Member
    Join Date
    Jul 2008
    Posts
    6

    Default Re: Vlookup only pulling first example of duplicates in a column

    Bump

  6. #6
    Board Regular Haseeb Avarakkan's Avatar
    Join Date
    Sep 2010
    Location
    India
    Posts
    901

    Default Re: Vlookup only pulling first example of duplicates in a column

    Try in D4; copy down,

    Array Formula, Conformed with CTRL+SHIFT+ENTER,

    =INDEX($B$4:$B$23,SMALL(IF($A$4:$A$23=E4,ROW($A$4:$A$23)-MIN(ROW($A$4:$A$23))+1),COUNTIF(E$4:E4,E4)))

  7. #7
    New Member
    Join Date
    Jul 2008
    Posts
    6

    Default Re: Vlookup only pulling first example of duplicates in a column

    Nope. That didn't work.

  8. #8
    Board Regular Haseeb Avarakkan's Avatar
    Join Date
    Sep 2010
    Location
    India
    Posts
    901

    Default Re: Vlookup only pulling first example of duplicates in a column

    This is an Array Formula, must hit CTRL+SHIFT+ENTER, not just ENTER

    This is the output i got, D4 copy down

    CS Week Data for Pareto

    DE
    2Defect Types/
    Event Occurrence
    TOTAL
    3

    4Interruptions (someone wanting info)34
    5Software (not working)28
    6Missing Parts (don't know why)15
    7Changeovers (unscheduled)15
    8Missing Info (what to work on)12
    9Documentation (not there)12
    10Unkitted Parts (here not pulled)9
    11Documentation (not accurate)7
    12Consumables not available7
    13Searching for tools6
    14Expedite - facilities5
    15Expedite - orders5
    16Documentation (not complete)3
    17Stock-outs (parts not ordered)2
    18Documentation (not available )2
    19Unscheduled Meeting1
    20Defective Parts / Material1
    Excel 2007

    Array Formulas
    CellFormula
    D4=INDEX($B$4:$B$23,SMALL(IF($A$4:$A$23=E4,ROW($A$4:$A$23)-MIN(ROW($A$4:$A$23))+1),COUNTIF(E$4:E4,E4)))
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself



    Is this do you mean?

  9. #9
    New Member
    Join Date
    Jul 2008
    Posts
    6

    Default Re: Vlookup only pulling first example of duplicates in a column

    I thought I was hitting that, but I wasn't. User error.

    Thank you soooo much. I would have NEVER gotten that one fixed on my own.

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