Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
1
vote
2answers
23 views
SUM columns from other sheets and group by month, but also show the individual sheets values before doing SUM
Currently, I have a spreadsheet which has a number of customer sheets, each with a pivot table showing the month in the row I and the total income for each month in row L.
On a separate sheet I have ...
1
vote
1answer
12 views
How to join VLOOKUP results of multiple sheets into one cell
I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it.
In this example, I am ...
0
votes
2answers
26 views
How to group data (by row and column) in a Google spreadsheet?
I have data as follows:
Name | Value | Y/N | Value | Y/N
A | 10 | Y | 12 | N
B | 100 | Y | 120 | N
A | 20 | Y | 10 | Y
B | 200 | N | 120 | Y
C | 1000 |...
1
vote
1answer
15 views
How can I create a unique list of data across multiple rows AND columns?
Google Sheets has a UNIQUE() function which when given a column of values, will output a column of all unique values.
This works well for column-specific data, however, you have multiple columns and ...
1
vote
1answer
16 views
Problem combining LEFT() and QUERY() functions
I'm currently working in Google Sheets and I need to import a part of a row from a spreadsheet to another. I'm importing the row with the QUERY() function and "cutting" the data from the row with LEFT(...
1
vote
1answer
23 views
How do I use the multiply line results from query or filter to get other values from an unsorted sheet?
I'm trying to take the results of a filter with multiple lines and use that results to acquire info from another sheet. Specifically, I'm trying to acquire Quantities in Customer Order sheet using ...
1
vote
1answer
18 views
Combining 3x IMPORTRANGE with dynamic range
I have 3 Google Sheet and want to combine all of them with:
Sheet 1: 480 rows
Sheet 2: 40 rows
Sheet 3: 20 rows
The sheet keeps expanding by the day since the sheet's data comes from a Google Form
...
0
votes
3answers
21 views
Select last entry from spreedsheet
So I have a Google Form dumping data into spreadsheet tab form responses 1.
I have created another tab labeled summary. I need to have the summary tab only show the latest entry for a given pilot ...
0
votes
0answers
16 views
Regex Matching for Google Sheets Query Formulas
I'm trying to return a limited list of cells using a query that references another tab in my Google Sheets book. Please find a sample book here.
The returned cells represent social media posts -- I'm ...
0
votes
1answer
14 views
Google Sheets Using Countifs & Unique Together
I am trying count the number of unique clients of Payroll service who are Won.
The answer as per the table below should be 4.
How can I achieve this, given that the table will be dynamic?
2
votes
1answer
21 views
Aggregate values based on categories and subcategories
I am trying to create a document which automatically tracks spending based on two categories.
For example, I have a sheet formatted similar to the table below:
Type | Amount | Category
...
1
vote
2answers
31 views
Using QUERY and IMPORTRANGE to pull records from a specific date range
I have created two spreadsheets.
This one is my source data: https://docs.google.com/spreadsheet_A
And I would like this one to just be the people who are currently here (so Column D < today and ...
1
vote
1answer
18 views
Query Importrange with multiple values
This formula is working fine, but I need to add in other teams into Column A. I would need to return ATT SLC, ATT Orem, and ATT Scottsdale within this same range, but I'm not sure the language to use ...
1
vote
1answer
21 views
Query Not Showing Certain Positive Values [duplicate]
I'm attempting to import some data to my Google Sheet.
Using QUERY to reshape my data, an example code of what I am trying to import is as follows:
=query(importhtml("http://statfox.com/cbb/cbbteam....
0
votes
1answer
44 views
Using array formula in Google Sheet
Trying to make an ARRAYFORMULA that executes a query function which takes a reference to the current position of the row number and the specified column name.
This is my version of formula:
=...
0
votes
1answer
31 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....
1
vote
0answers
54 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
56 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 ...
0
votes
0answers
21 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
25 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
17 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
33 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"); ...
0
votes
1answer
31 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
56 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
30 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
12 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
21 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
20 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
40 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
59 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
93 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
84 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
71 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
24 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
679 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
104 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
215 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
295 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
30 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
34 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
544 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
52 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
31 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
261 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
27 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
165 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
16 views
Search for multiple cities of unload
This is my table:
---------------------------------
| City of load | City of unload |
---------------------------------
| Enzesfeld | Lublin |
---------------------------------
| ...
2
votes
1answer
346 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
40 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 ...
