Wednesday, October 10, 2012

Google Spreadsheet Filter data from two sheets to third sheet

Question:



I have a sales data spreadsheet with 3 sheets.  In the 3rd sheet, I am trying to extract data from the other 2 sheets based on date and name.  For example:
Sheet 1
DATE   |      NAME    |    PHONE
9/9/2012     Stephen      206-999-9999 
9/10/2012   Marcus       360-999-9999
9/11/2012   Kishan        253-999-9999
Sheet 2
NAME    |    NOTES
Stephen      deliver to home address
Marcus        needs 3 orders
Kishan        is the best support ever
What I am trying to do is pull data from Sheets 1 and 2 into Sheet 3 based on date and name. In Sheet 3, I want to enter a date in one cell, then based on that date populate the rest of the spreadsheet using data from Sheets1 and 2.
For example, Sheet 3 looks like this:
A1 9/9/2012 (manually enter)
A2  NAME  (set header)
A3  I need a formula that would look at Sheet 1, find each row with the date 9/9/2012 (in the date column), then populate cells A3, A4, A5... (depending how many sales there was on that date) with all the customer names (in the name column) that placed orders on that date
B2  PHONE (set header)
B3  I need a formula that based on the name in A3, A4, A5... in Sheet 3, would look at Sheet 1, find each name (in the name column), then populate cells B3, B4, B5... with the phone numbers of that particular customer (in the phone column) that placed an order on that date
C2  NOTES  (set header)
C3  I need a formula that based on the name in A3, A4, A5... in Sheet 3, would look at Sheet 2, find each name (in the name column), then populate cells C3, C4, C5... with the notes (in the notes column) that are associated with that name
Can you please help me with this?
Thank you!
Stephen



Solution:


Following is the Screenshot of Sheet1:




Following is the Screenshot of Sheet2:




Following is the Screenshot of Sheet3:




Put the following formula in Cell A3 of Sheet3:
=FILTER('Sheet1'!B2:C;'Sheet1'!A2:A=A1)

Put the following formula in Cell C3 of Sheet3:
=FILTER('Sheet2'!B2:B;MATCH('Sheet2'!A2:A;A3:A))



I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,


11 comments:

  1. Hi Kishan,

    Thank you for the quick reply and your help! The first formula you provided worked perfectly after I tweaked it to =FILTER('Sheet1'!B2:B;'Sheet1'!A2:A=A1). However, I am having difficulty getting the phone and notes data to carry over using your formula. Can you revisit your formula that’s needed in Sheet 3 for cells B3 and C3?

    Thank you!

    Stephen

    ReplyDelete
    Replies
    1. Hi StephenSeattle,

      Note you have to put the formulas in Cell A3 and C3
      Cell B3 will automatically fetched from formula in Cell A3.

      Kishan.

      Delete
  2. Hi Kishan,

    Just checking in to see if you are able to assist me with the final 2 formulas?

    Thank you and kind regards,

    Stephen

    ReplyDelete
  3. Hi Kishan,

    Our messages just crossed paths! I retried your formula for Sheet 2, C3, and it still didn't work.

    What if the Name is in Column A of Sheet 2, the Phone number is in Column B of Sheet 2, and the Notes are in Column G of Sheet 2? What should be the formual to bring the phone and notes into Sheet 3, based on the name in Column A of Sheets 2?

    Thanks,

    Stephen

    ReplyDelete
    Replies
    1. Hi Stephen,

      It would be better if you can make a copy of your spreadsheet and share it with me... then it will become easy for me to provide you the solution.

      Thanks,
      Kishan.

      Delete
    2. if you are having the Notes are in Column G of Sheet 2..

      then try the following formula for it:

      =FILTER('Sheet2'!G2:G;MATCH('Sheet2'!A2:A;A3:A))

      Delete
  4. Thank you, Kishan.

    How do I get my spreadsheet to you?

    Stephen

    ReplyDelete
  5. Hi Kishan,

    Thank you for the reply, and I apologize for not getting back to you sooner. It's been a crazy day!

    I think I’m confusing you, and I apologize for that, too. What I am attempting to do is import data from several columns in Sheet 1 (named 'Sales Data') and Sheet 2 (named 'Customer Database') that would show up in Sheet 3 (named 'Press Orders'), with all the data being imported based on the matching name from the Sales Data sheet. Then, data in the Customer Database sheet, with the matching name from the Sales Data sheet, would be imported in to the Press Orders sheet, too.

    For example, I have the names coming over into the Press Orders sheet perfectly based on the formula =FILTER('Sales Data'!B6:B;'Sales Data'!C6:C=A3). But there are currently 8 more columns from both Sales Data and Customer Database that I need brought over (again, based on the matching names listed with the date I had entered at the top of the Press Orders sheet.

    I need to bring over data in columns A (name), E, F, H, I from the Sales Data sheet, then data from columns B, G, H from the Customer Database based on the matching name /date from Sales Data.

    Have I completely confused you now? What would a formula look like to bring in data from all of those columns in the Customer Database sheet to the Press Orders sheet, but have the data all based on the matching name? I would think I could replicate such a formula to bring over the rest of the data in the Sales Data sheet.

    I sure wish I was more familiar with Google Docs, but I’m a newbie who has been given this project and the formulas are so different from Excel. I was just trying to make my original question I posted simple, and I think I failed in doing so.

    Thank you for your patience and your help.

    Kind regards,

    Stephen

    ReplyDelete
    Replies
    1. Hi Stephen,

      I would request you to share your spreadsheet with me which has some realistic data..

      You can share it with me on my email Id: kishan.pionero@gmail.com

      Sharing it would make it easy for me to work on it and provide you the solution quickly.

      Thanks,
      Kishan.

      Delete
  6. Hi Kishan,

    Did you happen to receive my e-mail I sent to your Gmail e-mail address last Sunday? I had a couple final questions to the share spreadsheet. I'll resend the e-mail now.

    Thanks for your assistance,

    Stephen

    ReplyDelete