Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
1
vote
1answer
10 views
vlookup sum to calculate total hours assigned next to dates
I'm attempting to calculate a total number of hours based on entering 2 different dates. Each day between the dates has assigned 'hours worked'.
I'm trying to use Sumproduct or Arrayformula and Sumif,...
1
vote
1answer
20 views
How can I list rows meeting a criteria and limit the amount of rows by a sum of the list?
I am trying to create a scheduling assistant within Google Sheets. I need to list out tasks currently scheduled for a specific date but there is a volume cut off that needs to happen.
Client ...
0
votes
1answer
7 views
How to sum values of the same row from different columns in a separate sheet with QUERY?
I'm trying to add up the value of cells in the same row but different columns to another Google sheet.
Example below:
0
votes
2answers
39 views
QUERY function randomly and invisibly corrupts dates [on hold]
I found this BUG while trying to use MATCH using Queried data against the actual data.
This Google Spreadsheet shows the BUG in action.
When you format the data as Number the problem is fixed. This ...
0
votes
0answers
15 views
Using custom function in QUERY in Google Sheets results in “unknown function:”
I have a custom function written in Google Script for Sheets named sheetnames(). It returns the names of individual sheets inside a worksheet. When used in a regular cell-like =sheetnames(), it ...
0
votes
1answer
11 views
How to get a Sample Standard Deviation values that match condition?
I am trying to get the standard deviation of a subset of data. I have two sheets
Name Value
a 1
a 2
a 1
b 5
b 5
In a separate sheet I have:
Name StdDev
a
b
GOAL: I am trying ...
0
votes
1answer
17 views
Use of IMPORTRANGE to juxtapose variable-length lists
Let's say I have two Google sheets, Doc1 and Doc2, both having only one sheet featuring two lists with the same schema, say two columns Name and Age. The lists have an unknown number of entries, but ...
0
votes
1answer
29 views
How to count how often a value is submitted to a Google sheet via Google Forms per day, and put that count in different Google Sheets workbook
I have two Google Sheets workbooks, Workbook A and Workbook B.
Workbook A is generated from a Google form that collects email addresses submitted from users. Workbook A has two columns next to each ...
0
votes
2answers
20 views
Insert column into Google sheet generated from a Google form to calculate the difference between two columns of data
I have a Google form where people track their company mileage and input "Mileage In" and "Mileage Out."
I want to insert a column next to Mileage In and Mileage Out that shows the difference between ...
0
votes
2answers
16 views
Extract and graph information from a days-hours-minutes-seconds format [on hold]
I'm working in Google Sheets, and I have data for race finish times in "8d 3h 40m 13s" format and I'm want to extract that information so I can plot it in a chart. Any ideas for how to achieve this? ...
0
votes
2answers
12 views
How to count duplicate names as one value in Sheets?
I have a large dataset in Sheets and I'm grouping together the data in a pivot table and have a hard time figuring out how to avoid counting every instance of a name instead of counting the name as ...
0
votes
1answer
23 views
Count attendance percentage for top 10 specific events
Data
I have an attendance sheet for multiple events and for multiple people. The sheet looks like this (mock data):
X | A | B | C | D | E | F | G | ...
1 | |...
1
vote
1answer
26 views
Generate custom sequence in Google Sheets
I would like to create a column with a custom sequence: A-Z,a-z,0-9. (Essentially the Base64 character set with the last two characters, + and /, left out) So, A1 would be AAA, A2 would be AAB ... ...
1
vote
1answer
16 views
Count worked days per employee
I am creating a spreadsheet in Google Sheets for a client who wants the sheet to pull data from their Google Calendar to calculate how many days each employee works. They use Calendar for their ...
1
vote
1answer
12 views
Using Google Sheets to dynamically add entries to column based on logic?
I have a list of people that I have a drop down for if they are attending or not to an event.
If "Yes" I want them to be added to another column on sheet 2. Except I don't know how to add them ...
1
vote
1answer
21 views
Conditionally format rows with partial matches
I have a Google Sheets table that looks like this:
(col a) (col b) (col c) (col d)
Company Cohort Session Count Group
--------------------------------------------
Acme ...
1
vote
1answer
16 views
How to combine two QUERY results into one column
I have the following QUERY
=query(XX!A2:A;" select A where A>'50' ")
and
=query(YY!A2:A;" select A where A>'50' ")
how do I combine these two data sources so I don't have to have two ...
1
vote
2answers
25 views
Combining numbers based on a seperate cell
I'm trying to make a sheet that records every 'player's' balance. Transactions come from a form, and I was wondering if I could run a script that
Reviews each incoming data set
Adds an amount [what ...
0
votes
2answers
12 views
Query function problem
When using a query function that selects two columns from another sheet, I get two results in one cell. It looks like it doesn't respect the initial structure of the selected data. Any idea, please?
2
votes
2answers
48 views
Processing data from multiple cells and comparing results with another cell
For most orders, I have multiple deliveries under the same recipient ID and I need to compare whether everything ordered is really delivered. All products are divided by a comma and have always the ...
1
vote
3answers
57 views
How to return what is missing from a 2 column list comparing against all possible combinations of 2 single column lists?
Sample data:
A B C D E F
1 name handed in students assignments name missing
2 a 1 a 1 b 2
3 b 1 b 2 ...
1
vote
1answer
29 views
How to return all values (horizontal array) in B that has the same key stored in A?
Sample Sheet 1:
A B
1 a x
2 a y
3 a z
4 b i
5 b h
6 b w
Formula result, in Sheet 2, I am looking for:
A B C D
1 a x y z
2 b i h w
2
votes
2answers
42 views
Query returning #VALUE error when the column has no values
The below query returns #VALUE error when the column H is completely empty:
=QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, ' ', ' ', ' ', G, ' ', ' ', ' ', '"&...
2
votes
1answer
23 views
Return first vertical value that matches given criterion
On Google Sheets, I would like to figure out a way to have a cell return the first value in column C where the same row in column D matches the criterion ("Blue" for example, as opposed to "Red"). I ...
1
vote
1answer
41 views
How to multiply a query column by a cell refference on Google Sheets?
Below is the query that I use, and it works:
=QUERY(Input!A2:I33;
"SELECT '000', A, B, C, D, E, ' ', F, ' ', ' ', ' ', G, ' ', ' ', ' ', '"&TEXT(Input!L8;"dd/mm/yyyy")&"'...
1
vote
1answer
20 views
How to return unique list of text pivot by Query
I'm trying to mimic a pivot table behaviour where you would grab all distinct values from Col B and, by each of those unique Col B values, you associate all each respective values, that must also be ...
1
vote
1answer
18 views
Query Formula excludes results with new line character
It seems there may be a bug in Google Sheets' Query formula where results with a new line character (char 10) are excluded when the column is filtered. Is there any way around this or am I doing ...
1
vote
1answer
20 views
Combing two ranges with identical column structure from two different sheets (in same spreadsheet)
I'm doing a simple ticketing system, where I have two sheets, "Form Responses" and "Email Responses" both of which are populated automatically as their names imply (one from a form, one from an email ...
1
vote
1answer
12 views
How to combine dynamic columns in Google Sheets while retaining data in other rows?
I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns.
I want to ...
2
votes
2answers
76 views
Automatic currency conversion in Google Sheets
I have a personal accounting sheet that is mainly accounting in Euros, but for every manual Euro (EUR) entry I do, I want to have that entry converted to USD in the previous column next to it ...
0
votes
1answer
98 views
QUERY error NO_COLUMN:col1
=query(importrange("12HAbnTv4g_6m1UeCOs9ZO0gAYvy8tlaXr-cc2ecPEic","Master!A:ba"),
"select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col53
where col53<> ...
1
vote
1answer
52 views
Pie chart with sub-slices per category in Google Sheets?
I have a sheet that contains a list of books, with metadata about those books. Two columns that exist are the library within which the book is stored, and the condition of the book. I'd like to create ...
0
votes
4answers
29 views
How to pull the page number and the number of images listed from one sheet and then populate cells in another sheet accordingly?
Sorry, I didn't know how to summarize it in the title. Basically, I have a spreadsheet where I'm keeping track of the number of images that need to be on each page of a document I'm working on.
On ...
0
votes
2answers
36 views
Problem with QUERY(IMPORTRANGE()) to retrieve data
I’m having a problem with the =QUERY(IMPORTRANGE()) formula while retrieving a column data from one spreadsheet to another.
=QUERY(IMPORTRANGE("spreedsheetURL","Sheet1!A2"),"select Col2 WHERE Col1 ...
1
vote
2answers
25 views
Query using date range doesn't work for specific data
I have a spreadsheet (URL below). I've set up a query to make sure I understand how to do a query with a date range. Then I tried to create a similar query on the "real" data. The "Week 1" worksheet ...
1
vote
2answers
76 views
Cartesian Product with constraints
I have a really big database that I have to sort and divide as the example below:
F1 F2 F3 C1 C2 C3 C4 results
P1 X X X X X P1 ...
1
vote
1answer
25 views
How to concatenate cells using ARRAYFORMULA across rows and columns?
I have a document that's a recurring CSV import/export.
I'm trying to concatenate images separated by ", " and using ARRAYFORMULA to make it all the way down.
The problem is, I don't know how many ...
-1
votes
3answers
78 views
Finding closest value within a range of cells spread across the rows
Attached the Google Drive link and we have to refer to scenario 2 sheet of the spreadsheet.
https://drive.google.com/drive/folders/1HKePsd4SPSNdPujnwKLvq2i8xeg5sQgy
Here some value is given in rows ...
0
votes
2answers
26 views
Import range from 3 sheets/tabs into 4th
I currently have 3 sheets (tabs) in a Google Sheets workbook. Each column header (Row 1) in the 3 sheets are identical. I'd like to create a 4th sheet that combines ALL the contents in the 3 sheets ...
1
vote
2answers
83 views
Google Sheets Query with Date and Time
I need help with a query. I need to filter on some Google Form data and only show the results that have been submitted AFTER a specific timestamp.
Timestamp Cell = J2 - This cell is in the following ...
0
votes
3answers
46 views
Finding row number from value which is in between ranges
I have attached a picture containing two column B and C. If my input to this table is 2.3, it should return me row number where my input value is greater than or equal to B column value and less than ...
1
vote
3answers
189 views
Find the closest value within a range of cells and output cell reference
There are values from F column to AJ column in row 30. My input would be available in C column in row 30. As given in the picture, my input would be 118.45, I would like to find the closest value to ...
1
vote
2answers
31 views
Create random pairs without duplicates
I try to randomly pair people into unique pairs.
I have two columns one with helping values created via Rand() and one that ranks upon these values with
=INDEX($A$2:$A$164,RANK(S2,$S$2:$S$164))
...
1
vote
2answers
27 views
Google Sheets' Query or Filter solution
I have a simple table with columns:
A (Donor ID)
B (Donation date)
C (Donation Amount)
I need to extract the following report:
For each donor ID most recent donation date and amount donated ...
1
vote
1answer
28 views
Sum all values with same month and category
So I have this table where I'm writing down my daily expenses.
On one cell I choose the date, the next cell I choose the category and in the third, I insert the amount of money spent.
On another sheet ...
0
votes
3answers
22 views
Picking the Total from the Next Week
I have the next year mapped out (one line for each day) and have the week subtotal at the end of each week (which is a Friday).
I am wanting the Next weeks subtotal to show in another cell for that ...
0
votes
3answers
25 views
Average formula for Google Sheets with substitution over some figures
I have a row of figures from 0 to 2, some cells are empty. I need to count an average for them, but with the rule that would count 2 as 1.
Thanks
0
votes
3answers
87 views
LEFT JOIN two and more tables in Google Sheets
Say I have two tables. Table A (material requirements):
product_id | material | required
1 | A | 0.5
1 | B | 0.7
2 | A | 0.2
3 | A | 0....
0
votes
0answers
19 views
Send email notification when a data in column matches a certain value
I am trying to make Google Sheets send a notification to a particular person/s when one of the fields on column Q is equal to the word "High". I want the email to be sent when it is high because it ...
2
votes
3answers
33 views
How to remove duplicate words in a cell only if longer than a certain number of characters?
I need to remove a duplicate text in a cell but only if it is longer than 4 characters.
I have this formula that only works for any types of duplicate words:
=join(" ",unique(transpose(split(A1,", ")...
