Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [google-sheets-query]

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

0
votes
0answers
10 views

Using array formula in Google Sheet

Trying to make a arrayformula that executes a query function taking reference to the current position of the row number and specified column name sharing my version of formula: =ARRAYFORMULA(QUERY(DB!...
0
votes
0answers
5 views

Import Range of cells as per today date in another sheet

There are three columns A, B, C AND today date is in first row of the column A means A1. I need to import these three columns date excluding date row. A2:A100, B2:B100, C2:C100 in another sheet. I ...
0
votes
1answer
14 views

Google Query, change select columns based on cell reference?

I am trying to improve a manual process that requires filtering through the input from a Google Form. It would be great if we could filter each question individually, without building multiple queries....
0
votes
0answers
19 views

Calculate Percentage with QUERY on Google Sheets

I have a Google Sheets database like this: Name | Value | Answer A | 100 | Y B | 200 | N C | 10 | Y A | 100 | N A | 1000 | Y B |...
0
votes
1answer
50 views

Replacing Query output string when the count is less than 3

Given the reference table below: Date Location 11/29/2018 Office A 11/29/2018 Office B 11/27/2018 Office C 11/27/2018 Office A 11/27/2018 Office A 11/24/2018 Office C 11/24/2018 Office ...
-2
votes
0answers
13 views

Including insignificant zeroes at the beginning of numbers of varying length

I am trying to scan numbers into google sheets spreadsheet, but some of these numbers begin with a zero. Currently, my spreadsheet will automatically delete the zeroes, but I want to keep them. Plain ...
0
votes
0answers
13 views

Query Google Spreadsheets select Min and Max dates as Column

I have a large row data sheet with several dates by company, and I need to calculate at column level the leadtime (max date - min date). Raw Data Source: --------------------------------------------...
0
votes
1answer
19 views

Add numbers to results of Google Sheets query

Is it possible to add a column of numbers to the results of a query? It would be nice to have a number (from 1 to n) next to each records returned from a query.
0
votes
0answers
15 views

Is there a way that I can flag in Google Sheets when multiple cells contain the same values?

I'm wondering if someone can help me in my battle with Google Sheets. I'm creating a rota and I want it to flag if all senior members of staff are either off or all on the same shift (either Early or ...
0
votes
1answer
23 views

How to Fix Error in Multiple Importrange

I am creating a table to display data from a combination of several Spreadsheet files by using Multiple Importrange. = QUERY ({Importrange (G3, "Sheet1! A2: D"); Importrange (G4, "Sheet1! A2: D"); ...
-1
votes
0answers
9 views

Google Sheets trying to see if one cell is found in another list of cells. [duplicate]

I'm trying to figure out in Google sheets whether one 7 digit number is found in a long list of 7 digit numbers. Ex; Is 7878904 found in the list of cells, if it is then output the number. It will ...
0
votes
1answer
23 views

How to remove duplicate rows based on a column (leaving first one out) in Google Spreadsheets

I am trying to remove duplicates from my Google spreadsheet file. Here is an initial table example A B C D HelloWorld1 2 6 41 HelloWorld1 4 3 5 HelloWorld2 6 46 6 ...
0
votes
1answer
32 views

Arrayformula and Sum queries. Possible alternatives

I am using a sheet to list and summarise teachers absences and substitutions. Here's the worksheet Basically in the sheet "Substitutions" all absences and substituions are stored record by record: ...
0
votes
1answer
25 views

Google Sheets - select highest value for each unique label

I have a simple spreadsheet which is tracking timeseries data for several users: Time | User | info 00:01| bob | hello 00:03| sue | hello 00:05| bob | goodbye 00:10| sue | hello again I would ...
0
votes
0answers
11 views

Next lowest value query

New to queries. E8 = calculated value based on manually inputted data in B4 D2:D13 = contains set numerical values ranging from .00722 to .003608 from lowest to highest B7 = query field that ...
0
votes
2answers
18 views

Google Sheets Lookup For Nth Value

I am working on a spreadsheet that seeks to collate scores to a team. There is an input table where team and score start next to each other (see the example sheet linked below) into a table with each ...
0
votes
1answer
19 views

Google Sheets Query Searching?

I'm trying to run a Query on some data that's being imported, and have it only Bring data over that matches the Email in the document. This is my code: =QUERY(IMPORTRANGE("SHEET ID REMOVED","'Form ...
0
votes
0answers
18 views

Google Sheets Query Not Importing All Data

I am using the google sheets query function to import data from one sheet into another. It is functioning properly and taking only the data I want. However, some of the columns from which data is ...
0
votes
0answers
34 views

How do I reference the previous column of a calculated field on a pivot table? Looking to calculate percent change

I have a pivot table with a bunch of columns indicating the value over time, and would like to create a calculated field showing the change over each value. How would I reference the previous (and ...
0
votes
0answers
37 views

Automatically allocate deposits to invoices

I'm facing a difficult challenge (at least for me) with Google Sheet. I receive invoices all year long and I make deposit all year long that should offset invoices. However deposits values are ...
0
votes
1answer
46 views

Standard deviation in Google Sheets Query

I have data of jobs (A), their executions (B) and duration (C) in my Google Sheet. I have already calculated the average duration of each job with Query language: SELECT A, B, AVG(C) GROUP BY A, B ...
1
vote
1answer
52 views

Collated and separated results in one cell for QUERY results across multiple columns

In a Google Sheet I have the following formula: =CONCATENATE(QUERY(Summary!I4:U200,"Select I,U where S='"&A3&"'")) This currently returns each pair of results in sequence (with no spaces) ...
0
votes
1answer
56 views

Google Sheets intersection query

I'm trying to write an intersection query in Google Sheets that lists all the rows in sheet 1 that contain a cell in sheet 1, column A, that matches at least one cell in sheet 2, column P This is the ...
0
votes
1answer
20 views

Query not working unless I change the Date format to Automatic [duplicate]

Query works fine if I change Hudson F to Automatic, if I change it to date it stops working. I need to view F as date and have the query work =QUERY(Hudson2000!1:6634, "SELECT A, B, C, D, F, G WHERE ...
1
vote
1answer
355 views

Query: select cells in column where it is not null

I am trying to pull non null data from a column. I have tried the following. Each import range works independently. =query({IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34"); ...
0
votes
1answer
82 views

QUERY remove column and column header if blank

The attached document is a time-sheet document. There are 2 tabs, TimesheetData and InvoiceData. I want to pull through data from TimesheetData tab to the InvoiceData tab using a query. The query I ...
1
vote
1answer
140 views

Query based on column header in Google Sheets

I am trying to automate Worship list using Google Sheets, so it works good but one thing I can't do can be more than useful. I have a sheet like this: in row 1 dates and below songs used that Sunday....
1
vote
1answer
199 views

IMPORTRANGE syntax when using query on multiple tabs

I am using IMPORTRANGE within a query to extract data from sheet 1 to sheet 2. The following code is in a cell in tab A of sheet 2, and works as intended: =query(importrange("URL-A","E:J"), "select * ...
1
vote
1answer
23 views

Google Sheets query column=column

I am not familiar with google sheets, but I have been sent an assignment and I don't want to take the time to convert the whole thing over to an actual database. Is a way to take one column of values (...
1
vote
3answers
30 views

Pulling data from one sheet to another if

My knowledge is pretty basic when it comes to excel and Google Sheets. I'll explain what I'm trying to do. I'm trying to find a way to filter information from 1 sheet to another in Google Sheets. ...
2
votes
2answers
320 views

Google Sheets, how to sort query results manually (Not in query)

My google sheet is importing data from another tab just for sorting purpose, however, when the sort is done manually (through Google Sheet menus), results are unpredictable. The sheet is populated ...
0
votes
1answer
25 views

Date Query in Google Sheets with importrange

I have two tables. Table A= Customer Feedback Table B= KPI In Table A I have a column for a date with format mm/dd/yyyy and in Table B I have 2 columns as from date and to date with the format. I ...
0
votes
2answers
28 views

Scanning a column and partitioning information to other sheets in Google Sheets

I am a membership manager in an international organization and I was hoping to get some help optimizing our members' contact list. There are three kinds of members, NCs, LCs, and IMs. I have a ...
2
votes
1answer
156 views

How do I list Nth matches in Google Spreadsheet?

On MS XLS I can do some aggregate or leech off a structured reference, but I really don't know how to find the Nth result based on word match on Google Sheets. I have huge columns - name, role. I ...
0
votes
1answer
23 views

Split rows dynamically based on row length (COUNTA) and transpose to 2D array

I have data in this format: And I need it to be reformatted into this: Basically, each row in the initial format is composed of 4 concatenated sections of equal lengths: Part(s), Product(s), Quant(s)...
1
vote
1answer
119 views

Transform a Google Spreadsheet table? Columns by rows [duplicate]

I need to transform this table into this one, with de Services columns as rows:
1
vote
1answer
15 views

Search for multiple cities of unload

This is my table: --------------------------------- | City of load | City of unload | --------------------------------- | Enzesfeld | Lublin | --------------------------------- | ...
2
votes
1answer
238 views

Query Function with Unique References

I am currently using the QUERY function to move relative information to another sheet, which is then used for forecasting. Now what is currently happening is the same business names are showing up in ...
1
vote
1answer
39 views

Check value even if it doesn't match validation

In my google spreadsheet have a column G of dates that interviews are/have been scheduled on. There is a validation set on it to validate for dates with a warning. Every once in a while, when someone ...
0
votes
2answers
35 views

Confirm that values in a column are alternating

I have a column, call it A, of around 1500 cells. Each cell contains either "a" or "b". For the most part, the As and Bs are alternating, but sometimes there are repeating values. An example of A1:...
0
votes
0answers
45 views

In Google Sheets, SQL Query: How to make multiple rows out of an transposed split of a column?

I have a query that returns this: =QUERY(BOMs!1:300;"SELECT BU,A,B,E,G,H,K,L,M,BC ";1) L65P6 PR BOT 33-KLN121-06X BOBINA CHIP 120 OHM L500, L503, L502, L501 2780 4 UN 0,10154 The column ...
0
votes
1answer
45 views

Google sheet query to combine values

From various examples I've hacked together a pretty neat aggregation function which gives me an overview on which activity (strings in G) I've spent non-billable work hours (numbers in E) or billable (...
1
vote
1answer
366 views

Query returning results into merged cells in Google Sheets

Short Question: I have a query function returning an array over merged cells. Instead of displaying all of the values in the array, some of the data is hidden in merged cells where only the first ...
1
vote
1answer
24 views

How add column BY to a query?

How do I include the column BY in a query? I first tried =unique(query(data_table,"SELECT D, BY")) Then, I tried to surround it with single quotes =unique(query(data_table,"SELECT D, 'BY'"))
0
votes
0answers
18 views

Help with query

I have the following data: column A is an ID (integer) column E is DATA (integer) column F is DATE1 (date) Column G is DATE2 (date) The table has multiple rows for each ID. I'd like to get only one ...
0
votes
1answer
21 views

Can we use Column Range for Comparison in Google Spreadsheets Query?

Suppose I have a table with 3 columns in a Google Spreadsheets sheet. Col1 is a name and Col2 and Col3 are numbers. Can we use a column range for number comparison such as in the example below? (what ...
0
votes
2answers
50 views

Google Sheets: Split row value and count each part

My Problem I have a survey result with a checkbox question in which more than one answer could be checked. The results are given in a single column, separated by a | sign. I would like to run a ...
1
vote
1answer
594 views

HAVING clause in Google Sheets QUERY

My Problem I have built a count query that returns the most common elements from column H in the final sheet: =QUERY(final!H2:H,"SELECT H, COUNT(H) GROUP BY H ORDER BY COUNT(H) DESC") I would like ...
2
votes
1answer
112 views

Find column headers then do multi-criteria lookup

I'm trying to create a report where I need to find each column by header, then do a multi-criteria lookup. (I need to find the proper column because the data file is a csv export from another ...
-1
votes
1answer
68 views

How to use a value in Sheet2 in the WHERE clause of QUERY in Sheet2 whose dataset is in Sheet1

I have a Sheet1 that I want to use as my dataset for a Google Spreadsheet query that looks as follows: The query is in Sheet 2 and I want to pull across a list of manufacturers who have a Type that ...