Sunday, March 31, 2013

yogi_Pull Select Data In Multiple Sets From Another Sheet Based On A Specified Criterion



                                          Google Spreadsheet   Post  #1090
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 31, 2013
user Stephen Seattle1 :(http://productforums.google.com/forum/?zx=h0o3wpzxfaq#!category-topic/docs/spreadsheets/o-FoyenUc1Y)
IF formula extracting data from another sheet

I have multiple sheets within one spreadsheet and I am trying to use formulas to extract data from one sheet to another based on the date of a transaction.  I’m stumped in figuring out the formula needed.



My spreadsheet can be found at the following link:




On the “Press Orders” Sheet, I am looking to import data from the "Sales Data" sheet to populate the columns based on the date that I enter in cell A4. You will note that I already entered a formula in the other date cells A12, A20, A28, and A36. The rows under each date section should be populated based on a match of the same date located in the “Sales Data” sheet under columns K and M.  For example, for the date 3/2/2013, there should be 2 orders populating just two of the rows in the “Press Orders” sheet under the 3/2/2013 heading (Mila Adamova and Amanda Adams, since they have 3/2 press dates listed in column K in the “Sales Data” sheet).



 So, based on the dates listed in columns K and M in the “Sales Data” sheet, I am then trying to do the same import process for the other sections (dates) on the “Press Orders” sheet (there are 5 date sections starting on rows 4, 12, 20, 28, and 36).



For each "Press Orders" sheet column, I am trying to import the data as follows.  Note all this data is from the same row/transaction in the “Sales Data” sheet:

1. Name – imported from column A in the ‘Sales Data’ sheet

3. Inv # - imported from column C in the ‘Sales Data’ sheet

4. Cleanse Type - imported from column E in the ‘Sales Data’ sheet

5. Pack Details - imported from column F in the ‘Sales Data’ sheet

6. # of Bottles - imported from column H in the ‘Sales Data’ sheet

7. Current Order Comments - imported from column G in the ‘Sales Data’ sheet

8. Shipping Method - imported from column I in the ‘Sales Data’ sheet

9. Shipping Address - imported from column Y in the ‘Sales Data’ sheet

10. Phone - imported from column U in the ‘Sales Data’ sheet



Can someone point me in the right direction?  If this doesn’t make any sense, please let me know and I can try and explain it better.



Thank you!

Stephen
---
I tried the following formula and it didn't work:  =ArrayFormula(IFERROR(QUERY(IF({1,1,1,1,1,1,1,1,0,1};'Sales Data'!A3:J;IF(INT('Sales Data'!K3:K)=A4;'Sales DATA'!L3:L;IF(INT('Sales Data'!M3:M)=A4;'Sales DATA'!N3:N;-1)));ʺselect Col1, Col10, Col3, Col5, Col6, Col8, Col7, Col9,Col24, Col20 where Col8 > -1ʺ)))
--------------------------------------------------------------------------------------------------------------------------------------


following is a solution to the problem

4 comments:

  1. Hi Yogi,

    Thank you! This is exactly the solution I am looking for. But when I try and replicate the syntax in my spreadsheet, I get a Parse Error message. I am assuming it has to do with the order of the " (quote) and ' (apostrope) in the formula. Can you elaborate on how I should type the formula?

    Thanks,
    Stephen

    ReplyDelete
  2. Hi Stephen:

    Where ' and " are used in combination then at start it is ' followed by " and at the end it is " followed by '

    You may also want to copy the formulas directly from the formulas sheet in my blog post or the following ...

    formula in cell A5 is:
    =query('Sales Data'!A$3:Y;"select A where K= date '"&text(A4+0,"yyyy-mm-dd")&"' ")
    this formula then needs to be copied to cells A13, A21, A29 etc.

    and

    formula in cell C5 is:
    =query('Sales Data'!A$3:Y;"select C,E,F,H,G,I,Y,U where K= date '"&text(A4+0,"yyyy-mm-dd")&"' ")

    I hope this helps.

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

    ReplyDelete
  3. Hi Yogi,

    Thank you for the clarification. The formulas work without error messages now. However, for each formula, they need to look (and match) the date in not only column K in the 'Sales Data' sheet, but also column M. For example, if a transaction has a matching date entry in either column K or M, then it should return the results under the applicable date on the 'Press Orders' sheet. So, for 3/8/2013, there would be 4 entries. Is this an easy tweak to the formulas?

    Thanks again!
    Stephen

    ReplyDelete
  4. Hi Yogi,

    Just wanted to check in to see if you received my last post?

    Also, I wanted to run another formula frustration I have been trying to figure out. I am also trying to return results to column K in the 'Press Orders' sheet. The results would be pulled from column M in the 'Customer Database' sheet based on a match of the name in column A (Press Orders sheet) to the same name located in column C (Customer Database sheet). Does this query make sense to you? Whew - it's so complicated! I hope you can help.

    Thank you,
    Stephen

    ReplyDelete