Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Something of a reverse VLOOKUP (97)

    I have a database that will eventually be quite large (1500 rows X 50 columns). The first column contains unique numbers (Part Numbers) in ascending order. What I would like to do is search the entire worksheet for any cells that contain a particular piece of information and retrieve the list of Part Numbers that have that piece of information in one of their row cells (could be there one or more times in a variety of columns for any given Part). I don't have any idea how to begin on this one. Any help would be most appreciated.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Something of a reverse VLOOKUP (97)

    To get the ball rolling, here's one possibility. I think I've interpreted your situation correctly and I think my solution is fairly straightforward - enter the search string in a1 and filter column L on 1's. Of course, that formula is going to get pretty long when you adapt it for 50 columns.....

    Brooke
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Something of a reverse VLOOKUP (97)

    Another workaround that will require no coding is to use the Freeware utility ASAP Utilities from <A target="_blank" HREF=http://www.asap-utilities.com>http://www.asap-utilities.com</A>.

    It has a conditional select facility that will allow you to hide/show only the rows or columns meeting your search criteria.

    I hope this helps. (I am not sure if it operates fully under Excel97, so this may be a red herring...)

Posting Permissions

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