Sunday, July 24, 2011

yogi_Increment Data In One Sheet By Checking Against Existing Data In Another Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

MemberCarDan said:
Take data result from one sheet and check against existing in another sheet
One sheet - example2 - has an existing list of unique customer codes based on first two letters of last name, first letter of first name and then occurrence 01, 02, etc. (customer, Bobby Boatmaker would be BOB01).
Sheet example1 is where new customers get entered. I have worked out the formula to read the first and last names and convert to and ID code (not in example). What I can't get is:
I need to check my new customer ID code against the existing list (example2) and then 1) if there is no current entry, put the new code in OR 2) if there is an entry, read the entry and add the next occurrence (e.g., new customer is BOB, already have BOB04, so adds BOB05).
I've been trying to work it out with a VLOOKUP or query but I'm not good enough :( yet :). Any pointers appreciated.
I have assumed the ID codes in sheet named New are to be developed using first two characters of Last Name and first Character of First Name and then a numeric code is to be appended by checking against the ID codes that already exist as documented in sheet named Existing.