vlookup duplicate data? - Page 2
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: vlookup duplicate data?

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

    Default

     
    Aladin
    What if your data in column A is text rather than numbers, this is giving me an error return when I try it
    regards
    Derek

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

    Default

    OK, other solutions I know but this is how I approach these.

    It needs a little knowledge of the data so is not entirely general but you can make it so. With the given example, where the numbers are small positive integers, it is relatively simple.

    You have two ranges no doubt, call them numRange for the 20,39 etc. and textRange for the animals. The approach is to order them but introduce non-disturbing noise into that. So:

    {=LARGE(numRange,{1;2;3;4;5;6})}

    Gives you numbers, duplicates and all, but,

    {=INDEX(TextRange,MATCH(LARGE(numRange+ROW(numRange)/100,{1;2;3;4;5;6}),numRange+ROW(numRange)/100,0))}

    Does the text. By adding the 'ROW(numRange)/100' part you've simply broken up the equalities.

    You can replace 100 by ROWS(numRange) if necessary and you can create a column of integers by {=ROW(OFFSET(INDIRECT("A1"),0,0,n))} for artitrary n.

  3. #13
    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-09 08:01, Derek wrote:
    Aladin
    What if your data in column A is text rather than numbers, this is giving me an error return when I try it
    regards
    Derek
    The target system of formulas applies to the text case too. It requires modification (indicated in bold below) at those places where the data type of A is exploited.

    Consider

    {"Field1","Field2";
    "xza","cat";
    "sad","dog";
    "xza","cow";
    "sad","fox";
    "ret","rat";
    "sad","mouse"}

    in A1:B7.

    In D1 enter:

    =MATCH(REPT("z",20),Sheet1!A:A)-ROW(1:1)

    In D2 enter: sad [ a lookup value ]

    In E2 enter:

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

    In E3 enter and copy down:

    =IF(AND(LEN($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)),"")

    Here is what you get in the results area:

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

    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