Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Sorting & VLookup conflict (2003 SP2)

    Dear Loungers,

    My requirement is to have a list of peoples names(PEOPLE sheet), first and last are two fields, to be able to sort by first-last or last-first. In addition on another sheet (GROUPs sheet) I need to pick a person from a list and retrieve the relevant data for that person so it's conveniently together. This all seems doable but I keep bumping into small drawbacks howver I do it

    Option 1.

    I have tried this approach. I have a concatenated field constructed like this:

    =IF(AND(ISBLANK(B3),ISBLANK(C3)),"",B3&" "&C3)

    If I then do a sort on the column (A) the "blank "cells after the data (but with the formula in them) rise to the top of the list. Although the VLOOKUP works fine. This is shown in the file attached named: Contacts - group member details OK.xls

    Option 2.

    So I reverted to my original sort based on column B and column C rather than the concatenated column A. So I don't have the blank rows rising to the top BUT my VLOOKUP based on Column A no longer works. This is shown in the file attached named: Contacts - No group member details.xls

    The sort does this:
    Range("PeopleData").Select
    Selection.Sort _
    key1:=Range("FirstNameHdr"), _
    order1:=xlAscending, _
    key2:=Range("LastNameHdr"), _
    order2:=xlAscending, _
    header:=xlYes, _
    OrderCustom:=1, _
    MatchCase:=False, _
    Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, _
    DataOption2:=xlSortNormal
    PeopleData is defined by this: =OFFSET(Contacts.xls!FirstSortCell,0,0,COUNTA(Cont acts.xls!FirstSortCell:!$B$65534),13)

    Can anyone help me understand and suggest a way to do what I want?

    Incidently unless the list in column A is alphabetical the lookup fails, I seem to remember this is a limitation. Therefore if I sort Last-First the lookups only sometimes work. This seemsa little odd since all the values are unique. Is this as expected or am I doing something wrong?

    thank you.......... liz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting & VLookup conflict (2003 SP2)

    I'm not sure what is what, but you should add FALSE as fourth argument to your VLOOKUP formulas, e.g.

    =VLOOKUP($C4,ContactsList,4,FALSE)

    instead of

    =VLOOKUP($C4,ContactsList,4)

    The FALSE argument specifies that you're doing an exact lookup, not an approximate one. For exact lookups, the first column of the lookup table doesn't need to be sorted.

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Sorting & VLookup conflict (2003 SP2)

    Hans,

    Thank you.

    You and your expert colleagues have saved my bacon many times now. It's always difficult when I only use a tool's capability occassionally and I know that it does what's wanted but it's beyond most everyday users so I don't have anyone to ask,. And everyone already thinks that I'm an expert! I'm not (with excel, word is a differnt matter) but can usually find out how or know that it's possible.

    Anyway, very grateful for the lounger and its contributers...... liz

Posting Permissions

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