+ Reply to Thread
Results 1 to 4 of 4

Google Sheets query function replacement

  1. #1
    Registered User
    Join Date
    04-26-2019
    Location
    London
    MS-Off Ver
    365
    Posts
    8

    Google Sheets query function replacement

    Hi,

    I am moving away from Google Suite and to using 360 and so am in the process of moving all my google sheets to excel. In the most part it has been simple (especially with the help of people on here) with a few formula adjustments but I've come unstuck with the query function.

    Ill start at the beginning.

    I have a large sheet with evaluation data. There is a question where delegates are asked to write comments.

    I would like to sort the data so in another sheet only the comments that have been made on each row that both relate to a particular trainer (named in column V:V) and within a date range or particular month (Column R:R) are displayed.

    in google sheets (I was able to use =query(Exported!$V$1:$AV$998,"select AJ where V = 'Trainer Name'") although admittedly I hadn't worked out how to also filter by month.

    Any ideas of how I can replicate this so i can basically sort the data automatically by Month and Trainer name

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Google Sheets query function replacement

    It would be helpful if you could upload an example workbook that contains sample data (with sensitive information removed), and expected results. To upload a file, click Go Advanced (below the quick reply box), then Manage Attachments (scroll down a bit to find it). Click Browse to find your file, click Open, then click Upload. Click Close this window, and your file will be attached when you submit your reply.
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  3. #3
    Registered User
    Join Date
    04-26-2019
    Location
    London
    MS-Off Ver
    365
    Posts
    8

    Re: Google Sheets query function replacement

    Hi,

    Here is a a google sheet that shows what I need (I cant post a link apparently as ive not posted enough yet, so I've taken the hhtps bit out.

    docs.google.com/spreadsheets/d/1XDx4TzU0izfm_ISyY0Ba4-ElmlG1htm1JGfaLWaRIjQ/edit?usp=sharing[/url]

    The data is in the first tab and then I want to be able to filter the data onto the next tab by Trainer Name

    I also need to eventually be able to include a date filter so I can have 12 tabs, one for each month and then only pull the data that relates to a particular question, trainer name and month

    The query functions are all along row 8 in the attached google sheet

    I've attached the excel version also which just imports the info but not working formula
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Google Sheets query function replacement

    One of the caveats to this answer is that your data as is needs some adjustment. There are some merged columns that will need to be unmerged in order for this to work. I'll provide the formula for column A on the February sheet, and then we can go from there if it gets the results you're looking for. Please also note that I am not doing any error trapping, so there will be #NUM! errors, but those are expected for now. In February!A8, entered as an array formula with ctrl+shift+enter:
    Please Login or Register  to view this content.
    Give that shot, and then we can move on from there if you need additional assistance.

+ 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. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM
  2. [SOLVED] Converting Google Sheets QUERY to Correct Excel Formula
    By davis909 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-17-2017, 09:14 AM
  3. Google Sheets QUERY How can I do the same in Excel?
    By Ziggy21 in forum Excel General
    Replies: 12
    Last Post: 10-04-2015, 07:54 PM
  4. [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
  5. 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
  6. 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
  7. 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

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