Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
1
vote
3answers
22 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 ...
0
votes
1answer
9 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
17 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
12 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.)...
-1
votes
1answer
9 views
Query is not bringing in data when column is between two dates
I am using the correct formula which has worked on other sheets:
=QUERY(Patients!$A:$K,"SELECT * WHERE F > date '"&TEXT(B2,"yyyy-mm-dd")&"' and A <= date '"&TEXT(C2,"yyyy-mm-dd")&...
1
vote
3answers
44 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
13 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
27 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
17 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
20 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
23 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
18 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
15 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
22 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
35 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
20 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
45 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
38 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
64 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
23 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
34 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
33 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
62 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
42 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
65 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
38 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
103 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
89 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
73 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
25 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
740 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
110 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
233 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
314 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
32 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
583 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
56 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
294 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 ...
