vlookup duplicate data?
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: vlookup duplicate data?

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    If I have 2 columns as follows:

    20 cat
    39 dog
    40 cow
    39 fox
    50 rat
    39 mouse

    Is there a way I can lookup "39" and return the first 39=dog and the next 39=fox and the next 39=mouse etc..?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Change the reference of VLOOKUP's table_array to exclude the previously found item.

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was hoping I could keep the table array constant. Is there another way around this problem?

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Louisville, Ohio
    Posts
    248
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am not sure if this helps you, but you may be able to use "AutoFilter" under the menu...

    Data
    Filter
    Autofilter

    This would allow you to show only the items that have "39" in the first column.

    The only other idea that I have is to write some VBA code to ask the user for a value like "39" and then have the VBA code find all cells in column A that have "39" and write the results in some other cells. This should not be too dificult.

    I hope this helps at least a little.

    David

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-08 09:21, pleasehelpme wrote:
    I was hoping I could keep the table array constant. Is there another way around this problem?
    You could setup your table array like...

    {20,"cat",1
    ;39,"dog",2
    ;40,"cow",3
    ;39,"fox",4
    ;50,"rat",5
    ;39,"mouse",6}

    ...and enter the array formula...

    {=VLOOKUP(39,OFFSET(table_array,F1,),{2,3},0)}

    ...into cells E2:F2 (leaving F1 blank) and copying down to cells E4:F4.

    Note 1: Substitute an absolute cell reference "table_array"

    Note 2: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

    [ This Message was edited by: Mark W. on 2002-04-08 09:33 ]

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-08 09:03, pleasehelpme wrote:
    If I have 2 columns as follows:

    20 cat
    39 dog
    40 cow
    39 fox
    50 rat
    39 mouse

    Is there a way I can lookup "39" and return the first 39=dog and the next 39=fox and the next 39=mouse etc..?
    Lets say that A1:B7 houses the sample data you provided, with labels added:

    {"Field1","Field2";
    20,"cat";
    39,"dog";
    40,"cow";
    39,"fox";
    50,"rat";
    39,"mouse"}

    In D1 enter:

    =MATCH(9.99999999999999E+307,Sheet1!A:A)-ROW(1:1)

    This formula computes the number of actual data records in the data range dynamically.

    In D2 enter: 39 [ which is your lookup value ]

    In E2 enter:

    =IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$2,2),2,0),"")

    In E3 enter and copy down until no more retrieval occurs, associated with the value in D2:

    =IF(AND($D$2,COUNTIF(OFFSET($A$2,0,0,$D$1,1),$D$2)>COUNTA($E$2:E2)),INDEX(OFFSET($B$2,MATCH(E2,OFFSET($B$2,0,0,$D$1,1),0),0,$D$1,1),MATCH($D$2,OFFSET($A$2,MATCH(E2,OFFSET( $B$2,0,0,$D$1,1),0),0,$D$1,1),0)),"")

    This is what you're going to see in the results area:

    {6,"";
    39,"dog";
    "","fox";
    "","mouse"}


    Aladin

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for that Aladin,
    I am most impressed, however there are 2 points that may be of use:

    Point 1/ correct me if I am wrong, but should part of your last message have read:

    =IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$1,2),2,0),"")

    rather than:

    =IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$2,2),2,0),"")


    Point 2/Is it possible to possible to overcome the problem of having duplicate data in "Field 2". For example, if we add on to the bottom of the existing data:

    39, "rat"
    39, "fox"

    The results do NOT give:
    {6,"";
    39,"dog";
    "","fox";
    "","mouse";
    "","rat";
    "","fox"}

    but instead give:
    {6,"";
    39,"dog";
    "","fox";
    "","mouse";
    "","rat";
    "","mouse"}

    Many thanks
    Nick

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi


    If you have a look here: http://www.ozgrid.com/VBA/TwoColLkUp.htm you will find this UDF with full instructions.


    Code:
    Function FindNth(Table As Range, Val1 As Variant,Val1Occrnce As Integer, _
                      Val2 As Variant,Val2Col As Integer, ResultCol As Integer)
    
    '''''''''''''''''''''''''''''''''''''''
    'Written by OzGrid Business Applications
    'www.ozgrid.com
    
    'Finds the N'th value in the first Column of a table that has a stated _
     value on the same row in another Column.
    '''''''''''''''''''''''''''''''''''''''
    
    Dim i As Integer
    Dim iCount As Integer
    Dim rCol As Range
    
    	For i = 1 To Table.Rows.Count
    		If Table.Cells(i, 1) = Val1 And _
    			Table.Cells(i, Val2Col) = Val2 Then
    			iCount = iCount + 1
    		End If
    
    		If iCount = Val1Occrnce Then
    			FindNth = Table.Cells(i, ResultCol)
    			Exit For
    		End If
    	Next i
    End Function


  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi There

    There is a very simple solution to this type of problem.

    There are many elaborations you can make but the basic idea is this:
    You insert a column with a formula which sequences the item you want to list.
    You then do a vlookup on this column. Try this on your example

    Put your numbers in Column B starting with B2 (keep A1 & A2 blank)
    Put your names (cat, dog etc) in Column C starting with C2
    In A2 paste this formula and scroll down =IF(B2=$E$1,1+COUNT($A$1:A1),"")
    In E2 paste this formula and scroll down =VLOOKUP(ROW(E1),A:C,3,FALSE)
    Now type 39 in cell E1 and the names associated with 39 will list below

    If you want you can modify the formula so that #N/A does not show. Just change the formula in E2 to
    =IF(COUNT(A:A)>=ROW(E1),VLOOKUP(ROW(E1),A:C,3,FALSE),"")

    It is also possible, with modification, to obtain the answers from data in another sheet

    Regards

    Derek


    [ This Message was edited by: Derek on 2002-04-09 07:39 ]

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

      

    Thanks for that Aladin,
    I am most impressed,


    Thanks. It's mutual. Happy to see that you understood the system.

    however there are 2 points that may be of use:

    Point 1/ correct me if I am wrong, but should part of your last message have read:

    =IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$1,2),2,0),"")

    rather than:

    =IF($D$2,VLOOKUP($D$2,OFFSET($A$2,0,0,$D$2,2),2,0),"")


    Yep. Just visually confounded D1 & D2. I should have put that MATCH formula in C2 to avoid that visual trap..


    Point 2/Is it possible to possible to overcome the problem of having duplicate data in "Field 2". For example, if we add on to the bottom of the existing data:

    39, "rat"
    39, "fox"

    The results do NOT give:
    {6,"";
    39,"dog";
    "","fox";
    "","mouse";
    "","rat";
    "","fox"}

    but instead give:
    {6,"";
    39,"dog";
    "","fox";
    "","mouse";
    "","rat";
    "","mouse"}


    That's right. The formulas assume the uniqueness of values in column B. The addition boils down to having duplicate records. You could eliminate such duplicate records using Advanced Filter.

    Aladin

User Tag List

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