Bob Caygeon
New member
- Joined
- Sep 27, 2015
- Messages
- 5
- Reaction score
- 0
- Points
- 0
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
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