We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [google-sheets-query]

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

126 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
3answers
140 views

How to remove duplicate words in a cell only if longer than a certain number of characters?

I need to remove a duplicate text in a cell but only if it is longer than 4 characters. I have this formula that only works for any types of duplicate words: =join(" ",unique(transpose(split(A1,", ")...
2
votes
1answer
2k 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 |...
2
votes
1answer
380 views

How do I reference the previous column of a calculated field on a pivot table? Looking to calculate percent change

I have a pivot table with a bunch of columns indicating the value over time, and would like to create a calculated field showing the change over each value. How would I reference the previous (and ...
2
votes
1answer
834 views

Aggregate results by week and month for charts

Is there a way to convert daily values into weekly averages for charting? Sample source sheet --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc......
1
vote
1answer
12 views

help with query finding multiple entries in 1 column

I am trying to query several different colors from one column. This is what I have so far: =query('Raw Data'!C1:J1000, "Select E, D, I, J, C, F, H where H contains 'Monday' and F='Green' ...
1
vote
0answers
10 views

Using a query on an importrange, not pulling information

I am trying to do a query on importrange but it is only showing me the headers not any of the data. I am really stuck. =QUERY(IMPORTRANGE("19Nx2EtMktFW-xwNMjY4IiQpN33QHrXIwcqDsvKqCiHo", "MASTER!A1:...
1
vote
0answers
29 views

Multiple columns and get unique on preferred column

I want to get 3 columns only if a preferred column is unique. What is the correct syntax in Google Sheets? =Query('Sales'!A:AX,"Select A, B, C where A is unique")
1
vote
0answers
36 views

Google Sheets - Query Multiple Columns for same Data

We have Employee training data in a Google 'Form Response' spreadsheet which ranges from A1 to JQ1000. This spreadsheet is automatically populated when a Google Form is completed but it is difficult ...
1
vote
1answer
34 views

Why does close price results have a date shift (one day off) while using GOOGLEFINANCE in a Query

I want to get last 40 days close price of stocks. I used below formula for the same but the result not showing the last day close price. =QUERY( GOOGLEFINANCE("nse:"&B2,"all",$A$2-80,81),...
1
vote
1answer
32 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 ...
1
vote
1answer
30 views

How can I change the formula of one cell based on the value in a different cell?

COLUMN D: Dropdown list of values COLUMN E: IF function returning different values depending on COLUMN D contents I have the functionality described above working properly. My problem is that I want ...
1
vote
1answer
297 views

Google Sheets Pulling data between a specific date and time, from a timestamp

I am trying to pull specific data from a timestamp that I will be using in several pivot tables. Here is my workbook https://docs.google.com/spreadsheets/d/1iYHYdATK06Wxl-Jtxe2P92Kh50XEsWgwx5d-...
1
vote
1answer
14 views

Issue with finding the averages of multiple rows at once

I am currently tasked with hundreds of rows of data for a project. I need to take the average of each row, but there are way too many rows for me to do by each individual row. Is there any way to take ...
1
vote
1answer
32 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 ...
1
vote
0answers
137 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. ...
1
vote
1answer
24 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
1k 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
2answers
108 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 ...
1
vote
1answer
108 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
68 views

How to combine dynamic columns in Google Sheets while retaining data in other rows?

I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns. I want to ...
1
vote
1answer
280 views

How to concatenate cells using ARRAYFORMULA across rows and columns?

I have a document that's a recurring CSV import/export. I'm trying to concatenate images separated by ", " and using ARRAYFORMULA to make it all the way down. The problem is, I don't know how many ...
1
vote
2answers
97 views

Google Sheets' Query or Filter solution

I have a simple table with columns: A (Donor ID) B (Donation date) C (Donation Amount) I need to extract the following report: For each donor ID most recent donation date and amount donated ...
1
vote
1answer
38 views

How to join VLOOKUP results of multiple sheets into one cell

I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it. In this example, I am ...
1
vote
3answers
828 views

Select last entry from spreedsheet

So I have a Google Form dumping data into spreadsheet tab form responses 1. I have created another tab labeled summary. I need to have the summary tab only show the latest entry for a given pilot ...
1
vote
1answer
1k views

Query Importrange with multiple values

This formula is working fine, but I need to add in other teams into Column A. I would need to return ATT SLC, ATT Orem, and ATT Scottsdale within this same range, but I'm not sure the language to use ...
1
vote
1answer
742 views

Query based on column header in Google Sheets

I am trying to automate Worship list using Google Sheets, so it works good but one thing I can't do can be more than useful. I have a sheet like this: in row 1 dates and below songs used that Sunday....
1
vote
3answers
59 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. ...
1
vote
1answer
27 views

How to summary the multiple entry into a single sheet by name and by date?

Names are in rows and Dates are in columns. Sheet 1: Name 1-Jun-18 2-Jun-18 3-Jun-18....... KB CL KB EL AJ SL SD SL SD CL I need ...
1
vote
2answers
14k views

Google Sheets formula for “if contains” from range

I try to figure out how to get a value based on "if some word is in a range". So formulas from (Google Sheets formula for "if contains") works: =IF(ISTEXT(REGEXEXTRACT( A1, "sites")), 1,0) ...
1
vote
1answer
2k views

Query returning results into merged cells in Google Sheets

Short Question: I have a query function returning an array over merged cells. Instead of displaying all of the values in the array, some of the data is hidden in merged cells where only the first ...
1
vote
3answers
809 views

Copying data from one cell in another based on date

I have a spreadsheet, using google sheets, that I have formed into the following metrics: Column A is the dates of the month Column B is the data I want to be able to copy the data from Column B ...
1
vote
1answer
787 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 ...
1
vote
2answers
220 views

Is it possibile to concatenate with bidimensional arrayformula?

I have a table like this: +----------+----------+----------+----------+ | Photo1-1 | Photo1-2 | Photo1-3 | | +----------+----------+----------+----------+ | Photo2-1 | Photo2-2 | | ...
1
vote
1answer
488 views

Querying multiple ranges from form responses to new sheet

Recently, someone on this forum put together a formula for me. It works on a sheet pulling responses from a google form. I'm trying to model a new one after that but I'm having trouble. The working ...
1
vote
1answer
32 views

How to Iterate between colunns and rows?

I am running the following forumula: =IF(INDEX(C$2:$2, 1, ROW(1:1)),CONCATENATE(B$2,"-",INDEX(C$1:$1, 1, ROW(1:1)))) On the following table Basically, If a value is present in column C2, I am ...
0
votes
0answers
17 views

UNIQUE ,INDEX AND IMPORTRANGE

I am stuck. I use the import range to get a set of data however now I need to the unique values that that matches the import range that I have. Say for example below is randome sample. I have import ...
0
votes
0answers
8 views

QUERY Function with sort

I need to get data from tab 1 to tab 2 but not empty cells. I think the function QUERY should work but can´t get it to work. I made a test sheet you can check at this link: Test
0
votes
0answers
17 views

Query Import Range returns two rows

I've been using =Query(Importrange( functions for quite a while now and something I can't figure out is why the import is returning with two rows. Here's the formula I've been using: =QUERY(...
0
votes
0answers
8 views

Moving Rows of Google Form Responses to a new Sheet based on a specific answer in a column

I have a Google Sheet collecting responses from a Google Form. Based on answers in Column C (Col3), I would like the entire row imported to a completely different Google Sheet. I figured out a ...
0
votes
0answers
9 views

Change entire sheet information based on input of one cell

I'm trying to create a function on a secondary sheet to monitor which of my blogs are getting the most traffic. Currently my secondary sheet has a formula that works to show me blogs that have over ...
0
votes
0answers
7 views

Google Sheets data not loading in pdf

I have a Google Sheets with some tabs, but not yet a massive amount of information. I have one tab where data is put in and then two tabs where this data is being displayed by a query formula. This ...
0
votes
0answers
24 views

Loading Data Error Query with Importrange Gsheet

I am trying to use QUERY with importrange function to fetch custom data from another spreadsheet, it worked for a while but now it through loading data error consistently. if I refresh the sheet it ...
0
votes
1answer
21 views

In Google Sheets I need to find a string and return true/false, or even a count into a cell so I can perform an action

In my example i have a column with Names of signs we product in our sighshop and I need to be able to isolate similar named signs So say I need to return a a value if the cell is Bud Light and only ...
0
votes
0answers
26 views

Unable to filter rows in a sheet populated by QUERY

I am trying to filter one column A-Z to pair items listed in more than one selling app. However, if I apply an A-Z filter for a column with item codes, the sheet rearranges everything per the filter ...
0
votes
1answer
39 views

Include filename of source spreadsheet

I have 2 spreadsheets for 2 employees and I combine them into 1. From employee 1: From employee 2: I use importrange to combine the sheets. I also know now how to get the spreadsheet file name by ...
0
votes
1answer
55 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 ...
0
votes
1answer
32 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 ...
0
votes
2answers
111 views

Consolidate columns from a dynamic range into a single column in Google Sheets

How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case, I want non-unique cells. Also, each cell in the range ...
0
votes
2answers
22 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 ...
0
votes
0answers
5 views

Query to transpose data with col 2 offset for column headers

DATA: Col 1 = Job numbers Col 2 = Task numbers Col 3 = Date I would like to have: Col 1 = Job numbers All other Col = Dates with Col 2 as headers transposed So something like: query(DATA,"select * ...