Results 1 to 5 of 5
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Is it possible to search an Excel 2007 spreadsheet by searching for words that are in a TSV text file? For example, my spreadsheet's Column A is a list of 1,000 file names with paths. I want to find and, perhaps, filter (or otherwise segregate) on 500 file names that are in a separate text file. I should add that the names in my external file are in a different format. In the spreadsheet, the names are in a format of file://E:/folder/pics/myfile.jpg. In my separate list, the names appear as myfile.jpg. Basically, I want to find the file names, and I can bring the separate list into the same spreadsheet. Thanks.
    JimmyW
    Helena, MT

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Jimmy - Can you post a sample of your spreadsheet file and a sample of your TSV file. Just fill in about 10 rows of fake data on each example.

    It seems that a modified VLookup might work.

    Tim

  3. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Thanks, Tim. I attached the text file that contains a sample of file names that I want to find in the attached spreadsheet. Basically, I want to search only on file names, so a hit anywhere in a given record id okay. For example, I want to find MyFile.jpg if it exists in the spreadsheet as file://E:/folder/pics/myfile.jpg. I appreciate your help!
    Attached Files Attached Files
    JimmyW
    Helena, MT

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Jimmy - Take a look at the tab named "Source" on attached file. It looks like there is only one match (Cell A1) and the result is in cell B1.
    Is this what you are looking for? If you send more fake data that has more matches, I can try enhancing this for you.

    Tim
    Attached Files Attached Files

  5. #5
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Thanks very much, Tim! This will work, though I'm not really sure of what the formula is doing (it's a little complex for a modest user of funtions). If there's a way to step through the formula, perhaps I can follow along, just to learn, but I did run it against my data and it works very well. I appreciate your help very much.

    I just discovered the Evaluate Formula funtion - I'll give it a spin!
    JimmyW
    Helena, MT

Posting Permissions

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