Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
733
questions
0
votes
1answer
9 views
How to find closest similar value above current value, and return a different column on the same row
I'm looking for a way to find the closest similar value(in distance) above the current value(column B), and return the column next to that found value(column C) in column D.
A sort of vlookup where it ...
1
vote
1answer
44 views
GOOGLEFINANCE in Google Sheets to get high & low and timestamp of high/low from the day I took trade
I wanted to keep a track of high/low of a trade from the day I took the trade.
For a day I am able to get high/low but how to get from the date I took my trade which is stored in one of cell.
=IFERROR(...
1
vote
0answers
12 views
How to add date criteria to query function? [duplicate]
I am using query function to copy all data from sheet1!A2:B10 to Sheet2 if B column is equal to specific data/time.
Lets say A1 = 5 April 2021
A2:A10 names
B2:B10 has Timestamp
And I used this formula
...
0
votes
1answer
50 views
How to run a google sheets query against a dynamic range
Background
I have a spreadsheet that displays data internet consumption by users that looks like this
userid feb1 feb2 feb3 . feb29
u1 100 34 23 . 4
u2 ...
0
votes
2answers
26 views
Why does google sheets query stop working on 6 decimals (but works on 5)?
I was creating a google sheets query that's pretty simple:
query(<range>,"select Col2 where Col1 contains <number with decimals>")
I noticed that it works when the number with ...
0
votes
1answer
28 views
Concat columns from query result in Google Sheets
I have a sheet that has a column with labels in a single cell. A cell can contain 1 or more labels, each separated by a comma.
+-----------+
| Labels |
+-----------+
| Foo,Bar |
| Bar,Baz |
| ...
0
votes
0answers
19 views
Gsheets Query Importrange not giving expected results
I'm having an issue with the Query(Importrange) function of Google Sheets.
I'm using the following formula
=Query(IMPORTRANGE('Importrangelink,"'Sheetname'!X1:AA130"),"select Col1,Col2,...
0
votes
1answer
12 views
Get row reference from validated drop down list
So I have two sheets, masterIngredientList and shoppingList. I have data validation in my shoppingList which is bound to a column in the masterIngredientList.
In the master ingredient sheet, there are ...
0
votes
0answers
6 views
Add more numbers in Vertical axis
Ideally, I'd like it to have percentages of 10. 0% - 10% - 20% - 30%, etc.
But I'm not finding a way to customize this.
Can anyone help?
0
votes
1answer
21 views
How may I add an average count of rows by weekday to this example Google Sheets
Here is an example Google Sheet that summarizes Col A, D by weekday totals and counts.
I would like to add an Average number of items by Weekday, like K9 L9 but show it in formula K1.
There are two(2) ...
1
vote
1answer
42 views
Google Sheet split cell to rows - data manipulation
I have a sheet, where there is a list of times in Col. C.
What I want is for it to be split into different rows & also the relevant order number & name to be copied with the rows.
Resulting ...
2
votes
1answer
16 views
The QUERY is giving double results on a single cell
I recently started to use QUERYs on Google Sheets. I just encountered a problem where after typing my query
=QUERY('Copy of TEST'!A1:E7,"select A, sum(D)
group by A")
it is compounding the ...
-1
votes
0answers
15 views
Extracting data between date range in Google Sheets
I have some sales data in Google Sheets. The first column is the date e.g. 3/18/2021 (in column A) of each sale & other columns include customer info and the value of each sale (column X). The ...
0
votes
0answers
17 views
Query in Google Sheets with OR, using dropdown menu
I'm trying to pull data from the first tab, which collects data from a Google Form (regarding school subjects).
In the second tab, I have a dropdown menu in cell A3 with all subjects.
Say I choose &...
0
votes
0answers
19 views
Query - Procv with 2 Conditions
I'm trying to do a query with 2 conditions:
=ArrayFormula({"User"\A1:F1;QUERY(SE.ERRO({PROCV(B2:B&F2:F;G2:H;2;FALSO)\A2:F});"Select * Where Col1 Is Not Null Order By Col1 Asc")}...
1
vote
0answers
17 views
How can I use a dropdown menu in Google Sheets filter out data and populate cells for a an expense tracker?
I have created a personal expense tracker (budget) that uses google form responses and categorizes them by fortnights and expenditure categories. This has been successful on a sheet called 'Yearly ...
0
votes
2answers
36 views
Formula parse error on importrange query
I'm trying to recreate what's happening in this video at 7:17
https://youtu.be/5S7laJS9meU?t=437
I want to merge two sheets and remove the empty rows.
=QUERY({IMPORTRANGE(L2 ,"sheet1!A2:F20")...
0
votes
1answer
20 views
How to reference named range as sheet name in query function?
=QUERY(ActiveMonth!A7:L,"select A,D where K matches 'Admission|Pending'",0)
I am using sheets name/tabs (January, February.. December).
ActiveMonth is a namedRange of a cell containing the ...
1
vote
1answer
18 views
How to reference a cell outside range using query function in Google Sheets
I'm trying to copy cells B to F to another sheet if the text in cell J contains specific text. As an example case, I'm using the query function as:
=query('Sheet 1'!B1:J,"Select * Where J ...
0
votes
2answers
44 views
How to make the cells of query function results with borders that are dynamic according to rows/column numbers?
https://docs.google.com/spreadsheets/d/1VsEvt2KnYGnlN_qHjkjPohVHF5MUqfyHD7tSk0VpkqA/edit?usp=sharing
I would like to make the cells that have query results in All_Patients tab with borders instead of ...
1
vote
2answers
37 views
How do i use the query in google sheets to return N results from each tab
How do I use the query in Google Sheets to return N results from each tab.
So I have this formula:
=QUERY({sheet1!A2:D;sheet2!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3")
...
1
vote
3answers
29 views
Count instances of character in a column
Is there a way to count instances of characters down a column, without creating further columns that do row-by-row counts of each character?
Below is an outline of what I'm trying to do.
Note that no ...
0
votes
0answers
18 views
Auto-generate unique sheets when you add a new row in Google Sheets?
I'm a fairly new user to Google Sheets, I'm trying to set up a sheet whereby when you add a new line in one sheet, it creates a separate check-list sheet. So when a new user is added to the master ...
1
vote
1answer
35 views
Combine multiple IF statement/condition into one Google Sheet column
How can I achieve in Google Sheet so that the column will auto populate the correct "vendor" based on the selection on "model" and "size". Refer below:
There will be 2 ...
0
votes
0answers
12 views
Group By, multiple times?
I'm in the midst of creating some code to query my data.
Here's an example of said data:
Text version of image
Name
ToF
AOF
Jon Cena
Buyer
1
Tim Brown
Seller
3
Cabel Guy
Buyer
5
Cabel Guy
...
1
vote
1answer
25 views
How to use a query to find a row of same name but last entry, then copy a value next to it (update request)
(This is an updated version of this question - I am trying my best to make myself clearer this time.)
I am trying to use a query to copy values from one column to another.
Both source and destination ...
0
votes
1answer
15 views
How to extract desired text from a cell
As you can see below I have these data in single cell I want only car nos as my result
2
votes
1answer
61 views
How to use a query to find a row of same name but last entry, then copy a value next to it
I am trying to use a query to copy values from one column to another.
Both source and destination columns have a name on their left columns, and the name must match. The source names could be ...
0
votes
0answers
13 views
Allocate budgets into daily levels based on date range
Is there a way in Google Sheets to allocate cost/units evenly into daily breakouts if a total is entered with start date and end date? Ideally this would happen through a Google Sheets query. I've ...
2
votes
1answer
43 views
Counting rows based on the date it has and the current end of the week
Problem to solve:
Given 2 rows, containing the author's name and a date, you have to count how many rows that author has.
The tricky part here is that I have to use the date row and check how many ...
1
vote
1answer
20 views
Problem with results of filter function from tabs that have no data yet
I made a master sheet that combines all data from 12 tabs within the workbook sheet. These tabs use query functions and importrange from other sheets. Some of sheets have no data yet like May 2021 ...
0
votes
2answers
37 views
Change all unique country abbreviations to country names and place in one cell
I need a formula that can add all the countries with that is in a column.
Example:
In sheet 'Export' I have the below countries of origin:
And in other sheet I need to show all the countries of ...
0
votes
1answer
45 views
Look up rate for specific person in effect on or after a date
In this Google Sheet, I have one sheet, "Transactions", with a rate entry that should be auto populated from a table in the "Rates" sheet. The formula should look up the rate for ...
0
votes
1answer
44 views
How to use FLATTEN function in query function
Below in my query result:
In the above table "Form Response" table is the response received in Google Sheets from Google Form.
I want to convert "Form Response" table to "...
1
vote
2answers
54 views
Select Unique on 1 Column in Google Sheets - Distinct Doesn't
I'm trying to set up a kind of a bid thing, where the top 5 respondents receive a prize. I was hoping to use SQL to get the top 5, but I can't remove duplicate bidders and only take their top bid. I ...
1
vote
1answer
22 views
How to create an average value for every cell which contains a certain phrase?
I need to find the average value of cells containing a certain set of initials to create a scoreboard.
The value of the cell being the score and the brackets containing the initials of the score ...
0
votes
1answer
26 views
Query using starts with
A little help to do the following:
I am good with Queries however, can't find if it is possible to do the following:
=Query(range, "Select * where B begins with (**want a range from A to Can**) ...
1
vote
1answer
38 views
Can a formula cell reference be a variable cell reference?
Assuming I insert every day some rows into Sheet1, this formula stops working and I have to manually edit every day --- can the reference to Cell A2 and Cell E2 be a variable that remains hardcoded as ...
0
votes
1answer
28 views
How to use query with a criteria where Col1 is equal to a value in a cell?
https://docs.google.com/spreadsheets/d/1kYkfBoDsJR-sBo5J52lJTXKa-EXoFYPgVPnrRGvOx5A/edit?usp=sharing
=query(Importrange("1kYkfBoDsJR-sBo5J52lJTXKa-EXoFYPgVPnrRGvOx5A","LastRevision!A5:...
0
votes
1answer
26 views
How to overwrite or change the value of a cell that has the results of query function if it meets a new criteria?
=query(CASES,"select C,E,F,G,H,I,J,K,L,P,Q,R where Q matches 'Admission|Pending|Transferred' AND R is null ORDER BY R ASC ",0)
I use this formula to filter data to a sheet. Then I want to ...
0
votes
1answer
16 views
ORDER BY in QUERY functions of Google does not work at all
Please consider this sheet:
https://docs.google.com/spreadsheets/d/1qIbyvtr2OpjD25OxeMjId1PbtVKmcI6V_KvCJqzs7U8/edit#gid=151995974
In the TestSelect Sheet, I've entered 2 QUERYformulas:
in A1, I've ...
1
vote
2answers
42 views
How to get value from cell with query and importrange functions in Google Sheets?
A1 cell has 1. And I try use value from this cell through:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit?usp=sharing"; "Sheet1!A:P");
"...
0
votes
1answer
39 views
Query + Importrange functions together
I'm trying to get range from different sheet IDs. But getting error message.
=query({IMPORTRANGE($X$5,”CASES LIST!B9:T58”);IMPORTRANGE($X$6,”CASES LIST!B9:T58”);IMPORTRANGE($X$7,”CASES LIST!B9:T58”);...
1
vote
0answers
19 views
How to generate URL from cell contents on entering values
I have column A with values such as
345722
456922
71881
I want to generate URLs from these numbers like https://example.com/id/xxxx. I want the column values to be updated and the generated URL to ...
1
vote
0answers
22 views
Combine two separate, working queries
Trying to combine two separate, working queries, so both can be used on a single sheet.
The idea is a User selects Data for a Resource (sheet TAB), and then either: a Region (Column D) or a County (...
1
vote
1answer
17 views
Fill in missing dates in Google Sheet query
I have a Google Sheet query like this:
=QUERY(A2:I;"select I, sum(C) * -1, count(C) group by I order by I ASC"; 1)
And it yield a result like this:
In the source data not all months are ...
1
vote
1answer
22 views
Why doesn't Query pull up Data when using Where Function to reference a number
I am relatively new to using the Query function but I would like to understand what I'm doing wrong in the following scenario.
I would like to pull from a table with a Year field in column F and Month ...
0
votes
1answer
35 views
Using query function with (matches) empty cell
=query(A1:S10,"select C,D,E,F,H,K,P,R,S WHERE Q='USA' AND R matches 'Florida|NY|'''
I would like to add (or empty cell) to the 'Florida|NY'
That is to say I would like to select rows with the Q= ...
1
vote
1answer
81 views
Using query function with ' where Col1 or Col2 is not null'?
=query({TOIM;IMTO},"SELECT * where Col1 is not null ORDER by Col11",0)
Where TOIM,IMTO are named ranges.
I want to rewrite the formula with two criteria (OR). In other words like this:
=...
0
votes
1answer
34 views
How can gaps be quantified within a combined set of variable arrays?
I'm using Google Sheets to build a crop planning tool for farmers (kinda like a gantt chart). I need help quantifying the number of days within a given timeframe that no crops are planted in a bed. In ...
