The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
14 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:
1
vote
1answer
263 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 ...
1
vote
2answers
141 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
148 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
81 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
38 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
21 views

Extract and graph information from a days-hours-minutes-seconds format [closed]

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
24 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
33 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
93 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
votes
1answer
49 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
30 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
29 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
1k 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 ...
2
votes
2answers
70 views

Combining numbers based on a separate 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
16 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
65 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
229 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
89 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
258 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
36 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
292 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
145 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
155 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
54 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
49 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
1k 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 ...
1
vote
1answer
2k views

QUERY error NO_COLUMN:col1 [duplicate]

=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
2k 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
53 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
472 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
45 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
205 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
241 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
418 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
531 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
811 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
59 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
542 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
244 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
65 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
155 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
69 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
37 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
3k 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....
2
votes
3answers
127 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,", ")...
1
vote
3answers
59 views

Identify max value, divide others by half, then total all

This may be too specific of a formula, but I'm hoping there is a function or script that will accomplish it. I need a Google Sheet to identify the largest number in a row and divide all of the OTHER ...
1
vote
1answer
185 views

Cumulative Sum of grouped values

I found it a bit hard to phrase this question, so here's a screenshot: I'm trying to create a timesheet for our employees and would like to have a cumulative sum function that sums up the number of ...
1
vote
1answer
65 views

Formula for filtering all duplicate data pairs in Google Sheets

I'm making a table to check duplicate data. Where data that wants to be duplicated is in the "Sampel1" column and the duplicate checking results are in the "Status" column. The formula that I have ...
1
vote
1answer
739 views

Get URL from web page using IMPORTXML

Though I am new to web scraping, but I have a hand on experience of web scraping using VBA (Excel) I am facing the following problem: I have tried all methods (ImportXML, ImportHTMP, ImportData, etc.)...