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
Make your voice heard. Take the 2019 Developer Survey now

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 ...