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
4 views

Google Sheets Query Cell Reference

I'm trying to have my query reference a cell, but I keep getting errors =query(Sheet1!A3:X2838,"Select I, H, Q, P, J, N, O, S Where (N>10) and (J>"""&K2&""") Order By Q Desc Limit 80")
0
votes
0answers
5 views

Google Sheets: Transforming a single date column in a multi-column query

I'm importing a HTML table into my Google sheet with IMPORTHTML. I need to parse date strings to real dates in the first column so I can format them and sort them properly. The table structure is as ...
1
vote
1answer
38 views

Google Sheets - Get unique values from a column and return the values a single column

I was hoping to get some help and direction. I have two sheets--a 'Raw data' sheet and a 'Dashboard' sheet. A user would dump the raw data in the first sheet and the dashboard will summarise figures ...
2
votes
0answers
37 views
+50

Group by in query Google Sheet select different columns

Want to get the results described on the screenshot. How can I achieve it. QUERY: =QUERY(A2:F12,"SELECT A, MAX(B), MIN(B) group by A label A 'G V1', MAX(B) 'LAST V2', MIN(B) 'FIRST V2'") Want to ...
1
vote
0answers
9 views

Data has both text and number, I need to move to another sheet from master but only the numbers show up…I need both

I've used this formula =query('Form Responses'!1:136,"Select * Where E='name'") but all I get is the numerical value entered in a cell. If someone enters both a number and text the cell is blank. In ...
1
vote
1answer
73 views

Find the last instance of multiple values

I really need some help with this. I need to search for the value of A2: B2 in another tab in google sheets and it needs to return the row number of last occurrence/entry of the values in A2: B2 in ...
1
vote
1answer
31 views

Using a query and tick boxes to auto fill another tab

I'm new to this and I'm racking my brains over how to get this to work, I can do basic IF & Vlookup but I believe I need the merge the 2 for this one. Is there a formula I can use on the "NLN" ...
0
votes
1answer
30 views

Filter for a QUERY function

I need some help here. So I need a function which imports selected columns from a different sheet out of which one of them contains a specified word but allows you to filter/ignore based on specified ...
1
vote
1answer
29 views

Trying to pull Duplicate data from multiple columns

https://docs.google.com/spreadsheets/d/1VSiKZbD_PoQTfbav7spalkyBgrt4T8juaLlKOM7LgLM/edit#gid=0 I have two columns, A and B. Throughout this project, the same name will appear in each column. I want ...
1
vote
0answers
12 views

Find Difference from Previous Consecutive Row in Google Query Pivot Table

Say I have source data like the below: OriginDate|Name|IncidentDate| where Origin Date is constant and the earliest date of my imported data set. I've formatted the dates to numbers so I can perform ...
1
vote
2answers
40 views

Return the closest value from two-dimensional data

I have an input value and a table of two-dimensional data in Google Sheets. I would like to output the value from the table that is closest to the input value, either without going over the input ...
0
votes
0answers
5 views

Macro to copy and paste values from multiple cells as text into a Note

I'm new here and struggling a bit with the javascript in macros. I just need a steer as to how to get a macro to copy from a range relative to an active cell. I'm a showcaller, that is, during live ...
2
votes
1answer
39 views

How do I populate cells (with formulas) from one google sheet into another while using one as a search key for the other? If C=This, State D

I'd like to populate Columns C and D in an entirely new spreadsheet, where Column D still auto-populates the cells just as it does in the original/first sheet (as it does in the photo attached inside ...
1
vote
0answers
10 views

In a large document with many comments how do you review everything, both open and resolved comments, page by page?

The 'Comments' button allows me to see the comments in date order but that doesn't give me the context of the page it was on. I have no idea when some of the comments where made, that I am looking for,...
0
votes
1answer
17 views

Google Query: filter one record per day/week per user

I'm collecting repeated measures using Google Forms which only allows 1 or unlimited responses. I need a way to filter out responses that were entered by the same user too near to each other. For ...
0
votes
0answers
9 views

Using query function to iterate through columns in Google Sheets

I am trying to produce the average of the difference of two columns under the condition that one column is not null, for 10 different columns. I want the column being subtracted from to iterate in ...
-1
votes
1answer
22 views

Exponential Moving Average

What is the formula of Exponential Moving Average (EMA) in Google sheet ? How it can be used to calculate Share market stock EMA of different period. Eg 5, 21, 50 Days. Can anyone share Demo sheet.
1
vote
1answer
30 views

How to sum up to 4 (or more) columns in a query based on checkboxes?

Basically I have 4 checkboxes and I want to sum up to 4 columns based on those, with the formula query: It's a bit ugly what I di and I was wondering if there was a more elegant way of doing it, ...
1
vote
1answer
61 views

How to populate a cell based on specific name and specific date using a query function?

Hello new to the forum here and relatively new to all this data business. I feel pretty confident in my skills but this one has me stumped. Below you will see my initial query of dates, ditch_name(...
-2
votes
1answer
23 views

Google Sheets atomisation

I hope you are all well and healthy! I am looking for a solution in Google Sheets to automate something like this: and it could auto generate something like this Thank you in advance
2
votes
1answer
10 views

Google Sheets query how to make MATCHING skip (not match with) blank cells?

I am running a query in Google Sheets to analyze a bank account statement's transaction text (column D) against predefined categories (found in range $T14:$BG14 for this particular row). My problem ...
0
votes
0answers
25 views

Divide 1 row of data into multiple rows - Registration Data

I have a Google Form for registering multiple children. There is only one mom, but she can have anywhere from 1-3 children she is signing up for daycare. The form give me a sheet with a row with all ...
0
votes
0answers
12 views

Multiple IF statement including formulas as True response

https://docs.google.com/spreadsheets/d/1siXWH_83AFnymr58f72xBmwFlPG1O5ZHZgA1JqQNt0w/edit?usp=sharing Above is test document. I am trying to import information from sheet "AM Trip" to Sheet "BL AM", I ...
1
vote
1answer
21 views

Query Google Sheets Using Week Num to order dates

I was finishing ordering timestamps and dates, and I can't quite get it to work: =QUERY(Sheet3!A:I, " SELECT D, SUM(I) GROUP BY WEEKNUM(D) ORDER BY D DESC LABEL SUM(I) 'Balance Semanal' ...
-1
votes
1answer
23 views

Using “IF” or “Query” statement paired with “ImportRange” to bring in data set from one sheet to another

I have a sheet where I input my jobs booked. I want to be able to import information for the first 4 columns (Job Number, Job Name, Salesman, Date booked) if Column F (Permit Required) has a "Yes" in ...
1
vote
1answer
62 views

Left Join in Google Sheets

I have A and B. Google working example here: https://docs.google.com/spreadsheets/d/157x9Yg9tG2pmV-q4wZVn0NW1xas4VSin3zASbrCV3t4/edit?usp=sharing Sheet A stores data about patients. Sheet B ...
1
vote
1answer
34 views

Sum category column in one sheet based on a dates column in another sheet [duplicate]

I have a sheet, Transactions, that looks like this: +--------------+-------------+-------------+-----------+ | Date | Payee | Category | Amount | +--------------+-------------+----...
2
votes
1answer
30 views

Query a column based on current date

I'm trying to query a column based on the current day. Examples: If today is 1st day of the month I need to: =QUERY(table,"SELECT A WHERE B CONTAINS 'this text'") If today is 12th day of the month ...
2
votes
2answers
39 views

Copy entire rows from one sheet to another if certain cell in first sheet does NOT contain certain text value

I have two sheets. The first one has incoming data that dynamically updates. The fields are Name, Last Name, Email. I want to duplicate the rows in a second sheet but only if the email field in sheet ...
0
votes
0answers
11 views

Google sheet query, number of columns in table affect speed

I have a large Google sheets tab. It has about 1500 rows and the columns extend out to AR. It was querying really slow until I got rid of a few columns and shrunk the columns to AB. The pick in ...
1
vote
1answer
21 views

How do I get a list of all values in the same column across multiple spreadsheets within the same Google Sheet? [duplicate]

I have a Google sheet that has multiple spreadsheets, let's say Sheet1, Sheet2 and Sheet3. In all of those spreadsheets, column H is a particular field of which I want all the values with blanks ...
0
votes
1answer
19 views

In Google Spreadsheets how to make a relative column reference within a query?

I'm working with a large data set surrounding about 200 countries at the moment for a school project. As such, I'm using a query formula to transfer raw data to a computed form to be graphed later. ...
0
votes
0answers
17 views

Need to split a column from data source in two columns

I need to retrieve specific data from accounting records. In the case of the this example spreadsheet I need to have the values in Column H to be splitted in columns L and M according to the position ...
0
votes
1answer
26 views

How to flip query results in Google Sheets?

I am trying to select multiple rows from one row in a source sheet based on another column in the source sheet. E.g.: Name Start Add Bob 2012-01-01 01:01:00 10:00:00 Tim ...
1
vote
1answer
45 views

Issue trying to sum different columns

I am trying to sum 3 different columns and I'm not being successful. My function is: =ARRAYFORMULA(SUMIF(MONTH(D:D), 2, A:C)) Where D is a column with dates, and A,B and C columns with currency (A-...
2
votes
0answers
102 views

Create a pivot table with a column for each assignee, containing rows of task descriptions

I have a two-column table with the first column being "task description" and second column being "assignee". This is something I'm unable to influence. Crude representation: I'd like to be able to ...
2
votes
1answer
15 views

SumIf OR COunt if

I want to count dates monthwise with person name. This is my data: Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 2/2/2020 Mandeep 2/2/...
1
vote
0answers
19 views

Importrange, editing from the sheet that its imported too

I currently use importrange to get timesheets to a master sheet. I then download this sheet every week and update to my KPI's As all the information is on the master sheet where all data is ...
0
votes
1answer
33 views

Finding duplicates of emails at domain level

Need some formula help with the following - I want to compile a list of same domain name emails. Sample sheet here https://docs.google.com/spreadsheets/d/1BAB9YyPbvDgNzbT-tx-...
0
votes
0answers
47 views

How do I sort a specific column (a - z) when I have merged cells that affect a portion of that column?

I would like to sort a specific column in Google sheets alphabetically, and have come across the following problem. When I select the column that I would like to sort, an error message stating ...
1
vote
2answers
43 views

Array formula with index match to return array of matches

I've been working at this for a bit. I have tried many times different formula combos, using VLOOKUP to using INDEX & MATCH, even with an ARRAYFORMULA. What I'm attempting to do is match a ...
1
vote
1answer
43 views

Help Sorting Data

I have a spreadsheet that I'm using to keep track of stats for players in a league. This is the link: https://docs.google.com/spreadsheets/d/193GxNzaX4-bGrNB8eT4vhqAOKhUrChRDgq-HYVU_Txk/edit#gid=...
0
votes
0answers
37 views

Using Google Sheets drop down menu to query only when an option is selected

So my little project is to make a "calculator" for a components list. Moving left to right, the drop downs narrow down what compatible parts are available by using a few query functions to narrow down ...
0
votes
1answer
26 views

How can I reference a word in a dynamic cell for parameter 2 instead of having a fixed word in single quotes?

I want whatever value I enter into this cell - Sheet 2 Column D1 - to become part of my formula. And I need to be able to duplicate the sheets and have the "sheet 2" reference update to the duplicated ...
0
votes
2answers
58 views

Make a query argument work with arrayformula in Google Sheets

Can you help please? I have worked out how to get the result I need, but how do I make them work with arrayformula? Here is my google sheet This works for me in Y2: =COUNT(QUERY('Feed from Register'!...
0
votes
1answer
16 views

Pulling data from most recent google sheet

I am currently working on a sales sheet for my team and have a question. I currently have each month separated into different sheets for ease of use with our current workflow and would like to be ...
0
votes
1answer
48 views

Populate Last Cell With Query Function in Google Sheet

Looking for a way to populate an additional column in possibly a QUERY function. The extra column will only have as many rows as the first set of data. Example being my data is Sheet1!A:A and then on ...
0
votes
0answers
19 views

How to sort by drop down option and secondary date range

I have a data validation range (drop down) list. I'm using a query to bring back info from the choice of that drop down. I would like to then filter the information further with a date range that will ...
2
votes
2answers
48 views

Extracting range from of an array

From the following single cell array: 0, 1, 1, 2, 3, 5, 8, 13, 21, 34 I'd like to extract 3,5,8,13,21 into a single cell by specifying index 4 : index 8 of the split of that cell. The Challenge is ...
1
vote
1answer
28 views

Look for value then print corresponding cell

This is my test sheet: https://docs.google.com/spreadsheets/d/11oiKLq6EWk9grdrtIKHT0NQ4naTX_tSPl7o8zAQP3Tc/edit?usp=sharing My core information is in the sheet labeled "Information." In the sheet ...

1
2 3 4 5
13