+ Reply to Thread
Results 1 to 13 of 13

Google Sheets QUERY How can I do the same in Excel?

  1. #1
    Registered User
    Join Date
    04-19-2012
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2010
    Posts
    23

    Google Sheets QUERY How can I do the same in Excel?

    Hi

    I am using the following query in Google Sheets to import rows of data from my 'RawData' sheet to my 'Week01' sales sheet...

    =QUERY(RawData!A2:P10001,"Select A,E,F,H,J,L,O where I<>'Declined' and P= date '"&TEXT(B2,"yyy-MM-dd")&"'")

    used in 'ID' columns, row 4

    I want to achieve the same thing in Excel but I am really not sure where to start.

    Thanks in advance for your time and help

    Sheet attached
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Google Sheets QUERY How can I do the same in Excel?

    I will take a look for you

    2 things while I am looking...

    1. wow those colors are hard on the eyes
    2. Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile says 2003, but your file indicates at least 2007
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Google Sheets QUERY How can I do the same in Excel?

    hmm OK what exactly are you trying to do there?

  4. #4
    Registered User
    Join Date
    04-19-2012
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Google Sheets QUERY How can I do the same in Excel?

    Sorry here is a bit more explanation

    The query selects data in the A,E,F,H,J,L,O columns on the RawData sheet based on checking the row wasn't 'declined' in column I and the date in Column P matches the date in cell B2 in the Week01 sheet

    I am using Office 2010

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Google Sheets QUERY How can I do the same in Excel?

    Im thinking taht something like this should work...
    =INDEX(RawData!$A:$A,SMALL(IF(RawData!$I$2:$I$1000<>"declined",ROW(RawData!$A$2:$A$1000)),ROWS($A$1:A1)))

    This is an ARRAY formula...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    But I am having a hard time making it work becasue of the UK/US date forma difference, plus you have no July data in your sample anyway

  6. #6
    Registered User
    Join Date
    04-19-2012
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Google Sheets QUERY How can I do the same in Excel?

    Great thx I will try that, if you change the Week 01 in B1 to Week 10 it should hit the active data, just in case you fancied going back to it : ) . but ty for you help. Sorry you don't like my colour coding

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Google Sheets QUERY How can I do the same in Excel?

    I did try changing to week 10, but the data format is throwing me off. It will be quicker for you to test, than for me to adjust things to US date format.

    Also, I left out teh date check in my formula...
    =INDEX(RawData!$A:$A,SMALL(IF((RawData!$I$2:$I$1000<>"declined")*(RawData!$P$2:$P$1000>=Week01!$B$2),ROW(RawData!$A$2:$A$1000)),ROWS($A$1:A1)))
    still CSE entered

  8. #8
    Registered User
    Join Date
    04-19-2012
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Google Sheets QUERY How can I do the same in Excel?

    Doesn't this just import a single cell?, I need to import cells from the A,E,F,H,J,L,O columns if the row meets the not 'declined' and date match criteria. It should import all rows that meet the criteria

    Sorry really tired can't seem to find the words to explain what I am trying to do. Thanks again for your time
    Last edited by Ziggy21; 10-03-2015 at 07:58 PM.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Google Sheets QUERY How can I do the same in Excel?

    yes, it does pull into a single cell. once we have that, we can progress to the others.

    I dont use google sheets, so I dont know how the QUERY works, but generally, excel formulas only work on/in 1 cell, and only affect the cell they reside it

    Sorry really tired can't seem to find the words to explain what I am trying to do.
    Then dont try to explain, show some sample expected answers?

  10. #10
    Registered User
    Join Date
    04-19-2012
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Google Sheets QUERY How can I do the same in Excel?

    Good Idea

    Here is another sheet with the results pasted in (in a colour scheme you may like). I just did a couple of days although I had the the query in each day section on the Week01 Sheet
    Attached Files Attached Files

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Google Sheets QUERY How can I do the same in Excel?

    1. make sure your headings match exactly (eg you have Postcode on RawData, but Post Code on Week01)
    2. in Raw sheet, change the P formula to =IFERROR(DATEVALUE(D2),"") to remove the errors from row 201 onwards
    3. Try this ARRAY formula, copied down and across...
    =IFERROR(INDEX(RawData!$A:$P,SMALL(IF((RawData!$P$2:$P$1000>=Week01!$B$2)*(RawData!$J$2:$J$1000<>"Declined"),ROW(RawData!$A$2:$A$1000)),ROWS($A$1:A1)),MATCH(B$3,RawData!$A$1:$P$1,0)),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

  12. #12
    Registered User
    Join Date
    04-19-2012
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Google Sheets QUERY How can I do the same in Excel?

    Hi

    Thanks so much for your time on this, it's nearly there, but doesn't seem to be seeing the date, I tried switching date formats around but that didn't seem to do anything. any further thoughts ?
    Attached Files Attached Files

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Google Sheets QUERY How can I do the same in Excel?

    All the other dates look to be before the date in week01 B2, so they are being excluded.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Syncing Data from Google Sheets to Excel
    By CSPENCER in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 08-21-2015, 04:54 PM
  2. [SOLVED] Converting Google Sheets document with query to Excel
    By playmate in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-04-2015, 10:18 PM
  3. Google Sheets Query IF column problem Converted to Excel Function?
    By Garthilk in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-24-2015, 10:37 PM
  4. Uploading an Excel File with Macros in Google Sheets
    By Jim_Johnson in forum Excel General
    Replies: 1
    Last Post: 02-12-2015, 04:58 PM
  5. query w/ minimum requirement in google sheets
    By alansoftpublisher in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 12-14-2014, 04:59 AM
  6. Query function from google docs in excel 2011, anyone with a solution?
    By emilero in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-12-2012, 04:04 PM
  7. Replies: 0
    Last Post: 10-15-2012, 05:18 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1