Results 1 to 4 of 4

Thread: Identifying/Indexing by partial text string

  1. #1

    Question Identifying/Indexing by partial text string



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi - I've got two large files of records which I'll call "Applications" and "Accounts" for a single customer base. The Applications file has about 1000 records and the Account File about 10,000 records. The only common information between the two files is Customer Name but they are in very different formats. The Applications file has the name in the format FirstName LastName, while the Accounts file has probably every different permutation and combination of (Mr/Ms/Dr) First Name, Middle Initial, Middle Name, Last Name, Jr. or other suffix or joint account holder. I've attached a small file showing the Applications and Accounts setups as separate tabs. The Account file is too large to convert text to columns and scrub for the extraneous data and so I was thinking that if I added two columns to the Applications file using the @LEFT and @RIGHT functions, I could get the first 5 letters and last 5 letters of each client name (column D & E, Applications) and then search the Account records for cells that contain both of those text strings and return the cell values for Account # and Monitoring (Column D & B, Accounts) to the appropriate cells in Applications. I'm presuming that an INDEX function would be involved but, using the Bruce Springsteen line as an example, I can't figure out how to essentially say "Search Accounts array for any cell that contains both "Bruce" and "steen". If you find such a cell, return the value in column B in that row to cell F2 and the value in Column D for that row to cell G2. If there is no cell that contains both "Bruce" and "steen", return "Not seen" in either or both cells F2 and G2"

    I also see that there are both given names and surnames that are fewer than 5 characters, which can result in cells like "a Fey" for Tina Fey, "Amy P" for Amy Poehler, which will give me issues if the Account Customer Name is "Tina Louise Fey" or "Amy Ann Poehler" as in the file. I can adjust the number of characters in the @Left and @Right functions to see if it needs to be 5 letters or would work with fewer, but on the assumption that it needs 5 letters on each of @Left and @Right, is there a way to have the @Right and @Left functions pull the fewer of 5 letters or the number of letters before reaching a space?

    Thanks in advance for any guidance you can provide,

    Kevin
    Attached Files Attached Files

  2. #2
    just for name examples, and I dont know if suitable for all cases name combinations, for account name using array formula, for onitoring just using VLOOUKUP formula.
    Attached Files Attached Files

  3. #3
    Quote Originally Posted by Rizky View Post
    just for name examples, and I dont know if suitable for all cases name combinations, for account name using array formula, for onitoring just using VLOOUKUP formula.
    Thanks for the quick response! I really appreciate it. I don't fully understand what you've done, but I'll go through it step by step.

    Thanks again,

    Kevin

  4. #4
    Quote Originally Posted by Bob Caygeon View Post
    Thanks for the quick response! I really appreciate it. I don't fully understand what you've done, but I'll go through it step by step.

    Thanks again,

    Kevin
    Rizky - Thanks again for your suggestion, which I appreciate. I've posed the question a little differently above in a thread called "Using IF statements as identifiers in an Index function" as I think I need something that I can understand a little more. If you wish to, have a look at that thread.

    Regards,

    Kevin

Tags for this Thread

Posting Permissions

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