Questions tagged [google-sheets-query]

Use for questions about QUERY built-in function from Google Sheets.

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
32 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 ...
1
vote
1answer
32 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
23 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 ...
1
vote
1answer
15 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
19 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
19 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
21 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
10 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
9 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
9 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
63 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
32 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 ...
1
vote
0answers
44 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
42 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 ...
2
votes
1answer
16 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
25 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
103 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
15 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
25 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
26 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
62 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
21 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
12 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
37 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
28 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
57 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
17 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
25 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
20 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
15 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
15 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
23 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
27 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
20 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
14 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
32 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
32 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
10 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 (...
1
vote
3answers
87 views

Filter cell data using Query/Importdata

I am attempting to grab data from a cell, by using additional data in the cell above it to make sure its correct. Here is the data, each line is put into a cell by the formula. <total_count>6&...
0
votes
1answer
24 views

Replace value in field automatically (auto-correct?)

I'd like to replace occurrences found in Column A (cells A2:A200) so that if I enter 2/02/002 or 343 or 98/098 it auto-replaces it with UKD002/UKD343/UKD098. Is this possible?
0
votes
1answer
15 views

Collates the number of depots that don't have manifests and trunk headers

Trying to do a work project that collates the number of depots that don't have manifests and trunk headers. Column A: Depot Numbers Column B: No Manifest record (i.e if Colum B 2 - 200 contains "...
0
votes
1answer
15 views

INDEX + MATCH to return the greatest of all matching values

I'm looking for the most recent date corresponding to a particular name. Dates are not necessarily ordered. I can't sort the target, I need it to work in any order. Example: When was [Roberto]'s most ...
1
vote
2answers
38 views

How to pull last value in a Google Sheets column and populate it across another worksheet column?

This is my first post here- I hope this is the right place for it, apologies in advance if it isn't. Context: I have a password protected presentation that I need to give users access to, but only ...
1
vote
1answer
25 views

How to filter only the highest value for each date based in a criteria?

I have a table with 3 columns. I want to filter only the highest value of column B for each date (A) if column C contains the code "red". My sheet: date entries code 08/04/2019 1 red 08/04/...
0
votes
1answer
23 views

Combine multiple sheets in one master file

I am applying the below formula where I want to combine the live data from multiple sheets to one consolidate sheet in horizontal format & here my header is same... but I am getting an error as: ...