Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
5 views

Is it possible to include the filename of the spreadsheet using the query?

I have 2 spreadsheet for the 2 employee and i combine them all in 1 spreadsheet. From employee 1: From employee 2: In masterlist of task i want to automatically fill the column for QA NAME from ...
1
vote
1answer
20 views

Output counted list with extra qualifier

I currently have a table with the following columns: Comm ID Item Name Months Orichalcum Tempest Jade Green Iron Weltsilver Ambergelt Beggar's Lye Dragonbone Iridescent Gloaming ...
1
vote
1answer
19 views

How to combine same value for Google sheet query?

I am trying to create a separate tab in my Google sheet where I can filter the Trainer to see which days they will be conducting a Day 1 and Day 2 Training. Day 1 Training: =iferror( query('...
0
votes
1answer
119 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
22 views

Summarise data in a pivot table, but exclude certain data if it has a certain value in a certain column

I've got some data in a Google Drive spreadsheet that I want to run a pivot table on. There are 3 columns Element, Cost, Omit as per screenshot below I want to summarise the cost by element, but ...
2
votes
1answer
18 views

Dynamically reference data from multiple tabs and sort it

I've got a spreadsheet with multiple tabs of data. The data contains 2 columns, A = Categories, B = Quantity. I've created a copy of the spreadsheet HERE (please use file > copy) if you would like ...
0
votes
1answer
32 views

How to sort Column B to match with column A in Google Sheet?

I have 3 columns in Google Sheet. Column A have 95 rows with value. Column B and C have 500 rows with value: I need to sort Column B to match with Column A. Column C represent value associated with ...
1
vote
1answer
28 views

Visualize Data that updates dynamically

TLDR: How can I create a bar chart that updates the x-axis daily? The end goal is to have a chart that shows a side by side comparison of goal completions performance for each source of traffic. Here ...
2
votes
1answer
18 views

How to sum the values of same vlookup results?

I have this formula it gets the single data from another file from a sheet at column 7. =ARRAYFORMULA( if( len($A2:$A); iferror( vlookup( ...
0
votes
1answer
14 views

Importing answers from a Google Form into another Google Sheet, then adding a column for notes

I have been using a Google Spreadsheet with a master list created from a Google Form. I created a new tab to rearrange the order of the entries to the newest ones on top. However after those cells ...
2
votes
1answer
1k views

Calculate Percentage with QUERY on Google Sheets

I have a Google Sheets database like this: Name | Value | Answer A | 100 | Y B | 200 | N C | 10 | Y A | 100 | N A | 1000 | Y B |...
0
votes
1answer
18 views

Query runs but returns first row greater than the DATE at 00:00:00 not the row after 10:00:00

Trying to use the following query: =query(WABrdg729!A:B, "select A, B where A > date '"&text(datevalue($A$6), "yyyy-mm-dd")&"' AND A > timeofday '"&text(timevalue($A$6), "hh:mm:ss"...
0
votes
1answer
19 views

Cell Reference in a QUERY statement not working

I'm trying to add a cell reference (on the same sheet where the QUERY function is at) to a QUERY function On B1 there is this value: 13/09/2019 QUERY function: =QUERY(FORMULARIO!A1:S, "SELECT G ...
0
votes
1answer
362 views

Using array formula in Google Sheet

Trying to make an ARRAYFORMULA that executes a query function which takes a reference to the current position of the row number and the specified column name. This is my version of formula: =...
-1
votes
1answer
21 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
1answer
42 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")},"...
0
votes
2answers
29 views

Showing top 5 most frequent values and counts of each from a data range

I have a Google sheet with text strings listed down columns D through G. I want to, on another sheet, display a list of the top, say, 5 most frequent unique values from the first sheet in descending ...
2
votes
1answer
2k views

formulas to transfer certain data from one sheet to another

I have a list of volunteers in sheet one. They have selected what programs they want to participate in with a yes or no in the column under that program. How can I create a formula that says if this ...
3
votes
1answer
39 views

Importing a row from one Google Sheet to another

Doc1: I have individual sheets setup for each Game Week which list the football matches being played on that particular week. Doc2: I have an individual sheet setup for each team. I'm looking to pull ...
1
vote
1answer
19 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
vote
2answers
86 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
57 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 ...
-2
votes
1answer
89 views

Query is not bringing in data when column is between two dates [closed]

I am using the correct formula which has worked on other sheets: =QUERY(Patients!$A:$K,"SELECT * WHERE F > date '"&TEXT(B2,"yyyy-mm-dd")&"' and A <= date '"&TEXT(C2,"yyyy-mm-dd")&...
-1
votes
2answers
21 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 | ...
1
vote
1answer
523 views

Google Query, change select columns based on cell reference?

I am trying to improve a manual process that requires filtering through the input from a Google Form. It would be great if we could filter each question individually, without building multiple queries....
0
votes
2answers
59 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?
1
vote
2answers
339 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 ...
0
votes
1answer
20 views

Trying to make a column of “select count” in reference to another column

I am pulling data from multiple sheets in my workbook to create an array. In the first part of the array I pull data straight from a sheet like a user name # of points and rank. Now for the rest of ...
0
votes
3answers
35 views

How to use Importrange in a Query and filter by dates

I am trying to IMPORTRANGE if the condition in column1 that is text formated ( Column1 = September 4, 2019 alas 5:10 pm ) contains the word September. I am using the following formula. The other ...
0
votes
1answer
18 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 ...
5
votes
1answer
8k views

filter or sort Google Spreadsheet based on condition

I have an 8 column spreadsheet. I want to sort/filter based upon the value of the 4th column. In a separate sheet within the same spreadsheet, I want to print a list of each row matching a speific ...
-1
votes
1answer
7 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
0
votes
1answer
129 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 ...
0
votes
1answer
18 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 ...
-1
votes
1answer
15 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
291 views

Google Sheets Using Countifs & Unique Together

I am trying count the number of unique clients of Payroll service who are Won. The answer as per the table below should be 4. How can I achieve this, given that the table will be dynamic?
1
vote
1answer
4k views

How do I filter the array returned by a filter or other formula?

I want to filter an array returned by a filter to remove blank columns. Here is a picture of an example sheet: Here is the example sheet. I am filtering the large table by the rows that are of type ...
1
vote
3answers
49 views

Pulling data from one sheet to another if

My knowledge is pretty basic when it comes to excel and Google Sheets. I'll explain what I'm trying to do. I'm trying to find a way to filter information from 1 sheet to another in Google Sheets. ...
0
votes
1answer
24 views

How to combine 3 sheets into one using QUERY(IMPORTRANGE)?

I am trying to combine 3 different sheets of data from range A2:O Col1, Col4, Col5, Col6, Col8, Col11, Col12, Col13, Col14, Col15 using Query({IMPORTRANGE(... function. I tried using the formula below ...
1
vote
1answer
927 views

IMPORTRANGE syntax when using query on multiple tabs

I am using IMPORTRANGE within a query to extract data from sheet 1 to sheet 2. The following code is in a cell in tab A of sheet 2, and works as intended: =query(importrange("URL-A","E:J"), "select * ...
0
votes
1answer
1k views

How to Fix Error in Multiple Importrange

I am creating a table to display data from a combination of several Spreadsheet files by using Multiple Importrange. = QUERY ({Importrange (G3, "Sheet1! A2: D"); Importrange (G4, "Sheet1! A2: D"); ...
1
vote
3answers
40 views

How to use QUERY to show data in Google Sheet using tags

I have a Google Sheet with 2 sheets. In sheet 01 I have this table. In sheet 02 I want to have a "Search by Tag" sheet. So, googling I got this formula =QUERY('01'!A2:D4, "select B,C,D where A = '...
0
votes
2answers
186 views

Google Sheets Lookup For Nth Value

I am working on a spreadsheet that seeks to collate scores to a team. There is an input table where team and score start next to each other (see the example sheet linked below) into a table with each ...
1
vote
1answer
2k views

Using SUMPRODUCT with ARRAYFORMULA

I have the following random data in sheet one A orange D yellow C yellow B orange B orange C blue A yellow D blue C orange C blue In sheet two I use the following query function: A1=...
0
votes
1answer
607 views

Date Query in Google Sheets with importrange

I have two tables. Table A= Customer Feedback Table B= KPI In Table A I have a column for a date with format mm/dd/yyyy and in Table B I have 2 columns as from date and to date with the format. I ...
1
vote
2answers
31k views

Help with a “=QUERY(IMPORTRANGE(…” formula in Google Sheets

Having trouble with a =QUERY(IMPORTRANGE(... formula in grabbing a value from a single cell in another sheet, but only in the row where a particular text value exists in another column in that row. I ...
1
vote
1answer
686 views

In Google Sheets Query Max Date from Dates in Column A for a particular Project ID stored in Col B in Tab 'Data' from Tab 'Main'

Tab Data is as under Date_A Project_ID 01-May-18 EL-PR-18-08709-1 02-May-18 EL-PR-18-08709-1 01-May-18 EL-PR-18-08709-1 03-May-18 EL-PR-18-08709-1 02-May-18 EL-PR-18-08709-1 01-May-18 ...
0
votes
3answers
27 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 ...
0
votes
2answers
16 views

How to filter after parsing CSV values contained in Google Sheets cells

I would like to be able to match all cells in column A (titles) that have one of the comma-separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the ...
1
vote
1answer
36 views

QUERY function to search three columns for the same keyword?

I have a table with data in columns A-U. Rows go down to 368. I would like to pull (onto a different sheet) all the information in columns A-F where it matches a keyword from a dropdown menu (in cell ...