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...
https://docs.google.com/spreadsheet/ccc?key=0AvTqJBK5ZdjNdGpJS2tNZUY4bEY2cjRSWGNFZzBnUEE#gid=0

--------------------------------------------------------------------------------------------------
following is a convoluted solution to the problem

2 comments:

  1. I think I am doing something wrong. I apply the formula in cell A2 and in C2, and I drag it down as far as needed. It begins to work correctly, and then it errors and provides a wrong name. It is very possible I am entering it wrong. Could you take a look and provide feedback?

    ReplyDelete
  2. Hi Bee Lini:

    For your simplified requirements, I have updated the formula(s) both in my blog post as well as in your spreadsheet -- your specs have been changing all the time ... so make sure that you think through things well before settling on your specs.

    Make It A Great One
    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete