Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
954
questions
0
votes
1
answer
629
views
Combine an array formula with query function
I want to know if there is a way that I can combine any of these 3 formulas.
Sample Sheet
I have in col. A3, this extracts the info in Data tab, and splits into rows: =arrayformula(substitute(...
- 383
0
votes
1
answer
96
views
Return value by lookup from multiple value
We constantly receive offers from suppliers and requests from customers. The problem is that customers and suppliers usually have several different identifiers for the same product. Like this:
...
2
votes
2
answers
4k
views
Google Sheets: Remove column headers from a QUERY()
I'm trying to eliminate (not hide) the column headings from the following QUERY(). The output from the =QUERY() must be in a single row.
=QUERY(myData!A:G, "Select SUM(E), SUM(F) WHERE D ='"&...
- 2,897
0
votes
1
answer
46
views
Google Sheets I need the column sums() of a table produced by a query()
I have a query() that outputs a table with two columns (A and B) of numbers. Pictured in IMAGE #1.
=QUERY(myData!A:G, "Select E,F WHERE D ='Books'")
IMAGE #1
But I need a function that ...
- 2,897
1
vote
1
answer
175
views
Query function to extract data from rows to columns (ignoring zero data) and using a drop-down box
I have only started to learn Google Sheets instead of Excel. I find it easier to understand if I have a problem to solve but the Query function quite difficult to understand.
I have table of fruit ...
- 21
2
votes
1
answer
269
views
Google Sheets Query - Drop down text match
I would like a query on the 'query' tab that searches sheet 'sales' and returns all values if it matches the dropdown selection on the 'query' tab
https://docs.google.com/spreadsheets/d/...
- 87
2
votes
1
answer
854
views
Google Sheets - Group/Aggregate data
I would like a formula that groups/aggregates the data of Column A, B, C and D if column A is the same string
The issue is the end of the string in column A with different sizes at the end of the ...
- 87
0
votes
1
answer
175
views
Regexextract numbers from string
I have a regexextract formula which takes the year from Column A (column) - however this only works if the year is yyyy-yy format - however some of the years are only yyyy and it returns an error
Is ...
- 87
1
vote
1
answer
45
views
Text input if cell > than 1
I would only like the values in Column A to return if there is a value of >1 in Column B - I am unsure on a formula to do this
- 87
0
votes
1
answer
302
views
Match value of cell if in between range and text match
Desired outcome:
I would like to return a value in column C (column D is the values I'd like the formula to return) - the value in column C should return on the criteria it matches the product name in ...
- 87
0
votes
0
answers
26
views
Extra m appears in com while working on Google Sheets
Check the above image when I add any link my Google Sheet every time an extra m appears in the end of .com. How can I resolve this?
- 1
1
vote
1
answer
115
views
Return matching text value in cell
I have a sheet of products and skus - I would like to have a formula that searches the entire column for matching text strings and returns it in a separate cell as the text string
Is this possible?
- 87
0
votes
1
answer
477
views
How to return multiple rows matching multiple criteria in spreadsheet
In Google Sheets, suppose I have rows like:
name
team memberships
Ben
operations
legal
Kate
legal
UK residents
infrastructure
growth
Carlos
operations
senior management
legal
Tina
operations
UK ...
- 101
0
votes
1
answer
639
views
Pivot Table - How to copy sum value onto another sheet
I have set up 3 sheets in Google sheets in related to budgeting/expenses.
The first contains the full budget (say Sheet A for example);
The second contains the expenses (Sheet B);
and the Third ...
- 5
1
vote
2
answers
2k
views
Using Query to select colum based on headers
I have a sheet with a large table of data with a header for each column. I want to import a specific column to another sheet using the header name. I am using the below formula to do so (which has ...
- 11
1
vote
1
answer
353
views
How to stack query results into single cell formula
I'd like to generate a URL based on a list of items that will change over time and I'm having a little trouble. My data looks like this:
Items
URL
Item 1
Item1URL
Item 2
Item2URL
Item 3
Item3URL
...
- 11
0
votes
2
answers
1k
views
Vlookup with column search by headers
I am downloading the "Data" report from the system, in which the order of the columns may be different, but their headers will always be the same and the first "ID" column will ...
0
votes
1
answer
725
views
In Google Sheets, take the first and last value of a query to summarize, grouped by user and shorted by date
:Edit -> added a working spreadsheet with the data https://docs.google.com/spreadsheets/d/1RNlZtvYZIpRF97u-7cOEGLS4my4OcLHD_1OJfDRCVCA/edit#gid=2100307022
I have a spreadsheet with a list of users,...
0
votes
1
answer
25
views
How do I make text in a column appear after the rows have been filled?
I have made this spreadsheet to help our mechanical workshop track labour. I have a problem on my spreadsheet where column K displays "follow up". The end users (technicians and foreman) ...
- 3
0
votes
1
answer
913
views
Pull some data, based on a date
I need to pull some data, based on a date.
QUESTION;
what is the value in column D, based on a date for example 'TODAY' or 2/4/2022
I ideally want to populate this detail onto another tab, where it ...
1
vote
1
answer
38
views
Student Pilot Spreadsheet - Retrieving date of last landing
Overview
As a student pilot I try to keep record of all my flights, total hours, costs and number of landings in Google Sheets.
As a safety measure our aviation safety agency (EASA) requires pilots to ...
- 23
0
votes
1
answer
39
views
Google Sheets: Is it possible to retain an IF/ELSE formula *AND* enable custom number entry into a cell?
I'm using the IF/ELSE formula in a cell:
=IF(logical_expression, "TRUE", "")
My use case is the following:
If the logical_expression is TRUE: A logic-derived value is shown in the ...
user232134
0
votes
1
answer
846
views
I want to query a set of data (multiple rows and columns) and return the rows of data based on an input value (search box) - google sheets
Raw data I want to query:
I want to query this data based on an input value that could feature in any of the columns of data to then return the correct rows of data:
For example if I type in 'A001' ...
- 3
0
votes
1
answer
610
views
Getting error using AVERAGEIFS in Google Sheets
I am trying to get the average score that team members in Team 3 in a given quarter gave for a question.
Column D contains team names, Column E contains the scores (on a 0-10 range). Column AK ...
- 21
-1
votes
1
answer
4k
views
Replacing text of links in google sheets
I have a bunch of links in cells in my google sheets. I want to replace all the text of them all just to say "LINK" so that when I click on them it's still hyperlinked to the correct site.
...
- 1
2
votes
1
answer
4k
views
Add iferror() for every error that occurs in sheet but keeps formula in this cell
I have a sheet with a lot of formulas. some of them gives me an error. I'd like to make something similar to ctrl+h and replace values but I'd love to find every cell that is giving an error and ...
- 23
0
votes
1
answer
255
views
How to create an IF function that has multiple outputs
I am trying to create a query that populates information based on the user's selected drop downs. Query is all built and functional, and populates the items in the master data that match the selected ...
0
votes
1
answer
117
views
Formula for Daily Cash Balance Sheet
I have created a Google Form for cashiers to balance out their cash drawers at the end of each day. Currently I have the form set so that they only have to count the amount of each coin/bill and not ...
0
votes
3
answers
878
views
Google Sheets: keep track of days spent in different countries
I have prepared a Google Sheet that lists all flights I have taken in recent years, alongside the departing and arriving country:
Date & time
From
To
Outbound country
Inbound country
Days in ...
0
votes
0
answers
326
views
QUERY function with date range not working and shows wrong results
I have been working on creating a personal finance spreadsheet and from my experience of using QUERY previously to create dashboards, I thought I would have no issues with this but I was wrong! The ...
- 1
1
vote
1
answer
2k
views
Google Sheets App Script Sorting
I'm trying to have an auto sort function only impact one sheet in a workbook rather than all of them. I've done this in other projects and def messed this up along the way - any help is appreciated!!
...
0
votes
1
answer
104
views
Highlight value in column based on list of values in other column that's on the same line as another value
Is that subject confusing enough?
Here's my spreadsheet: https://docs.google.com/spreadsheets/d/1pksa5_7R2TuPaufIS8mPl6gxZ8aPoMdEfkebKsx1YKI/edit?usp=sharing
It's for calculating speed@RPM for an ...
- 3
0
votes
1
answer
1k
views
Using IMPORTXML in Google Sheets: How to Combine XML Data Sources as a Single Set Before Applying XPATH Query
Using the IMPORTXML function on Google Sheets, I've got two XML data sets at two URLs, respectively in cells A1 and A2. I've got the XPATH query in cell H1. I would like to run a single query on the ...
- 237
0
votes
1
answer
311
views
Query function with variable in the Col1= [duplicate]
Here' the formula:
=query({{$P$3:$P$34;$R$3:$R$34;$T$3:$T$34;$V$3:$V$34;$X$3:$X$34;$Z$3:$Z$34;$AB$3:$AB$34;$AD$3:$AD$34},{$Q$3:$Q$34;$S$3:$S$34;$U$3:$U$34;$W$3:$W$34;$Y$3:$Y$34;$AA$3:$AA$34;$AC$3:$AC$...
- 1
0
votes
0
answers
19
views
Need helping knowing how to process this data in Google Sheets
I need to sort this data in Google Sheets. I have names and then each name has 4 letters assigned to it, one in each column.
An example is I need to know everyone who has a T and an I in column B and ...
- 1
0
votes
1
answer
46
views
Summing groups, Aggregate costs by items in groups
I have a sheet with a grouping by Feature / Subfeature / Task.
Here is an example:
How can I aggregate the cost from individual tasks to subfeatures (eg. rows 147-150 of Sign-In subfeature group) and ...
- 101
0
votes
1
answer
70
views
Trying to count values with multiple criteria
I have a file that contains a planner with dates in the lines and the roles of my team members in the columns.
I need to be able to count how many people are working per day and per role.
I need to ...
- 1
0
votes
1
answer
212
views
Select were Col1 contains range of other column
I would like to ask for help with my query.
Sheet1 contains another sheet that is imported with the formula import range. The first column contains the first names, the second row is the date, and ...
0
votes
1
answer
422
views
Problems merging 4 columns into 1 and charting them in Data Studio
I would need to unify the data from columns Metod_1,Metod_2,Metod_3,Metod_4 and Metod_5 into a single column, but keeping the resolution date data associated to each row:
Having resolved the above ...
- 3
-2
votes
1
answer
74
views
i can't find the problem to this formula
=UNIQUE(QUERY({IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP Directory!A2:U");IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP ...
- 1
1
vote
2
answers
52
views
Is there a way to remove entries based on condition in =filter?
I probably did not phrase my question accurately. Is there a way to remove the result 'a' because it fulfils the '<>1' condition? I am pretty sure a mixture of query and arrayformula is needed ...
- 11
1
vote
1
answer
2k
views
How do I return query results without the 'sum' column label?
I have the following formula in Google Sheets:
=QUERY(INDIRECT(C13&"!$C$5:F"),
"SELECT sum(F) WHERE D ='" &$E$12 &"'
AND C >= date'" &TEXT($G$4,&...
- 11
0
votes
1
answer
340
views
Clear cells script
So I have a google sheet document which houses figured for each hour of the day from 6 - 16:00 (each hour in a different tab.
I’m looking to add a script and a button that will allow me to clear all ...
- 11
0
votes
1
answer
415
views
Flip a column of Data vertically Import range
I'm importing the data from another sheet using the import range. How can I flip a column of data vertically in another Google Spreadsheet?
=sort(index(IMPORTRANGE("URL")
E.g. if the column ...
- 1
1
vote
1
answer
111
views
Multiple Gsheets data querys
Hey all so I currently have 3 Gsheets. 2 are sql imports with the below tables. The goal is to get the information based off a Type Id 28353 into my Master sheet. The plan is to get all values from ...
- 11
1
vote
1
answer
360
views
Google sheets import columns based on date value
Google sheets for simplicity's sake say I have two tabs, one with master data and one that is a search function to display a certain part of the master data. The issue is I only want import certain ...
- 11
0
votes
1
answer
2k
views
Get value of merged cells for query function
I'm trying to find if someone has been scheduled in the AM and PM in a table like this:
So I want to write something like this:
=QUERY(A13:B1002,"select A,B where A matches 'AM' and B contains '...
- 101
1
vote
2
answers
6k
views
Using Google sheets `QUERY` instead of `VLOOKUP` - function returning empty - why?
I need to use use a "VLOOKUP" type formula to match values across two data sets, but instead of using 1 key value as you would normally with VLOOKUP in need to use 2 key values.
Please see ...
- 6,999
0
votes
2
answers
1k
views
Using query/import range to combine multiple columns from Workbook A into one column in Workbook B in same order
I have one Workbook A where a client will fill in either column A or B with a value (if there's something in A1, B1 will be empty, etc.), and I'd like to connect it to Workbook B in one column. It's ...
- 3
0
votes
1
answer
139
views
Query with long search criterion
In a Google sheet I have to search for a column containing a specific number.
My search is
=Query(OPGAVE_TRANS!$A$1:$AD$23;"select AD where A="&A1&" OR B="&A1&"...
- 3