Tuesday, May 21, 2013

yogi_Match Abbreviated Name In Sheet Named Names And Pull Corresponding Full Name From Sheet Named Master

                                          Google Spreadsheet   Post  #1204
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 21, 2013
user StudentSupport (http://productforums.google.com/forum/?zx=ttx1g8rgpf8v#!category-topic/docs/spreadsheets/Gipvrzc2vnQ)
renaming names to reflect master list
Last step then I'm done...

While replacing a name, if there are 6 "Tom B"'s it is inputting ALL 6 Tom's rather than leaving that cell blank, as an error, and moving onto the next name to keep the rows aligned with the master list

I am currently using:

=ArrayFormula(query('master'!A:A;"select A where A starts with '"&left(names!B2,len(names!B2)-1)&"' "))

I would like to essentially use the same formula, but adjust it a bit so if there are 2 Tom B's, or 2 Danny A's that it will skip that name/cell, and move to the next name it can identify.

Any suggestions? Here is a link to the spreadsheet which I am using as my dummy data...

following is a convoluted solution to the problem