Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
532
questions
1
vote
1answer
14 views
Ways to iterate and append a date calculation a set number of times in Google Sheets
Let's say I have a sheet with this dataset:
Start Date Number of Days
3/1/2019 2
From this input set, I want to make the following transformation:
Date
3/1/2019
3/2/2019
3/3/2019
A ...
0
votes
0answers
27 views
Google Sheets trying to combine and un-combine text join
Ok, Unfortunatly due to security I cannot share the page, but I am using the following codes
=ARRAY_CONSTRAIN(
ARRAYFORMULA(iferror(
TEXTJOIN(",",TRUE,unique(IF(($D4=Section!C$2:AW$2),Section!C$3:...
0
votes
0answers
12 views
Query matching two columns to three columns
I have 5 Columns divided in two groups.
The first column of the first contains the name of a fruit (Pear, apple or peach) and the second its price (1 -> 5). In the other group, I also have the name ...
0
votes
0answers
18 views
UNIQUE ,INDEX AND IMPORTRANGE
I am stuck. I use the import range to get a set of data however now I need to the unique values that that matches the import range that I have.
Say for example below is randome sample. I have import ...
1
vote
1answer
35 views
Count query based on multiple conditions
I have a spreadsheet that I use for my World of Warcraft guild, and I've hit a bit of a wall while trying to add new functionality to it.
For reference, when our guild goes through dungeons, we ...
0
votes
0answers
17 views
Query Import Range returns two rows
I've been using =Query(Importrange( functions for quite a while now and something I can't figure out is why the import is returning with two rows. Here's the formula I've been using:
=QUERY(...
0
votes
0answers
9 views
Moving Rows of Google Form Responses to a new Sheet based on a specific answer in a column
I have a Google Sheet collecting responses from a Google Form. Based on answers in Column C (Col3), I would like the entire row imported to a completely different Google Sheet.
I figured out a ...
0
votes
0answers
9 views
Change entire sheet information based on input of one cell
I'm trying to create a function on a secondary sheet to monitor which of my blogs are getting the most traffic. Currently my secondary sheet has a formula that works to show me blogs that have over ...
2
votes
1answer
26 views
How to automatically calculate averages from ID in Google Sheet?
I've been looking around for an answer but couldn't find what I was looking for.
The problem is that I want to use an arrayformula, but can't figure out a dynamic formula that:
Looks at the unique ...
1
vote
1answer
12 views
help with query finding multiple entries in 1 column
I am trying to query several different colors from one column. This is what I have so far:
=query('Raw Data'!C1:J1000,
"Select E, D, I, J, C, F, H
where H contains 'Monday'
and F='Green'
...
0
votes
1answer
16 views
Google Sheets: Formula to get data from other tab
I have a Google Sheet to get an overview of all workshops/events we are hosting.
There is a tab INPUT. There is a column for Date, Month, Event, Location, Presenter, Language. The column month is ...
0
votes
0answers
8 views
Google Sheets data not loading in pdf
I have a Google Sheets with some tabs, but not yet a massive amount of information.
I have one tab where data is put in and then two tabs where this data is being displayed by a query formula. This ...
1
vote
0answers
10 views
Using a query on an importrange, not pulling information
I am trying to do a query on importrange but it is only showing me the headers not any of the data. I am really stuck.
=QUERY(IMPORTRANGE("19Nx2EtMktFW-xwNMjY4IiQpN33QHrXIwcqDsvKqCiHo", "MASTER!A1:...
1
vote
0answers
29 views
Multiple columns and get unique on preferred column
I want to get 3 columns only if a preferred column is unique.
What is the correct syntax in Google Sheets?
=Query('Sales'!A:AX,"Select A, B, C where A is unique")
1
vote
2answers
47 views
Select All Columns Where Cell In Columns Contain A Word - Google Sheets
I am looking for a query formula that will allow me to select all columns (entire column) that contain a specific word within the column.
You can see a test sheet here: https://docs.google.com/...
2
votes
1answer
26 views
Google Sheets Query: Making the selected column depend on formula
I would like to use a normal QUERY with where operating in the usual way where A = 'Speed' but with the selected column being determined based on a formula. Specifically, I want to return only the ...
2
votes
2answers
49 views
How can I filter a list based on whether it contains any of a list of substrings?
I have a list of Japanese words. I want to produce a formula to filter that list, based on a filter list of substrings (kanji characters) which I'm looking for.
So for example, say my filter list ...
0
votes
0answers
26 views
Loading Data Error Query with Importrange Gsheet
I am trying to use QUERY with importrange function to fetch custom data from another spreadsheet, it worked for a while but now it through loading data error consistently. if I refresh the sheet it ...
0
votes
1answer
14 views
Counting with Query
I am using queries, in order to create a dashboard,
=query('Usuarios icorp'!I2:M522; "count(F) when F contains '"&B3&"' ", -1)
I am having issues with my query function.
I want to count how ...
0
votes
1answer
21 views
In Google Sheets I need to find a string and return true/false, or even a count into a cell so I can perform an action
In my example i have a column with Names of signs we product in our sighshop and I need to be able to isolate similar named signs So say I need to return a a value if the cell is Bud Light and only ...
0
votes
0answers
26 views
Unable to filter rows in a sheet populated by QUERY
I am trying to filter one column A-Z to pair items listed in more than one selling app.
However, if I apply an A-Z filter for a column with item codes, the sheet rearranges everything per the filter ...
1
vote
1answer
37 views
Can I use “countIFS” with a filter?
https://docs.google.com/spreadsheets/d/1PlPmShZ4SnkxAqBTg5xY_LHaNokGaFli4Wc7MjV_Jqo/edit?usp=sharing
I want to count how many paid is for city MB and then how many paid is for city UB.
I tried to ...
1
vote
0answers
36 views
Google Sheets - Query Multiple Columns for same Data
We have Employee training data in a Google 'Form Response' spreadsheet which ranges from A1 to JQ1000. This spreadsheet is automatically populated when a Google Form is completed but it is difficult ...
0
votes
1answer
39 views
Import Form data into a sheet
I am trying to accomplish something that may not be possible. My work rents equipment to multiple companies and my boss wants me to setup a "database" of rental equipment and where its located and any ...
1
vote
1answer
34 views
Why does close price results have a date shift (one day off) while using GOOGLEFINANCE in a Query
I want to get last 40 days close price of stocks.
I used below formula for the same but the result not showing the last day close price.
=QUERY(
GOOGLEFINANCE("nse:"&B2,"all",$A$2-80,81),...
2
votes
2answers
86 views
Query or Filter formula to 'search for' and 'return' multiple words
I am a basic user of Google Sheets and need help creating a new formula. I am trying to filter a worksheet so that it returns particular information into another sheet.
The filter I have created is ...
1
vote
1answer
77 views
Adding missing dates to Google Sheets
I'm pulling data from HubSpot to Google Sheets via 3rd party connector. I have a raw data set on Sheet 1 and then I'm pulling certain data from Sheet 1 to other Sheets with QUERY function.
For ...
0
votes
1answer
44 views
Include filename of source spreadsheet
I have 2 spreadsheets for 2 employees and I combine them into 1.
From employee 1:
From employee 2:
I use importrange to combine the sheets. I also know now how to get the spreadsheet file name by ...
1
vote
1answer
20 views
Output counted list with extra qualifier
I currently have a table with the following columns:
Comm ID Item Name Months Orichalcum Tempest Jade Green Iron Weltsilver Ambergelt Beggar's Lye Dragonbone Iridescent Gloaming ...
1
vote
1answer
22 views
How to combine same value for Google sheet query?
I am trying to create a separate tab in my Google sheet where I can filter the Trainer to see which days they will be conducting a Day 1 and Day 2 Training.
Day 1 Training:
=iferror(
query('...
2
votes
1answer
59 views
Dynamically reference data from multiple tabs and sort it
I've got a spreadsheet with multiple tabs of data. The data contains 2 columns, A = Categories, B = Quantity.
I've created a copy of the spreadsheet HERE (please use file > copy) if you would like ...
0
votes
1answer
58 views
How to sort Column B to match with column A in Google Sheet?
I have 3 columns in Google Sheet.
Column A have 95 rows with value.
Column B and C have 500 rows with value:
I need to sort Column B to match with Column A.
Column C represent value associated with ...
1
vote
1answer
59 views
Filter the row containing an exact word and highest value in Google Sheets
I have a table (Master sheet) in Google Sheets that looks something like this:
https://ethercalc.org/t8f0ze0kyx6a
I need to filter out an entire row of information on a separate sheet (Last Lesson) ...
2
votes
1answer
32 views
How to sum the values of same vlookup results?
I have this formula it gets the single data from another file from a sheet at column 7.
=ARRAYFORMULA(
if(
len($A2:$A);
iferror(
vlookup(
...
0
votes
1answer
21 views
Importing answers from a Google Form into another Google Sheet, then adding a column for notes
I have been using a Google Spreadsheet with a master list created from a Google Form. I created a new tab to rearrange the order of the entries to the newest ones on top.
However after those cells ...
1
vote
1answer
42 views
Summarise data in a pivot table, but exclude certain data if it has a certain value in a certain column
I've got some data in a Google Drive spreadsheet that I want to run a pivot table on. There are 3 columns Element, Cost, Omit as per screenshot below
I want to summarise the cost by element, but ...
0
votes
1answer
24 views
Cell Reference in a QUERY statement not working
I'm trying to add a cell reference (on the same sheet where the QUERY function is at) to a QUERY function
On B1 there is this value: 13/09/2019
QUERY function:
=QUERY(FORMULARIO!A1:S, "SELECT G ...
3
votes
1answer
48 views
Importing a row from one Google Sheet to another
Doc1: I have individual sheets setup for each Game Week which list the football matches being played on that particular week.
Doc2: I have an individual sheet setup for each team.
I'm looking to pull ...
0
votes
3answers
57 views
How to use Importrange in a Query and filter by dates
I am trying to IMPORTRANGE if the condition in column1 that is text formated ( Column1 = September 4, 2019 alas 5:10 pm ) contains the word September.
I am using the following formula. The other ...
0
votes
1answer
25 views
Trying to make a column of “select count” in reference to another column
I am pulling data from multiple sheets in my workbook to create an array. In the first part of the array I pull data straight from a sheet like a user name # of points and rank. Now for the rest of ...
0
votes
1answer
33 views
How to combine 3 sheets into one using QUERY(IMPORTRANGE)?
I am trying to combine 3 different sheets of data from range A2:O Col1, Col4, Col5, Col6, Col8, Col11, Col12, Col13, Col14, Col15 using Query({IMPORTRANGE(... function. I tried using the formula below ...
1
vote
1answer
40 views
QUERY function to search three columns for the same keyword?
I have a table with data in columns A-U. Rows go down to 368.
I would like to pull (onto a different sheet) all the information in columns A-F where it matches a keyword from a dropdown menu (in cell ...
0
votes
2answers
56 views
Extracting last match from a string with REGEXEXTRACT
I've created a simple text filter:
=IFERROR(REGEXEXTRACT(A1;"- May 2019| - Jun 2019|- Jul 2019| - Aug 2019|- Sep 2019"); FALSE)
It should extract the month and year for an event from the cell A1. A1 ...
0
votes
1answer
25 views
How to find the last value in a column when the column is populated from an ARRAYFORMULA
Some columns in my sheet are filled manually, but then two columns are created dynamically from an ARRAYFORMULA (i.e. there is nothing in the column if the manually populated cells are empty, but once ...
1
vote
3answers
81 views
How to use QUERY to show data in Google Sheet using tags
I have a Google Sheet with 2 sheets. In sheet 01 I have this table.
In sheet 02 I want to have a "Search by Tag" sheet. So, googling I got this formula
=QUERY('01'!A2:D4, "select B,C,D where A = '...
0
votes
1answer
25 views
Merging cells based on same ID but divided into categories (columns)
I receive a pretty extent list of material that has been delivered and every single product is in a separate row. That means for one order I have easily a lot over 15 rows and I need to merge the ...
0
votes
2answers
25 views
Show values from only rows that have a checkbox ticked
I have a data table with groups as column headers and names and emails as rows. I want to export data to another spreadsheet in the same document, which would only display members who belong to a ...
0
votes
2answers
35 views
Using TODAY function with QUERY
I am using this formula (see below) to try and retrieve dates automatically between the current date and the next. However, I keep getting the error, "QUERY completed with an empty output."
=...
0
votes
2answers
126 views
Consolidate columns from a dynamic range into a single column in Google Sheets
How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case, I want non-unique cells. Also, each cell in the range ...
0
votes
2answers
31 views
Showing top 5 most frequent values and counts of each from a data range
I have a Google sheet with text strings listed down columns D through G. I want to, on another sheet, display a list of the top, say, 5 most frequent unique values from the first sheet in descending ...
