Tuesday, April 9, 2013

yogi_Pull Data From Select Columns Of Student Roster For A Specified Student


                                          Google Spreadsheet   Post  #1102
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 9, 2013
user BeeCon :(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/RJysoNHjelk)
Convert a keyword search macro into workable script
Hi guys, I am keeping at this. I've swtiched from macros/scripts to functions. I could really use some help writing this as I think its too advanced for me.
I have tried: VLOOKUP, QUERY, FILTER functions which I can't get to work.

I have created a sheet with fake data to express what I want to do with expected results.
Heres a link:
https://docs.google.com/a/watertown.k12.ma.us/spreadsheet/ccc?key=0AhElu_CbtWnFdFlmRmVSUS1VaWw2RDlmQ2dqTW9Qb3c#gid=0

there are many hidden columns which on the real sheet will also have data I would like copied to my 'Expected Results' section. I have hidden these columns for simplicity.

Any ideas?
BRIZZLE!!!

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

5 comments:

  1. That is fantastic! You make it look so easy Yogi!

    Follow up question: Can the QUERY also search additional sheets? Such as Data1,Data2,Data3...all for the same "B2" text

    ReplyDelete
  2. Hi Bee Lini:

    To use the QUERY for multiple sheets, it will be best to first merge the data from multiple sheets in to a single sheet named say Alldata and then QUERY Alldata.

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

    ReplyDelete
  3. If I merge all the data to one sheet, isn't then the query function no more than just a filter?

    I'm looking for a function that can search different sheets based upon a name: Searching through Data, Data2, Data3 and all the rows, and in this example returning A,B,AC,AD,AE where A= B2....Is this more powerful function possible Yogi?

    ReplyDelete
  4. Hi Bee Lini:

    If I merge all the data to one sheet, isn't then the query function no more than just a filter?
    NO.

    I'm looking for a function that can search different sheets based upon a name: Searching through Data, Data2, Data3 and all the rows, and in this example returning A,B,AC,AD,AE where A= B2....Is this more powerful function possible Yogi?
    YES.

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

    ReplyDelete
  5. I have continued to work on the spreadsheet from above. I have added dummy data for an additional sheet called 'Data2', and I have also updated the expected results section. The new sheet 'Multi Sheet' are my attempts at the updated function.

    I can't seem to figure out how to string more than 1 query together. I must be missing something small (or very very large). Can you take a look?

    ReplyDelete