Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
2k 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
1answer
515 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
20 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
603 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
968 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
124 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
70 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
396 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 ...
2
votes
1answer
3k 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:...
-1
votes
1answer
31 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
16 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
406 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
98 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)... ...
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
436 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 ...
-1
votes
2answers
60 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
57 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
71 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
12 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
315 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
2answers
211 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
18 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 "...
1
vote
1answer
25 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 ...
2
votes
2answers
5k 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
464 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
54 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: ...
1
vote
1answer
21 views

How do I arrange raw data in Google spreadsheet in group way?

I have a list of orders, like this: Paid $28.00 | 2019-04-08 15:14:20 Unpaid $00.00 | 2019-04-08 15:13:48 Unpaid $00.00 | 2019-04-08 15:12:58 Unpaid $00.00 | 2019-04-08 15:12:10 Paid $18.00 |...
-1
votes
1answer
11 views

How to split a cell into key/value rows?

Apple,3,Orange,4,Mango,10 How can I split this cell into key/value rows? Apple (a1), 3(a2) Orange (b1), 4(b2) If I just call split(), I get: Apple 3 Orange 4
-1
votes
1answer
16 views

Outputting a cell x number of times in an array format

Using Google Sheets: Need to display a vertical list, where each occurrence in the A column of sheet1 is repeated based on the number in the B column of sheet1. I've created a dummy file to show what ...
0
votes
1answer
30 views

Value transformation in Google Sheets

I'm working with a table of data as following: User ID: Event name: 111 Event 1 222 Event 2 333 Event 3 111 Event 4 444 Event 5 111 ...
0
votes
1answer
503 views

How to disable new row when Google Form collects responses for Google Sheet?

When Google Forms collects responses, it creates an entirely new row in the Google Sheet which I don't want to happen because I am using a VLOOKUP for another sheet and it's not grabbing the data I ...
1
vote
1answer
25 views

How to get the largest initial values of multiplied columns?

I'm tracking performance for some athletes and we need a way to see their "Best" workout numbers meaning the highest numbers of (rep × weight). The table looks like this: The reps and the weight are ...
1
vote
1answer
36 views

How can I encode HTML literals for output within the Sheets Query function?

I have a query in a spreadsheet. What I need to do is output a bit of simple HTML text between each column being pulled out of another tab. Sample code looks like this: =Query(Official!A7:P617, "...
-1
votes
3answers
29 views

Summing every other row depending on the classification of the row above

I've got a confusing question, I can't figure it out. I have this spreadsheet of states population and their political control, each on separate alternating rows (political control is designated with ...
1
vote
2answers
38 views

Return values to another sheet if a cell is not empty, otherwise skip to next cell

I have one sheet with a list of phone extensions in which some have names associated with them and some do not. I am trying to use a function to display one the extensions with names onto another ...
0
votes
1answer
198 views

Summing hours spent on a subject in Google Sheets' “Assignment Tracker”

Hi everyone! I love the idea of this Assignment Tracker in Google Sheets. But my goal for time management is to find out how much time I'm spending on each subject. Is there a way to automatically ...
0
votes
1answer
413 views

Linking a dynamic inventory price list to another file in Google Sheets

I've got a master inventory price sheet for all the ingredients we use in our recipes that I want to reference in all our recipe files to dynamically calculate costs. This way if we change suppliers ...
1
vote
2answers
229 views

How do I get the count number of weekdays in a datetime column in Google Sheets?

I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday? This gives me a ...
0
votes
1answer
23 views

Cross Comparing values between two columns for voting purposes

The current content of our Google sheet, is formatted as such: - PERSON 1 | PERSON 2 | YES - PERSON 1 | PERSON 3 | YES - PERSON 2 | PERSON 1 | YES - PERSON 2 | PERSON 3 | YES - PERSON 3 | PERSON 1 | ...
0
votes
1answer
46 views

SUM across other worksheets (without hardcoding their names)

I've got a spreadsheet for my organisation's management accounts. Each project has a worksheet, setting out income and expenditure by financial year. This is aggregated together in a few sheets ...
0
votes
1answer
59 views

Single QUERY for most recents of two groups

I have two sheets with the same columns. I need a single query to pull the most recent matching record from each of the two sheets. Here I'll just show them as adjacent ranges: | Stu | Date ...
-1
votes
1answer
641 views

Reconnecting form responses to current sheet after file move to Team Drive

I have searched for an answer for the "current" Google Forms problem I am having. It seems the answers here for this issue are outdated as Google keeps changing their technology about as fast as I ...
1
vote
1answer
660 views

Using QUERY and IMPORTRANGE to get a value from Col8 when some of the rows in Col1 are subheadings

I just want to return the value in Col8 corresponding to the row that has "Residential". I can only seem to get the value that is in the top row rather than the row corresponding with "Residential". =...
0
votes
1answer
521 views

ImportRange not pulling in Dates from first sheet in correct format

I'm trying to pull in a date cell from one sheet to another with an importrange function: =iferror(query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1UClhpev01z-...
-1
votes
1answer
17 views

Query with Date Selector. Both must be inputted or no information is available?

Currently using: =IFERROR({QUERY(PRI!A4:T,D1,1);QUERY(NON!A5:T,D1,0)},"Nada") This is used to grab information from two spreadsheets with the same number of columns, with PRI grabbing data with ...
1
vote
1answer
24 views

If a dropdown value is selected, how do I add it to another column and then gather the sums?

So I have a dropdown in Col K with "Apple, Oranges, Grapes, Cherries". If a kid wants to order a specific fruit, I want it to auto-populate the section of Col N in green and then sum the total ...
1
vote
1answer
556 views

Arrayformula for running MAX of separate column

I'm trying to use ARRAYFORMULA in Google Sheets to get the max figures from a separate column only up to the row being calculated each time but can't for the life of me figure out how. It sounds more ...
1
vote
1answer
42 views

Sort by a Column While Maintaining Information in a Row

I had a previous question with lots of support and it solved my problem. The next issue I have is sorting the data while bringing the information in the corresponding row with it. A link to the ...
1
vote
1answer
128 views

Report the date/column header for the first and last non-zero entries for each client/row

I have a sheet with client names (Column A) and the number of visits to the business by mm-yyyy (Columns B - AC). I'm trying to find the first month that they visited the business and the last month ...
1
vote
1answer
44 views

vlookup sum to calculate total hours assigned next to dates

I'm attempting to calculate a total number of hours based on entering 2 different dates. Each day between the dates has assigned 'hours worked'. I'm trying to use Sumproduct or Arrayformula and Sumif,...

1
6 7
8
9 10
15