Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
0
votes
1answer
14 views
Repeat formula n number of times in Google Sheets
Apologies, I'm very new to Google Sheets and spreadsheets in general. I'm trying to fill some cells with repeating data, and then analyze it. The data is pulled from another large spreadsheet and has ...
0
votes
1answer
13 views
Merging data sets from two tables with origin reference [duplicate]
In a Google Sheet, I have multiple tables containing a list of dates.
I want to create a single (sorted) list containing all the dates and the table they came from.
Table foo:
1.1.19
3.1.19
Table ...
1
vote
1answer
14 views
Filter and display duplicates in a table
Here's the link to my Google sheet: https://docs.google.com/spreadsheets/d/1SBD0_Ca0r_p3810u1ZNlbG6MXsvhFlLcwEJndVQJRP0/edit?usp=sharing
The master data will show:
And I would like to have a table ...
-1
votes
0answers
6 views
How to Change Language of Addresses in my database? [on hold]
I have a very large customer database with an address for each customer. My postal service however, requires the addresses to be in Hebrew. Is there any sort of batch process that I could run that ...
1
vote
1answer
16 views
Dynamic Master sheet that grabs red formatted cells from another sheet
The title may be a little too ambiguous but I'll try to give a rundown as best I can;
I have a Google sheet with multiple "Sheets" or Tabs (as I'd like to call them) within it.
The Main tab is blank ...
1
vote
1answer
18 views
IMPORTRANGE from a separate sheet based on closest date to today's date
Sheet1 will have data with different past dates
Sheet2 - I want to import data from the 1st sheet and find the closest date to today's date.
https://docs.google.com/spreadsheets/d/...
0
votes
1answer
10 views
How can I copy a row of data automatically to another spreadsheet when a condition is met in a drop-down menu?
Our small business uses a spreadsheet to track data from inquiries. Once booked, I need the data to automatically copy in the "Current Bookings" tab in the same sheet when "booked" is selected from a ...
1
vote
1answer
18 views
Using ARRAYFORMULA and QUERY to produce results in multiple columns
I would like to be able to group rows based on unique values and then output the AVERAGE and STDEV values of two different variables for those groups.
Using the table below as an example, I would ...
1
vote
1answer
8 views
Help using 2 parameters to output a specific category
I'm looking to be able to enter an "AGE" and a "V02" in a formula that tells me what "category" the person would be in ie; good, excellent, etc.
0
votes
1answer
12 views
Split data of a row in multiple rows
I've got some problem with data coming from a Google form.
The result are:
A|A|A|A|B|B|B|B|C|C|C|C|
What I want to get is:
A|A|A|A|
B|B|B|B|
C|C|C|C|
Is there a way to get a similar result?
1
vote
1answer
28 views
How to get data to fill alternative cells when data is present in a zigzag pattern
https://docs.google.com/spreadsheets/d/1kwGbFd88cgifQRK_ZqsahQt1aDLzMTnmxHKs7mML5jA/edit?usp=sharing
How to get data entered in one cell to fill another cell in a different location with same values ...
0
votes
1answer
7 views
How do I search a string, and return all values?
How do I use the script editor to search a string in one cell, and return all results from another sheet?
For Example:
I have my string in cell B1 - which is the item number Data to be searched in a ...
0
votes
1answer
15 views
How can I do an aggregate function with multiple columns with a Google Sheets query?
I've got two columns of 10 numbers in Google Sheets. I'd like to average the numbers in both columns using a Google Sheets query. Something like this:
=query(A1:B11, "select avg(A&B)", 1) <- ...
2
votes
1answer
21 views
Google Sheets sort question
I used to know this a few years ago and for the life of me can't figure it out and can't find an answer in Google help.
I know how to sort columns, but only by the default A-Z format, how can I sort ...
2
votes
1answer
19 views
Perform functions (AVERAGE, STDEV) on rows grouped by unique values in Google Sheets
I would like to be able to group rows based on unique values and then output the AVERAGE and STDEV values for those groups.
Using the table below as an example, I would like to determine the AVERAGE ...
2
votes
1answer
6 views
Is it possible to edit the column a Google Form response saves to?
I have a client who uses a Google Form to save data to a Google Sheet. They deleted and recreated one question, and the responses to that question are now being stored in a new column. This messes up ...
1
vote
1answer
12 views
Full Join 2 lists with 3 key values
I'd like to fully join 2 tables on 3 key values, there doesn't need to be 0 value on blank cells if it makes it easier to understand (Some Array Formulas can be really confusing)
I have income:
I ...
0
votes
2answers
27 views
Checkbox to activate a column in QUERY
Is there a way to activate/deactivate a column in QUERY function?
I have a query to query and selection of several columns
Select Col1,Col31,Col32,Col33,Col25,Col26,
and want to have a checkbox for ...
1
vote
1answer
14 views
How do I refer to data in a column under this month?
In the below screenshot, I'm trying to get B42 to show the value of whichever cell on row 40 is under the current month's column:
As the dates are automatically generated using a transpose(...
0
votes
0answers
16 views
Issue with SUMIFS pulling data from QUERY across multiple sheets
I would like to sum values from a QUERY generated list in the sheet I/S/N/W to the sheet BUDGET for determining a value based on the date in BUDGET!B8. The function in BUDGET!B8 is =MONTH(TODAY()).
...
0
votes
1answer
15 views
Importing only certain rows after looking at other answered questions
=QUERY(IMPORTRANGE("URL","SHEET1!A5:P85"),"SELECT * WHERE K:K contains 'Name.s'")
Error Unable to parse query string for Function QUERY parameter 2:
PARSE_ERROR: Encountered " "K "" at line 1, ...
1
vote
1answer
20 views
Concatenating cell values to a matching cell
In the following sheet I am trying to grab a list of GUID keys from tab sheet1
and concatenate them to their respective order in tab sheet2. My expected output is in tab sheet2 column B as follows:
...
1
vote
2answers
13 views
How to get a true when a character is NOT in range
I want to filter all cells in a range which are not empty and does not contain a certain character. So I tried everything with regexmatch
=filter(Planning!G$5:G$37; regexmatch(Planning!G$5:G$37 ; "
2
votes
1answer
23 views
Comparing Unique Values to a Range
I have the following formula
=CONCATENATE (FILTER(boxmod!AC2:AC, boxmod!I2:AB=concatboxmod!C2 ))
The intention of this formula is to take a range and compare it to a list of unique identifiers on ...
0
votes
2answers
17 views
Copy other cells if EITHER column has specific text
In a single sheet wanting to copy D3:N if either B3:B or C3:C = S2. I'm sure there is a simple formula to do this I am just unable to find it!
1
vote
2answers
20 views
Copy the contents of a row to another sheet based on a specific value
I have a Google sheet with various rows of data, in one column (Column E), the values for each row follow the following pattern:
A1
A2
B1
B2
C1
C2
etc.
I want to copy the rows ...
1
vote
1answer
18 views
Left join 2 sheets on 2 key values, add constant value
I'd like to join by date on these 2 sheets (Income & Expense), on key values month and day and add constant value from FixedCost sheet,
Income:
Expense:
Add FixedCost to every row/date:
...
1
vote
1answer
50 views
Google Sheet Query smarter way to get multiple criteria
I use a spreadsheet where I want a list of items which contains multiple criteria.
(I need all cells in a range which contains the letter 'a - g'
The formula I use now is
=query(Planning!G5:G37; "...
1
vote
1answer
20 views
Link to a date cell in QUERY criteria
I have the following formula and it works fine
=unique(query(ARRAY_CONSTRAIN(ARRAYFORMULA(QUERY(
{Database!A1:AB, (Database!N1:N)-(Database!M1:M)},
"where Col9 <> '' AND Col1 >= date'2019-...
0
votes
2answers
14 views
Importing data found to be true from one Google sheet to another
I am trying to find a way to import data (text) from one sheet to another, via the same book. I assume it's going to need an IF or IFS formula because I only want data is that found to be "TRUE" (via ...
1
vote
1answer
26 views
Averaging cells that have both blanks and numbers for values that are in non continous columns with nine columns in between them
I need help averaging cells that contain numbers ranging from -1200 to 1200 approximately, or blank values only. I will be averaging them by groupings of 100 increments ie. -1200 to -1100, -1100 to-...
0
votes
1answer
12 views
How to find a string then find the sum of all cells next to the found string
So I'm trying to get G4 to automatically find "Bananas" in all of C and for every time it finds a value it adds up the corresponding number in D.
0
votes
1answer
45 views
Pivot Table Filters - Custom Formula
In my pivot table, I want to only display the entries in the column "Which Event" if the name contains "Baseball" or "Softball".
I can very easily do part of this by adding a filter for the field "...
0
votes
1answer
21 views
QUERY with Week Number
I use query to extract two columns from another sheet with data.
Column A has dates
Column B - numbers
I want to extract only rows that correspond to a specific calendar week number. For example 19.
...
0
votes
1answer
17 views
Google Sheets Pivot Table, Summary by Day
I have a sheet with 2500 dates & times of when customers have placed their order.
I want to do a heatmap or a bar graph to show which days of the week are the busiest.
I rounded the exact times ...
0
votes
1answer
13 views
How do I turn a SQL database downloaded table into a Google Sheets multi-line chart?
I want to turn this table (comes from an SQL database)
...into a line chart in Google Sheets that looks like this
1
vote
1answer
35 views
Query select all values of specific month
A have a table where Column A has dates. I want to get all values of a specific month
I have the following query for May for example
=query(Database!A1:AD,"Select A,E,F,U,V,W,X,AB,AC where Month(A) =...
0
votes
0answers
14 views
ARRAYFORMULA + QUERY + IMPORTRANGE - Final cell reference in IMPORTRANGE is acting like it's absolute when I need it to be relative
I know I'm chaining a lot together here, but it's SO close to functional. Here is my formula with the doc address altered for privacy.
=ARRAYFORMULA(IF(ISBLANK($AE$3:$AE),(query(importrange("https://...
1
vote
1answer
24 views
Google Sheets Formula Import Range with Indirect
I have this formula and want the reference to Archery to reference Archery which will be in Cell E1. I tried using the indirect formula but can't get it to work with the formatting for Google Sheets.
...
1
vote
2answers
103 views
How to return values in a formula, from rows of latest 4 nonconsecutive dates recorded
Using an ARRAY_CONSTRAIN formula we want to capture the latest 9 records (rows) over a period of nonconsecutive dates.
=ARRAY_CONSTRAIN(sort(vlookup(query({row(EBPP!D:D),sort(EBPP!A:E)},Q15,0),
{...
1
vote
1answer
13 views
Query with dropdown not working
In my Google sheet I'm getting an error:
Unable to parse query string for Function QUERY parameter 2: NO_COLUMN:
I've added a dropdown so the formula will auto-update depending on the tab ...
0
votes
2answers
19 views
How to use IMPORTRANGE when I have multiple tabs?
I have to export some data from a Sheet with 30 tabs(a tab for each day of the months) and I have to extract data under specific criteria from all the sheets which have to be done every month on a new ...
1
vote
1answer
9 views
Set certain days of the week on repeat [closed]
I'm making an attendance sheet for my team, how do I make only certain days like Mon Tue Wed appear in the top column?
0
votes
1answer
16 views
Query Function Not Putting Consolidated Data in the Right Place
So I don't know how to explain this completely, but basically, I'm using the query function to consolidate data from multiple sheets into one Master Sheet. The function itself seems to be working fine ...
1
vote
1answer
15 views
Rotate and duplicate row values in Google Sheets
I'm trying to rotate values in a sheet and then duplicate the first value in the row as a header. For example:
a | b | c | d
would become:
a | b
-----
a | c
-----
a | d
Is there any way to do this ...
0
votes
1answer
11 views
How to pull data from a random cell on page load in Google Sheets?
Basically, I'm just trying to figure out how to make a cell pull a random cell's value within a range at random when the page loads.
For example, have cell A1 pull a cell at random from range B1:B20 ...
0
votes
1answer
18 views
Filter column based in other column (using query prefer)
I am trying to make a filter with a query based in a column, but I cannot make it works.
https://docs.google.com/spreadsheets/d/1zKMW8vuyHRpS8eVEmRgAOg_dBQ0r2OIXnqV3_TFEm9o/edit?usp=sharing
This is ...
1
vote
1answer
20 views
Is there a formula to list all possible 2 card combinations of 4 playing cards in Google Sheets?
Example:
Is there a formula to list all possible 2 card combinations of 4 playing cards?
4 playing cards:
A♥
A♦
A♣
A♠
The result should be 6 combinations only (Not 4 x 4 = 16 Combinations):
1: A♥ ...
1
vote
1answer
31 views
How to import/merge data from from 2 separate sheets from another spreadsheet
I need to import data from "Spreadsheet A" to "Spreadsheet B" but all the data is in two separate sheets "MY" and "NY" in "Spreadsheet A"
I basically need exactly this but with a function to import ...
1
vote
1answer
20 views
How can I have Google Sheets automatically parse and calculate text that follows a certain pattern?
I have the following sheet:
https://docs.google.com/spreadsheets/d/1iEMjWVEwyuqbn_imW1UZ-AF2NlE__IbRZdHQifKJjko/edit?usp=sharing
I would like to calculate "Total Work" by deriving it from my own ...
