Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
756
questions
1
vote
1answer
18 views
Compare imported range with existing range, and filling corresponded data to the fields
I am not sure how to import data to corresponding cells.
query(IMPORTDATA("https://www.iftach.org/taxmatrix/charts/4Q2020.csv"), "Select Col4 where Col1 = '"&A4:A&"'&...
-1
votes
0answers
18 views
My Regexmatch isn't working for one specific row
My formula should be looking through the green table to identify any rows that match the yellow heading of either nurse, addiction professional, etc. Then if it finds a match it should be coming back ...
0
votes
1answer
18 views
Needing to change a column to false when Data Validation is changed
I am using Google sheets and have a cell that has data validation and I am putting check boxes (I4:I) for people when items are received, they need to be changed to "False" when the Data ...
0
votes
1answer
11 views
Need help filtering entire rows from reference sheet to master sheet where column text meets criteria
I feel like this should be a relatively simple issue but everything I write is clearly incorrect or leaves me needing to do work manually every time new data is made.
Basically I have a series of ...
0
votes
1answer
19 views
Google Sheets: Joined queries registering as two arguments in IFERROR() function
I'm joining two queries together using an array: ={query(A1:B,"Select A where B is not null");query(C1:D,"Select C where D is not null")}. I have it wrapped in an iferror() that, ...
0
votes
1answer
36 views
QUERY syntax using cell reference more than 1
I'm trying to use a cell reference instead of static values.
=QUERY(Responses!B1:I, "Select * where G = '"&B:B&"'")
I use code like this because the data is varchar, but ...
0
votes
1answer
21 views
Using query as a text join
A few days ago I reach the limit of 50000 characters in one cell, I found a solution with "query" but I need a delimiter inside the formula that I haven't been able to find:
This is the ...
0
votes
1answer
32 views
Google SpreadSheets query: how to join “grouped by” cells in a list?
Suppose I have this table:
| Name | Amount | Category |
| name_1 | 10 | A |
| name_2 | 2 | B |
| name_3 | 3 | B |
| name_4 | 6 | C |
Thanks to this ...
2
votes
1answer
32 views
Need to sum the number of instances of texts in a column using Query function in Google Sheets
Still a noob in Google Sheets. I am trying to create a sheet with the statistics with the data coming from Sales sheet . I want to sum the quantity of all items sold according to sizes. For ex. How ...
0
votes
1answer
15 views
How to modify Query Options no_format to allow some format, but not others?
I have to replicate a Google Sheet, and am using ImportRange and Query together. I need Query also because the first sheet has urls under them, which I cannot have on the copied sheet. I was able to ...
1
vote
1answer
17 views
If cell in Col A is blank substitute with cell in Col B
I'm attempting to combine two columns from another sheet into one through the QUERY function. My plan is to make it so if a cell in Col A is empty then a cell in Col B will be substituted.
However, ...
0
votes
1answer
32 views
Combine data from multiple sheets to create a stacked line chart
I have multiple sheets with three columns each: Date, Invested Amt, and Return Amt. I am trying to merge the values and get a Result dataset to create a single stacked line chart of Invested vs Return ...
1
vote
1answer
32 views
How to stack two columns in Google Sheet with column name in another column?
I have these columns in the Google Sheet.
Date A Date B
1/1/2020 3 5/2/2021 328.28
1/2/2020 41 5/3/2021 322.75
1/9/2020 23 5/25/2021 342
1/12/2020 54 5/28/2021 ...
0
votes
1answer
22 views
Cannot get Query to display time
I need help getting to show the result of a dropdown. If you look at C22, there's a dropdown for a specific date. I'm currently working on row 24 and have applied the formulas I think should work. I ...
0
votes
2answers
31 views
How to make variables dynamic in ARRAYFORMULA/QUERY Combination
I'm trying to self populate the first column of the "Main" sheet with a query that returns the first column of the "Finance Office" sheet that matches all three names.
I came up ...
0
votes
0answers
23 views
Google Sheets data autofill issue =Query? =Vlookup?
I'm attempting to autofill a budget spreadsheet, based on a paychecks paydate, filling the bills due that pay period.
Bills 1-30
Pay Dates ranging from 1-30
I'm hoping to be able to plug in the Pay ...
0
votes
0answers
31 views
What is the Google Sheet query for getting rows excluding non matching rows
The tables will have multiple entries for amount debited and credited details. It also has 5% debit and credit and 1% debit and credit entries as well. Data includes unique flag for districts, Date ...
1
vote
1answer
43 views
What's the formula to select non matching rows by comparing rows in Google Sheets query?
In Google Sheets query,
I need to get only non matching debit rows by comparing rows say if amount debited (S1 flag - row 1) matches with amount credited (S1 flag - row 3), it needs to be excluded in ...
0
votes
1answer
19 views
Filtering views based on a cell having a value
I am trying to sort out a spreadsheet which has multiple columns in but don't really know where to start!
I am looking to filter the sheet where column C has more than 3 errors for the ID on column B.
...
1
vote
1answer
33 views
How to use use cell references in the WHERE part of a QUERY / IMPORTRANGE formula
Can you tell me what's wrong with my formula?
= QUERY (IMPORTRANGE ("url googlesheets database"; "Data range"); "SELECT Col1 WHERE Col1 =" A3 "AND Col2 =" B3 "")
The "A3" and "B3&...
0
votes
1answer
36 views
How to have a query wrapped in an arrayformula auto-populate many rows
I have a query that queries over a set of rows. I want to run this query, itself, for each row in another set of rows. The one value that changes in the query for each row is something in the where ...
1
vote
1answer
27 views
Google Sheets - COUNTIFS with timestamp
I have a column in a table that contains the date and time. This is the form
15.06.2020 09:43:24
17.06.2020 08:21:04
18.06.2020 12:11:34
or also
2021-01-21 11:57:34
2021-01-21 11:58:02
2021-01-21 11:...
1
vote
1answer
20 views
No query result when one of the columns used in the output never has a value
I have a query where the output involves summing two columns. If either of the columns never has a value (given a set of filter criteria in the where clause), there is no result. Why?
Here is an ...
0
votes
0answers
23 views
Google Sheets Checkbox Query/Pivot/Other Question
For my pizza hobby I keep track of the pizzas I like to make and the ingredients in them. I have a good sheet with the following data.
I would like to make a view of the same data where I can see ...
0
votes
1answer
20 views
How do I chart timeseries data organized by one of the columns?
Here's what seems to be a simple common thing in excel/sheets that I can't seem to be able to google in the right way. three columns: timestamp, email, things_done.
I want a chart with time as the X ...
-1
votes
1answer
34 views
how to select and automate “copy by value” to persist data in Google Sheet
I would like to use Google Sheets as a form to query for data, select and store the data I'm interested in.
Below what I'd like to try. Please let me know if feasible, and if so, could you tell how to?...
1
vote
3answers
48 views
'contains' and 'starts with' cannot match text with a character after a hyphen
Google Sheets queries cannot match text using contains or starts with when there the text to match contains a hyphen, and the hyphen is not the last character in the text to match. Am I doing ...
3
votes
2answers
125 views
cannot drag query across multiple rows (overwrites data)
Is it possible to click and drag queries?
I describe my problem below and also have a reference sheet.
cells A1:B5
Category
Withdrawal
CatA
4
CatB
2
CatA
6
CatB
11
cells C1:C3
Check For
CatA
...
0
votes
1answer
34 views
Best way to change dates in Google Sheets in all the tabs?
I have a sheet that is for accountability in my business, I have each month with it's own tab with five week sections and the dates are done by a Monday thru Sunday sections with the date that is ...
0
votes
2answers
33 views
Why doesn't “format A 'yyyy-mm' options no_values” strip the day from the output?
I have some input that contains a set of dates. I want to strip the day from the input and sort by just the month and year. I know there are other ways to do this, but why can't I set the format to ...
1
vote
1answer
46 views
Google Sheets: VLOOKUP formula for a multi line cells range
I have here a sample project, where columns A to C are in FORM RESPONSES TAB/SHEET and columns D and E are in RECORDS TAB/SHEET.
In the RECORDS I have a list of names, in alphabetical order, and a ...
0
votes
1answer
15 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 ...
2
votes
1answer
73 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
15 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
92 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
58 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
36 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
32 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
13 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
9 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
22 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
75 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 ...
3
votes
1answer
23 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 ...
0
votes
0answers
19 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
20 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
24 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
52 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
45 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
34 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
134 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 ...
