Tuesday, January 15, 2013

yogi_Return Multiple Instances From A Range Based on Specified Condition


                                          Google Spreadsheet  Post  #972
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 15, 2013
user Scatmanicus said:(http://productforums.google.com/forum/?zx=i1xirkbimh7#!category-topic/docs/spreadsheets/rKP72EWmG_w)
return multiple instances from a range 
Firefox on MS 7 64 bit

I have a need to return multiple occurrences of a row cell for each matching value.

Here is the range c2:d7 called 'Jodie'.
      C          D
2 Frank    1234
3 Jodie      2345
4 Sue       3456
5 Jodie     4567
6 Sam      5678
7 Jodie     6789

I need to have it returned link this
      A           B
1  Jodie      2345
2                4567
3                6789
I can't use a simply IF statement as I don't want individual blank lines.  I've tried 'vlookup("Jodie",Jodie,2,False)' but it returns the 1st matching value on each line.  I found a routine that works for Excel athttp://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#horizont but the VBA code won't work in the Google Apps Script as I get a "Missing ; before statement. (line 1)".  My gut tells me the VBA has to be rewritten in JavaScript but that is above my pay grade. :)

Anyone have an idea how I can do this?

Luke

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