Saturday, May 4, 2013

yogi_Pull Select Information From A Table Based On Specified Fields (Columns)

                                          Google Spreadsheet   Post  #1170
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 04, 2013
user StudentSupport (http://productforums.google.com/forum/?zx=489otlptm5fw#!mydiscussions/docs/OsltWNvu2Dg)
I am trying to pull data, but with user selected input. If a user decides they want to view data from a certain column on another sheet then they can put an "x" in a cell. I have a helper column that helps write the formula prior to pulling the particular information.

="select C, D, E, F, G, H where B1:B="x"", which gives me a parse error.

Please view: https://docs.google.com/spreadsheet/ccc?key=0AvTqJBK5ZdjNdFRCV0Uxd3RZOXdVMTVtWmt2dDJSMHc#gid=0

----------------------------------------------------------------------------------------------
following is a solution based on my understanding of the problem

13 comments:

  1. Mr. Anand,

    I think K1:K9 is supposed to be B1:B9, so

    =query(index(Info!A:I);"select "&join(",","Col"&query(index(B1:B9);"select* where Col1 is not null ")))

    ----
    Then I receive the following error: Invalid query: Column [Colx] cannot appear more than once in SELECT

    ReplyDelete
  2. Hi Bee Lini:

    Sorry about my typo in writing the formula ... the solution I proposed works fine -- the correct formula is:

    =query(index(Info!A:I);"select "&join(",","Col"&query(index(if(B1:B9="x",row(B1:B9),));"select* where Col1 is not null ")))

    The blog post does show the formula correctly now. Let me know how it works out for you now.

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

    ReplyDelete
  3. Fantastic!

    Thank you for your solution, and thanks for corresponding back and fourth with me while I described my problem.

    ReplyDelete
  4. The solution works perfectly as was described. Can I add one additional piece of information to the formula? I thought I would be able to add this myself, but the formula became more complex than expected.

    A2 is now Data Validation, and it pulls all the student names from 'Info'. A user will select a specific name, with
    =query(index(Info!A:I);"select "&join(",","Col"&query(index(if(B1:B9="x",row(B1:B9),));"select* where Col1 is not null ")))....I just don't know where to put A2


    I updated Sheet1 incase you want to see expected results

    ReplyDelete
  5. Hi Bee Lini:

    Please update your spreadsheet so I can see where you have the Data Validation for Name ... currently cell A2 in your Sheet1 has Date (field name) in it -- so your Data Validation for Name can not be in Sheet1!A2 and has to be some place else.


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

    ReplyDelete
  6. Gosh, I'm sorry. I made the typo this time. Sheet1!B1 is the Data Validation cell

    ReplyDelete
  7. Hi Bee Lini:

    No ... cell B1 is being used for presence of x to check whether name field should be used in the QUERY output.

    If you need to have Data Validation in Sheet1 keep it outside of cells A1:B9, you can use any cell in column C, say cell C1 for example.

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

    ReplyDelete
  8. I removed B1 from being part of the presence of x. It now begins at B2. I have no problem changing it to C1 though.

    My follow up question is how do I incorporate the TEXT of a DV cell into the formula =query(index(Info!A:I);"select "&join(",","Col"&query(index(if(B2:B9="x",row(B2:B9),));"select* where Col1 is not null ")))

    So basically it filters/querys B1 for a name, and THEN it does the =query(index(Info!A:I);"select "&join(",","Col"&query(index(if(B2:B9="x",row(B2:B9),));"select* where Col1 is not null ")))

    ReplyDelete
  9. Hi Bee Lini:

    I have added a Data Validation list in cell C4 of yogi_Sheet2 -- see the solution for selecting QUERY output for select columns and selected Name -- see the solution in yogi_Sheet2.

    Let me know how it goes.

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

    ReplyDelete
  10. Yogi_Sheet1, and Sheet1 both reflect what I'm trying to do, I just cant figure out where to add B1 to the wonderful formula you created

    https://docs.google.com/spreadsheet/ccc?key=0AvTqJBK5ZdjNdFRCV0Uxd3RZOXdVMTVtWmt2dDJSMHc#gid=0

    ReplyDelete
  11. You my friend are a genius! Many many many thanks

    ReplyDelete
  12. You Are Very Welcome Bee Lini ... Now Let us Keep Googling.

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

    ReplyDelete
  13. Hi Yogi,

    After a few weeks of playing with the amazing data you assisted me, I am back for one final question.

    I was wondering if its possible to sort a start date, and an end date. The report will only show dates that fall in between start date/end date.

    I edited the Yogi_Sheet1 to give an example. Here is the link:
    https://docs.google.com/spreadsheet/ccc?key=0AvTqJBK5ZdjNdFRCV0Uxd3RZOXdVMTVtWmt2dDJSMHc#gid=2

    ReplyDelete