+ Reply to Thread
Results 1 to 5 of 5

Searching for Multiple Values

  1. #1
    Registered User
    Join Date
    04-18-2007
    Posts
    7

    Searching for Multiple Values

    I have two Excel worksheets. The first contains a complete list of User IDs (several hundred rows of numeric values listed in one column). The second contains specific IDs (around 80, same format), which must be removed from the first sheet.

    Is there a way to search for these values all at once, rather than using Find for each one individually?

    Any help would be greatly appreciated.

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by Templinho
    I have two Excel worksheets. The first contains a complete list of User IDs (several hundred rows of numeric values listed in one column). The second contains specific IDs (around 80, same format), which must be removed from the first sheet.

    Is there a way to search for these values all at once, rather than using Find for each one individually?

    Any help would be greatly appreciated.
    Hi,

    on the first sheet, in a spare column, run a VLookup to the second sheet to pickup if the item is to be removed, something like

    =If(IsError(Vlookup(A1,sheet2!A:A,1,False)),"",Vlookup(A1,sheet2!A:A,1,False))

    and then use the Data, Filter on that column to select all non-blanks and delete those.

    clear the column and the filter after the deletes.

    hth
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    04-18-2007
    Posts
    7

    Thumbs up Thanks

    That's great, thanks.

    There's also an IF statement I'm having some trouble with. I have two lists of numbers in adjacent columns. I want to find out whether or not each number in column 2 appears anywhere within column 1:

    So:

    =IF(B1=..., "True", "False")

    What is the correct way to specify this range of values? (e.g. cells A1 to A100).

  4. #4
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127
    =IF(ISERROR(MATCH(B1,$A$1:$A$100,0)),"FALSE","TRUE")

    Try that and let me know if it works

  5. #5
    Registered User
    Join Date
    04-18-2007
    Posts
    7

    Thanks

    Yeah, that works perfectly. Thanks guys.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1