Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
192
questions with no upvoted or accepted answers
3
votes
1
answer
6k
views
Repeat formula n number of times in Google Sheets
Apologies, I'm very new to Google Sheets and spreadsheets in general. I'm trying to fill some cells with repeating data, and then analyze it. The data is pulled from another large spreadsheet and has ...
Phinet
3
votes
2
answers
6k
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 ...
Akira
3
votes
3
answers
673
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,", ")...
- 31
3
votes
1
answer
6k
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 ...
- 364
2
votes
0
answers
35
views
How to link 2 sheets, so when a cell on sheet1 is modified, sheet2 got updated automatically and viceversa
I am trying to have the same updated values in two differents sheets of google spreadsheet.
So when I edit sheet1, the the values in sheet2 got updated. Also the opposite: when I edit something in ...
2
votes
1
answer
25
views
Compare imported range with existing range, and filling corresponded data to the fields
I am not sure how to import data to corresponding cells.
query(IMPORTDATA("https://www.iftach.org/taxmatrix/charts/4Q2020.csv"), "Select Col4 where Col1 = '"&A4:A&"'&...
- 21
2
votes
2
answers
3k
views
Select Unique on 1 Column in Google Sheets - Distinct Doesn't
I'm trying to set up a kind of a bid thing, where the top 5 respondents receive a prize. I was hoping to use SQL to get the top 5, but I can't remove duplicate bidders and only take their top bid. I ...
- 21
2
votes
2
answers
219
views
Spreadsheet formula to calculate most common items in ordered items lists
I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items.
Like is you have an Oscar ceremony and you get votes from ...
- 163
2
votes
2
answers
359
views
Aggregate data to show text in one cell
I need to put together multiple entries into one where the result is text and has to be in one cell.
From this data in A:B
This is a sample; the actual list in Column A may be extremely long.
A
B
1
...
- 21
2
votes
0
answers
43
views
Google Sheet Column Tick box validation
Complete newbie here, sorry if this sounds silly.
I have 3 columns and 60 rows of tick boxes, I have the tick boxes adding up at the bottom of the sheet.
Question is I do not want a user to tick more ...
- 21
2
votes
1
answer
213
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 ...
- 21
2
votes
0
answers
213
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
0
answers
54
views
How to use COUNTIF and UNIQUE
I have a Google Sheet with two columns. One containing a list with order numbers - the order numbers can be listed several times/duplicates. The other column consists of the a free text with the name ...
- 21
2
votes
1
answer
3k
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 ...
- 31
2
votes
2
answers
501
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 ...
- 85
2
votes
2
answers
306
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 ...
2
votes
3
answers
3k
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 ...
2
votes
2
answers
289
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 | | ...
- 53
2
votes
1
answer
1k
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 ...
- 21
2
votes
2
answers
3k
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 ...
- 19
1
vote
2
answers
17
views
Filter Rows while also doing SUM columns based in column header values as part of Select statement in Query function in Google spreadsheets
I am trying to set up a pivot table using Query function in Google Spreadsheets, so that this can be used for various different reports.
Below is the sample data i have.
As per the guidance from the ...
- 29
1
vote
1
answer
28
views
Keep matching columns aligned when stacking ranges from different tabs that are missing certain columns
Since a while back, I have been trying to find an alternative to Power Query in Google Sheets to combine several sheets of data into one by matching on column letter.
For example, in tabA I have the ...
- 11
1
vote
0
answers
60
views
How do I build a formula to automatically select from dropdown categories column based on strings/text from another column?
I'm hoping to build a formula that automatically selects a category based on keywords/phrases from an imported bank statement. This is a very similar question that Ryan Voigt has asked back in Feb ...
- 11
1
vote
0
answers
562
views
Google Sheets Query doesn't return all data
this is my first time posting and I am a beginner with Google Sheets. I am trying to use QUERY to return 3 columns of data with a condition that the number column is greater than 0. Here is what I ...
1
vote
1
answer
187
views
Multiple large GOOGLEFINANCE formulas make spreadsheet unresponsive
Problem
In Google Sheets I have huge GOOGLEFINANCE formulas that return accurate results however the sheet is often unresponsive, and can take a few hours to recalculate when reloaded.
Is there a way ...
- 11
1
vote
1
answer
36
views
How to sum data on one row from multiple selected Google Sheets
I am tracking paid advertising key performing metrics on both Google & Facebook ads platforms on two separate Google Sheets, however, I'd like to combine both datas collected into one master sheet....
- 11
1
vote
2
answers
89
views
Add cell value in query
When doing a multi-sheet query, I would like to bring in the value of a cell (A2) from each source sheet to a new column in the results.
The query:
=QUERY({CCL!A4:M; HOOD!A4:M; AAL!A4:M; BAC!A4:M;
...
1
vote
2
answers
784
views
How can I QUERY a range of data for rows where a column contains any text string from a group of valid strings?
I want to query data from another sheet by checking if the data_range's column C (invoice#) matches any one of multiple lookup values from a range in the current sheet's column B.
The lookup values ...
- 11
1
vote
1
answer
71
views
How to filter data from various sheets based on dropdown selection
I am setting up a sheet to track renewable services for my company. There will be various tabs, for example, Domains, Hosting, Google Workspace and Phone Numbers.
I would like to be able to filter all ...
- 11
1
vote
1
answer
199
views
Dynamic & extendible category names
I'm having trouble on something with Google Sheets:
I need category names to be dynamic & extendible between 15-200 values, so it's not quite worth an Apps Script, would be super cool to put the ...
1
vote
1
answer
1k
views
Stack two sets of data into one in google sheets - different columns
I am looking for a solution that helps me stack two sets of data (from diff. columns, different order) into one data sets
EXAMPLE:
columns names + order tables1:
first name, last name, city, age, ...
- 11
1
vote
2
answers
650
views
Query formula to add a column in Month/Year format from existing date values
I have a data set where 1 of the columns contains dates in "YYYY-MM-DD" format. I'd like to use a single query formula to select some columns from that data set, including the date column. ...
- 77
1
vote
1
answer
2k
views
google sheets query function where A matches string in a cell
I am trying to use a cell value to impact a query. The issue seems to be that the cell value is a string which I am unable to use as a dynamic object in the query.
I have based my findings and ...
1
vote
0
answers
8
views
Auto Fill but changing only certain cells
I am trying to make a QR code attendance system and try to organize it so I can see what student comes on what day. I am trying to auto fill everything so I don't have to type everything for every ...
- 11
1
vote
0
answers
14
views
Query Filtering Corresponding Columns
Formula work in progress:
={query(C3:G82,"select * where D like '1' order by C desc, D asc LIMIT 1",0);query(C3:G82,"select * where D like '2' order by E desc LIMIT 1",0);query(C3:...
1
vote
1
answer
113
views
How to retrieve today and tomorrow's values in a specific tab of a dynamic calendar
Employees are selected in their corresponding shift on each month tab in a dynamic calendar
See 092022 tab in my example:
I want Pemanence tab will be able to retrieve automatically the values in the ...
- 53
1
vote
1
answer
40
views
Query meeting 2 conditions
I am trying to create a query that pulls information based on two drop downs. The first drop down searches "staff" or "student" in column C of my form response tab, and the second ...
- 11
1
vote
1
answer
504
views
Split, Flatten, and Repeat Cells in Certain Columns Within an Array Formula for Google Sheets
I am looking for a formula in google sheets to to break down an existing data set at once in a format that's a little more versatile. Here is the link to the sample sheet which is also the source of ...
- 11
1
vote
0
answers
41
views
How do I filter in IMPORTRANGE using SEQUENCE and ARRAYFORMULA?
I am having issues with including a FILTER option in my current working formula listed below:
=QUERY (IMPORTRANGE("1n7qMGR9p8xD-8tyPIhfrcyBEsW8FAUMJMHmgxM7xIVI","Consultant Directory!A6:...
- 11
1
vote
0
answers
30
views
Add more conditions to the formula
=query({IMPORTRANGE("1c6ihzJjzD_Nhbz5rB1s0485PS98V-Q4bfVAiIon5vSA","MC2!A11:S5000")},
"select Col19, Col1, Col2, Col3, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, ...
- 11
1
vote
0
answers
519
views
Query a sheet based on checkbox and order according to check order
In Google Sheets, I have two sheets that work together and I want to achieve the following:
I have a sheet1 with information in rows and a checkbox at the beginning
When the checkbox is checked, the ...
- 111
1
vote
0
answers
46
views
Problem with adding query function with another function
newbie here!
I was testing and practicing the query function, when I came across this function on a website
={QUERY($A$11:$B$61,"select A, sum(B) group by A order by sum(B) desc label sum(B) '...
- 11
1
vote
1
answer
2k
views
Google Sheets App Script Sorting
I'm trying to have an auto sort function only impact one sheet in a workbook rather than all of them. I've done this in other projects and def messed this up along the way - any help is appreciated!!
...
1
vote
2
answers
52
views
Is there a way to remove entries based on condition in =filter?
I probably did not phrase my question accurately. Is there a way to remove the result 'a' because it fulfils the '<>1' condition? I am pretty sure a mixture of query and arrayformula is needed ...
- 11
1
vote
1
answer
111
views
Multiple Gsheets data querys
Hey all so I currently have 3 Gsheets. 2 are sql imports with the below tables. The goal is to get the information based off a Type Id 28353 into my Master sheet. The plan is to get all values from ...
- 11
1
vote
1
answer
360
views
Google sheets import columns based on date value
Google sheets for simplicity's sake say I have two tabs, one with master data and one that is a search function to display a certain part of the master data. The issue is I only want import certain ...
- 11
1
vote
1
answer
78
views
How do i Import a rows from one Google Sheet to another with filter?
I have a Google Form, the answers go to Google Sheets.
I want to import answers from sheets (Аnswer) into new sheets but with a filter applied.
For example: from sheets (answer) into a new table, I ...
1
vote
1
answer
1k
views
Using query as a text join
A few days ago I reach the limit of 50000 characters in one cell, I found a solution with "query" but I need a delimiter inside the formula that I haven't been able to find:
This is the ...
1
vote
1
answer
848
views
How to run a google sheets query against a dynamic range
Background
I have a spreadsheet that displays data internet consumption by users that looks like this
userid feb1 feb2 feb3 . feb29
u1 100 34 23 . 4
u2 ...
- 1,199
1
vote
0
answers
70
views
How can I use a dropdown menu in Google Sheets filter out data and populate cells for a an expense tracker?
I have created a personal expense tracker (budget) that uses google form responses and categorizes them by fortnights and expenditure categories. This has been successful on a sheet called 'Yearly ...
- 11