Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
954
questions
1
vote
2
answers
89
views
Add cell value in query
When doing a multi-sheet query, I would like to bring in the value of a cell (A2) from each source sheet to a new column in the results.
The query:
=QUERY({CCL!A4:M; HOOD!A4:M; AAL!A4:M; BAC!A4:M;
...
3
votes
1
answer
67
views
How to add a column in query that adds 1 year to date column
I have a Google sheets with a bunch of sheets for each vehicle type. Each sheet is formatted the exact same: column A is the unit number, column B is the inspection date, and column C is the status (...
0
votes
1
answer
269
views
Query ignoring text
When I share data with sub-subcontractors, I use the QUERY function to return a subset based on a company name match. I never had any issues in the past, but lately QUERY has not been returning values ...
- 1
3
votes
1
answer
90
views
Filter a list of lottery results for those matching at least 4 numbers
I have a list of 3,000 winning lottery numbers. Each winning lottery number is on its own row and is comprised of 6 individual numbers, each in their own column. The winning number's draw date is also ...
- 33
1
vote
0
answers
37
views
Google Sheets - pull multiple lines of data from Workbook1 based on A1 in Workbook2 [closed]
Ok. So I've been all over this looking for an answer to my problem. I found so much helpful info, but none of it quite solves my problem. So I am here to ask the experts myself.
I work for a ...
- 19
0
votes
1
answer
143
views
How do I return the value of a specific cell within a range on Google Sheets based on current date?
I have a spreadsheet that I'm using to track weekly performance metrics and want to create a summary page that only shows me the value of each metric for the current week.
The worksheet is currently ...
- 1
1
vote
2
answers
784
views
How can I QUERY a range of data for rows where a column contains any text string from a group of valid strings?
I want to query data from another sheet by checking if the data_range's column C (invoice#) matches any one of multiple lookup values from a range in the current sheet's column B.
The lookup values ...
- 11
0
votes
1
answer
49
views
Formula to check is cells are blank. If the cell is not blank then assign a value
I have a data set on Google Sheets where for example,
Name
Stage 1 Date
Stage 2 Date
Stage 3 Date
Stage 4 Date
Bob
3/1/2023
3/5/2023
3/31/2023
4/15/2023
Joe
2/1/2023
Zoe
3/21/2023
4/1/2023
4/15/...
- 43
0
votes
1
answer
29
views
Query to group files together by a unique box number and return the min/max range
I have a Google sheet that list of files with a corresponding box number(Col1)labeled "Box Number". I want to be able to gather all files by each unique box number and then return the min ...
- 13
2
votes
2
answers
191
views
Sum of all numbers from many columns that match text from multiple rows in another column
I have some data from analytics that I need to combine somehow and trying to find the best way of doing this. I have data similar to the following:
URL
Sessions
/
59
/?variable=1
2
/?variable=2
37
...
- 135
1
vote
1
answer
368
views
How to QUERY - group SUMed results? [duplicate]
I'm new to query and can't figure out how to get grouped results of sum in two columns.
There's data set in the picture of names and points which are in two columns and query formula to sum and group ...
- 11
-2
votes
2
answers
23
views
i want that webp urls are arranged in the same order as jpg urls
webp urls have the same names as jpg urls. i want that webp urls are arranged in the same order as jpg urls. they're in Y2:Y17 in google sheets. jpg urls are in Y2:Y9 and webp urls are in Y10:Y17. ...
0
votes
1
answer
45
views
if cell is greater or less than the sum of the range of cells
I've looked around for an answer to this without success.
I have a spreadsheet where I need the "18" change and be highlighted as green if the sum of the next 4 columns with a check is ...
- 23
0
votes
2
answers
336
views
How to Count Combinations In a Set (Including dupes)
Trying to figure out a formula where I have 5 columns of text data. I want to count how many times combinations of data in 2 contiguous columns appear in each row.
Example data:
A
B
C
D
E
1
A
A
A
B
...
0
votes
1
answer
266
views
SUMIF for unique entries
I need to do a SUMIFS statement in Google Sheets but am having a hard time not double counting duplicates in the data. For example:
Volume
Flavor
Batch #
6000
CLM
BT1000
6500
CLM
BT1000
7000
WBG
...
- 13
0
votes
1
answer
366
views
Google Sheets use count in query without expanding array
My goal is to dedupe results based on column B, filter by last X days in column 13 (omitted in sample data I can work that part out myself), and then count the number of rows that remain with a value ...
- 469
-1
votes
1
answer
31
views
Pair and Sort the Columns in Desc Order [closed]
Hello I have the following data in my table:
Columns A to E indicate the year tag, and columns from F to J indicate the years. Each tag corresponds to a year. For the first line:
T1 -> 2004
T2 -&...
0
votes
1
answer
31
views
Query that combines the contents of 3 columns, but also filters by a date in a 4th column
This is an example of my data that is located on a sheet called Data:
Date
Fruit 1
Fruit 2
Fruit 3
1/1/2020
Apple
Mango
Apple
2/1/2021
Cherry
Banana
Pear
2/1/2021
Orange
1/1/2022
Apple
Cherry
...
0
votes
1
answer
44
views
How to Eliminate Duplicates Within Same Cell with Separator
Say I have one column that contains cells:
Apple||Banana
Banana|Apple
Apple||Apple
Banana|Banana
Apple||Banana||Apple
etc...
How do I eliminate any instance of a word appearing twice (separated by a ...
0
votes
1
answer
771
views
Google Sheets function to create hyperlink based on data in cell, but not if the cell is empty
The solution outlined here is ideal for my needs (converting an article ID in column C into a hyperlink to a CMS that displays in column D).
However, I would like the formula to leave column D blank ...
0
votes
1
answer
34
views
Formula to select certain people only
I have a spresdsheet in google sheets and use the following formula
=QUERY('Main Data'!A2:U,"select B, sum(J), sum(E),count(E), sum(F), count(F), sum(G), count(U),
WHERE A IS NOT NULL
AND O>=...
- 1
0
votes
1
answer
157
views
Best way to SUM all the columns between two particular columns
What should be the best approach to get a sum of cells that are between two columns A and E. Column A has a header of "Month Jan" and col E has a header of "Month Feb".
Columns are ...
- 3
-1
votes
1
answer
58
views
Sorting data extracted by QUERY by a new column in the destination list
I am pulling some filtered data out of a master list by QUERY. I am also adding another column, which does not exist in the master list, to the date in the destination list. My question is how should ...
0
votes
1
answer
49
views
Formula to randomly select the name associated with lowest value from data set & a random from the lowest options if tied with drop-down select filter
I have a data set on Google Sheets where for example,
Name
Scores
Region
State
First
Row
Row
Row
Second
Row
Row
Row
On another tab, I have a drop-down list to select a specific Region and State. I ...
- 43
2
votes
1
answer
69
views
How do I make the weight column in my medical log sheet get updated with the most recent weigh-in?
I am looking to get the weight column in my medical log sheet to be updated automatically when I key in the new weights on my Weight Log sheet. I take the weights weekly and update the info into a new ...
- 23
1
vote
2
answers
101
views
Query+Importrange for multiple sheets based on values in Col26
=QUERY({
importrange("1HGW2VJFyhosCM-5AK7II6CRt19sPMsmGPbxEJvPVnK0","SUN!A3:AB5001");
importrange("1Fjbl3Ndh-thsb9ufNhJ2GgHjjIfvG1e7vUe4dNcwCew","SEA!A3:...
0
votes
1
answer
735
views
Use MATCH & LARGE but with 2 identical highest values in Google Sheets
Thank you to doubleunary for quickly solving my first of two questions. I am now able to create a single cell that properly lists all of the top 5 largest value column titles separated by commas; even ...
0
votes
1
answer
513
views
amending query statement difficulties
doubleunary kindly helped with the linked question however i'm struggling to amend the Query() section so as to pull different results. ie. for repairs and revenue also.
Google Sheets - Formula or ...
2
votes
1
answer
119
views
How do I receive the data from Google Form onto Google sheets and allow my students to edit it without other students seeing their work
I am trying to feed data from a Google Form to a Google Sheet. I want to have it separate each student into their sheet. Then, I want to send that information back to the student in a Google Sheet ...
- 21
1
vote
1
answer
71
views
How to filter data from various sheets based on dropdown selection
I am setting up a sheet to track renewable services for my company. There will be various tabs, for example, Domains, Hosting, Google Workspace and Phone Numbers.
I would like to be able to filter all ...
- 11
1
vote
1
answer
199
views
Dynamic & extendible category names
I'm having trouble on something with Google Sheets:
I need category names to be dynamic & extendible between 15-200 values, so it's not quite worth an Apps Script, would be super cool to put the ...
2
votes
1
answer
159
views
Using QUERY with IMPORTRANGE as the data source not allowing referencing columns
(Link to Test Spreadsheet)
I am working on a spreadsheet that has a list of people, and for each person, they have ten choices that correspond to adjacent columns with artist and song name. The data ...
- 23
1
vote
1
answer
334
views
merge data of columns that have the same label vertically in google sheets
I have a couple of datasets with the same columns entered horizontally next to each other.
I have a data / facebook leads / facebook adspend column for thee moonth of january in column a, b, c and the ...
- 35
1
vote
1
answer
47
views
Match taking into account characters like ä ë ï ö ü, á, é, í, ó, ú
Hey I am running against a small problem that I do not know the answer of.
I have 2 lists:
List 1 has all my data, for example
Märio
Toad
Bowser
Pëach
Luigi
List 2 has data too (but this list doesnt ...
0
votes
1
answer
368
views
How to repeat query for a range of data in google sheet
I have this table in Google sheet:
Col A (Month)
Col B (Date)
January
1/1/2023
January
1/2/2023
January
1/3/2023
And I have another table:
Col C (Dept.)
Col D (KPI)
Dep1
KPI1
Dep1
KPI2
Dep2
...
- 35
-1
votes
1
answer
80
views
Building a function that imports value for the neighbor cell of a matching cell from another spreadsheet
I’m trying to create a two-way communication “sync” system for neighboring cells with a matching cell between 2 separate Google Sheets Spreadsheets. So for example, 2 Spreadsheets called “A” and “B” ...
- 1
0
votes
1
answer
333
views
use a string as a parameter in a equation
I want to use a string as a parameter in an equation to get a value from another tab.
For example, i want to use the string 'OKT' in the gray column as a reference inside the equation in the orange ...
- 1
0
votes
1
answer
203
views
Google sheets: auto sorting based on color and alphabetic order
So I have this list:
B (white background)
A (white background)
C (green background)
And I would like to make a function where I sort it as follows:
C (green background)
A (white background)
B (...
user305241
-1
votes
1
answer
834
views
Unable to parse query string for Function QUERY parameter 2: ADD_COL_TO_GROUP_BY_OR_AGG: Col2
I have two query formulas, with one inside the other (This is the way my manager figured it out and wants to proceed using this methodology for some reason). What's happening in the initial query ...
- 101
1
vote
1
answer
190
views
sheets query requires splitting last returned column by comma
Table with data in columns A-N. Data in column 'M' contains CSV data.
Using this query can return data, need what's in 5th returned column (E) to be split into additional columns through one command. ...
- 11
2
votes
1
answer
192
views
Google Sheets Query function sort breaks when using drop-down filters
I have a query in my Google Sheet to pull student testing data from a master list based on drop-down menu options. My goal is to sort the filtered data by Column L (Completion Date) so that I can ...
- 23
0
votes
2
answers
850
views
Google Sheets: FIND multiple cells containing specific text
I have a Google Sheets table and one of the columns has email addresses.
I'm filtering that column using this custom formula that works:
=FIND("@gmail.com",I2)
But I need to be able to ...
0
votes
0
answers
125
views
How can I combine data from 200 different google spreadsheets to one master google sheet
I have about 200 google spreadsheets. Each of these google spreadsheet has multiple sheets. I need to gather the data of one of the sheet in all the 200 google sheets to one combine sheets. it is like ...
- 1
0
votes
1
answer
65
views
Add data to certain columns if conditions of separate 1 or more conditions for data in each row are met
I have a table with "manufacturing quotes" and each quote has 1 or more parts in it (please see screenshot below). The data in parts1-3 (columns E-J) are filled, however, in reality, the ...
1
vote
1
answer
126
views
Lookup and Conditional Formatting
I have some columns in sheet 1 (here in image O and P columns), and in another sheet (here in image R, S, T, U, V, W). I want to check the matching Date and Number from sheet 1 with Date and Number ...
0
votes
1
answer
36
views
In Google Sheets how do I return the total number of scenarios that match two conditions?
I am creating a summary within a large spreadsheet and want to be able to show the total number of items that meet two criteria.
Criteria A is in a range in one column, and Criteria B in another. Both ...
- 3
0
votes
1
answer
121
views
Google Sheets - Sum Filter of Table 1 in each row of Table 2
I have table of transactions with categories (let's say 1-5) and assigned dollar values, and another table with a list of each category. I want to sum up all the matching categories from Table 1 with ...
- 35
1
vote
1
answer
1k
views
Stack two sets of data into one in google sheets - different columns
I am looking for a solution that helps me stack two sets of data (from diff. columns, different order) into one data sets
EXAMPLE:
columns names + order tables1:
first name, last name, city, age, ...
- 11
0
votes
1
answer
68
views
What is the best approach for filtering rows and counting unique dates in a Google Sheet?
I have a shared activity log of bike rides in Google Sheets that looks like:
[A] Date
[B] Name
[C] Miles
1 Nov 2, 2022
Joe
2.1
[2] Nov 2, 2022
Ray
1.5
[3] Nov 3, 2022
Ray
3.1
[4] Nov 4, 2022
Joe
...
- 155
1
vote
1
answer
26
views
Get series of date
I want a column with date only, like this
DATE
11/05/2022
11/05/2022
11/05/2022
11/06/2022
11/06/2022
11/06/2022
11/07/2022
11/07/2022
11/07/2022
And list goes on. I just want to know how ...