Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
0
votes
1answer
10 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 ...
0
votes
1answer
4 views
Set certain days of the week on repeat
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
5 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
6 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
7 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
10 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
18 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♥ ...
0
votes
1answer
12 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
18 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 ...
1
vote
1answer
27 views
Trying to Query a list against another list
First time ever using stack exchange so please excuse any formatting problems.
I'm trying to create a sheet where Sheet1 is a dump of data and Sheet2 contains:
A date range
A list of items
True/...
0
votes
2answers
21 views
Add columns based on rows
I'm currently working on setting up a sheet, but I want to add columns in one worksheet based on rows in another worksheet.
I have no idea if this is even possible, I've tried googling it. Might be ...
0
votes
1answer
16 views
Sort Google Spreadsheet by 'alternating' values in multiple columns
How can I sort a random list in Google Sheets so it returns alternating values? Is it possible to do it with multiple columns?
How can I alternate so each Room starting with the lowest has one ...
1
vote
1answer
12 views
How to group values by month and sum previous values?
I would like to sum the values of a table grouped by months but adding up the previous months.
Example:
Result that I want:
The closest I've come so far is:
=Query(B2:C4;"select sum(C), B group ...
1
vote
1answer
17 views
Analyse multiple columns per row
I need to combine the data of three different columns. Thus, I have this:
-------------------------------------------------------
| ID | ID Data | Markdown 1 | Markdown 2 | Markdown 3 |
--------------...
1
vote
1answer
12 views
Google Sheets Query Where Cell value
I'm looking for help editing a Google Sheets formula. I want the formula to Import Range from another worksheet where value equals a specified cell. I can only make the where in the formula equal a ...
0
votes
2answers
14 views
Using a checkbox to auto-populate a row on another sheet
I have this spreadsheet which is used for receiving and recording work. SST responses sheet is where people submit requests, Workload is where we record all work.
I'm wanting it so that when someone ...
0
votes
1answer
9 views
How can I have a 2nd function start in the cell after the output of the 1st function?
So the problem I am trying to solve is where I have Google finance fetching the price of a stock and outputting it, but the length of cells of its price history is increased by 1 each day. In the ...
0
votes
0answers
4 views
Edited Google Form questions not same when open in spreadsheet
I used google form to create few surveys. I do some edit on the questions, questions sequence, etc. However, the questions sequence in spreadsheet is not the same as in google form. If there are new ...
1
vote
1answer
8 views
Search max value of column after individual operation on each value of that column using arrayformula or similar
I have a Google Sheets with a column D with identifiers in a certain format (i.e. Y19_XXX):
Y19_234
Y19_213
Y19_814
Y19_215
Y19_817
...
Now I want to determine the highest value XXX in that column ...
0
votes
2answers
17 views
Applying IMPORTRANGE formula in Google Sheets between tabs
I have one Google sheet having three tabs, Sheet 1, Sheet 2, Sheet 3. I have another tab having the name "Sheet 4" in which I want to import all data from these three tabs, but the problem is data in ...
1
vote
3answers
26 views
How to use FILTER to select only values repeated more than X times
I am trying to use the filter function to select from 2 columns, only the values repeated. I will give you an example:
My column is like:
Names Jobs Value
-
Gerard player 4
Gerard player 2
...
0
votes
0answers
16 views
Google Spreadsheets - Query Select A, B, MIN(C), group by A only
thanks for the help! Sorry if I'm expressing myself in a confusing manner, I'm a beginner at this... I'm trying to create a spreadsheet to help me plan and analyze grocery expenses more efficiently.
...
0
votes
1answer
28 views
Filtering a QUERY using MATCH - how to pass column value into function? Or is there a better way?
I want to get a subset of data from another tab. Eventually, this will become a script and pull data from a different sheet.
I can use QUERY to get all the data available, but I only want to return ...
1
vote
1answer
9 views
How to SUMIFS text beginning with apostrophe
I am receiving a list of products from an external data source. One of the product names begins with an apostrophe. When I attempt to produce a summary of the products and total quantity using UNIQUE()...
0
votes
1answer
14 views
Average first 3 values excluding empty cells in google sheets
Trying to find the average of the first 3 values in the column.
I'm already running a query to find the average data within the last 3 days
=average(query({$A$10:$A$107,$E$10:$E$107},"Select avg(...
1
vote
1answer
18 views
Shortening this Formula, and Future Proofing it
I am creating a Google Sheet to use as an employee schedule. On this sheet, I have Cells which will contain the Total number of Hours worked per day by all employees.
At the moment the formula I ...
1
vote
1answer
21 views
Query(Importrange) or Query with dates
I am trying to show or limit what is seen to just entries made in 2019. I found a few places that referenced this and have tried the following without success.
=query(IMPORTRANGE("1bSyor-...
1
vote
1answer
9 views
Finding the average from the last 14 days in Google Sheets
I would like to figure out a formula that will look at the last 14 days of data in column D and calculate the average. But I would like it to dynamically update every day so it's always showing the ...
0
votes
1answer
22 views
Can Weekday value be shown in natural (Su, Mo, Tu..) format in Array Formula?
For Google sheet with the following data in A2:B4
06/29/2019 1:25 PM 1
06/30/2019 1:32 PM 1
07/01/2019 1:40 PM 1
The output will show
Monday 1 1
Saturday 1 1
Sunday 1 1
Can this be ...
0
votes
1answer
16 views
Count in QUERY to include counts for all unique items (dates)
=query(A:B,"Select A, Count(B) where A is not null and B='A' group by A,1")
0
votes
1answer
45 views
Equivalent of IMPORTRANGE with script in Google Sheets [closed]
Since the Importrange() is not working as expected, do you know how I could express e.g the following function with a script?
={IMPORTRANGE("SheetKey","SheetName1!A:J") ;
IMPORTRANGE("SheetKey","...
0
votes
0answers
7 views
Dynamically reference DATA in Query in Google Sheets
I have a query in a Google Sheet structured like so:
=QUERY({IMPORTRANGE(Expenses_2018Q4,"DATA_Expenses"); IMPORTRANGE(Expenses_2019Q1,"DATA_Expenses");IMPORTRANGE(Expenses_2019Q2,"DATA_Expenses")},"...
1
vote
1answer
11 views
COUNTIFS issue, output is always zero
I have no idea what I'm doing, considering there's plenty of topics on COUNTIFS. However, each solution I've tried, as well as my own solutions, which I shall list, end up in the same thing... ZERO. ...
0
votes
1answer
12 views
How to select existing sheet to be populated by a Google form?
I have a team working in pan India with 6 Team leaders. I have created a Google form to collect the everyday status report.
Now the question is:
How can I assign a particular sheet[already created in ...
0
votes
1answer
21 views
Grabbing only non-empty cells from a row range for every row
I have a survey that sends responses into a Google sheet. The raw data that is inserted is messy, so I have another sheet that processes the incoming raw data.
One of the things I need to do is: from ...
1
vote
1answer
19 views
Compile cells when cell contains certain value
In Google Sheets, I have a range of values as follows:
I am looking for a formula that will search the range, find all of the cells that is a 'wire' (contains the term "wire") and outputs the range ...
0
votes
2answers
13 views
Format cell if all the cells in a row are not empty
I tried all kind of formula but they all seem to work the other way around for me. I have a very large sheet with lots of columns. I'd like to have the first cell in the row to be a certain color if ...
1
vote
1answer
24 views
How to convert multi-value answers into single column in Google Forms' response sheet?
I have a Google form that asks for Name and up to 3 Score. The data is dumped into a sheet like this
But that data is not very convenient. I want to have the data like this...
Can I do that by ...
1
vote
1answer
16 views
Finding multiple “most common values” in a column
I am trying to display the 5 most commonly appearing text values in a column in Google Sheets.
The following will give me the most common value in column A:
=ArrayFormula(INDEX(A:A,MODE(MATCH(A:A,A:...
0
votes
1answer
12 views
Description based on each checkbox “checked” to populate into a summary on the 2nd tab (sheet)
I am building a spreadsheet that has to tab (sheets), the 1st I have an "assessment checklist" with about 250-30 checkboxes based on what is found. Example (Checkbox is in "A" column and description ...
0
votes
2answers
14 views
How Do I Quickly Merge A Large Amount Of Cells?
So I'm trying to merge all cells in Col A and B so that A1 and B1 are one cell all the way for 150 rows, anybody knows how?
0
votes
1answer
12 views
How to ignore certain strings when creating unique list of words in Google Sheets?
In Google Sheets, I am using the formula to create a unique list of words:
=unique(transpose(split(ArrayFormula(concatenate(B2:E"+")),"+")))
But I want to filter out certain strings.
My unique list ...
0
votes
1answer
23 views
Method to preserve cell reference in a formula upon adding rows in reference sheet?
I have a Google Sheets with two(2) tabs. The first sheet is just a running tally of raw data and is updated with new rows at the top weekly.
In tab 2 there is just a single query(arrayformula)... ...
0
votes
0answers
17 views
Order query() by dates using the format Mon/D/YY (Apr/13/19)
I'm importing data from a sheet that uses the Mon/D/YY date format (Apr/13/19).
When I try to sort the entries chronologically with the following request, they're instead sorted alphabetically (A is ...
1
vote
1answer
16 views
Selecting row with best value for items with several versions/upgrades
I'm sure my problem is quite simple to solve, but I've been on it for a while and I could use some help.
I have a huge table, but the problem boils down to this: some items have 2 versions (one ...
1
vote
1answer
11 views
Autopopulate a RANGE of Cells from one sheet (tab) to another in a single workbook
All I want to do is have cells in one sheet (tab) in a workbook auto-populate with data and formatting of a different sheet (tab) in the same workbook. I know how to do this one cell at a time, BUT is ...
0
votes
2answers
16 views
How can I group by one column to join values in multiple rows?
Let's say I have a survey of people in teams, and what fruits they like:
User | Team | Fruits
----------------------
Dave | Mine | Banana, Apple
Steve | Mine | Apple, Orange
Matt | Yours | ...
0
votes
5answers
29 views
Layperson's grid to normalized table
Introduction (too long, don't read)
I have a data-arrangement problem. I'm now up to three qualifying files from coworkers, and for that matter, I've run into a couple of questions here on SE ...
0
votes
1answer
30 views
Reference all tabs with a specific word in COUNTIF statement
Basically, I am trying to make a master tab that adds up all of the data from all the other tabs. These tabs are weekly breakdown numbers for ad statistic numbers, so there is a new tab added every ...
1
vote
1answer
9 views
Finding the number of instructors available on different weekends in Google Sheets
I'd like to find a generic way to see when resources are available, in order to help plan when activities can be run. In this case, the resources are people with certain instructor qualifications (...
