Questions tagged [google-sheets-query]

Use for questions about QUERY built-in function from Google Sheets.

Filter by
Sorted by
Tagged with
1
vote
1answer
45 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
25 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
44 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
42 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
34 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
52 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
43 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
139 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
37 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
22 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
25 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
31 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
50 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
117 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
127 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
80 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
46 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
89 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
19 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
89 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
167 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
106 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
44 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
43 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
13 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
126 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
20 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
27 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
74 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
79 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
79 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
246 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
52 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
30 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
21 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
54 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
14 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
34 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
18 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
66 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
46 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
24 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
242 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 ...

1
2
3 4 5
16