Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
748 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
1answer
981 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
2answers
1k views

How to concatenate columns for multiple rows simultaneously in Google Sheets?

I have the following 3 rows of data in cells A1:A3 in Google Sheets: Doe, Jane Doe, Joe John Doe I want the following in B1:B3: Jane Doe Joe Doe John Doe Essentially, I'm creating a new column ...
1
vote
1answer
296 views

Google Sheets query column=column

I am not familiar with google sheets, but I have been sent an assignment and I don't want to take the time to convert the whole thing over to an actual database. Is a way to take one column of values (...
1
vote
3answers
62 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. ...
2
votes
2answers
4k views

Google Sheets, how to sort query results manually (Not in query)

My google sheet is importing data from another tab just for sorting purpose, however, when the sort is done manually (through Google Sheet menus), results are unpredictable. The sheet is populated ...
0
votes
1answer
632 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 ...
0
votes
2answers
100 views

Scanning a column and partitioning information to other sheets in Google Sheets

I am a membership manager in an international organization and I was hoping to get some help optimizing our members' contact list. There are three kinds of members, NCs, LCs, and IMs. I have a ...
1
vote
1answer
56 views

Convert cell containing days of the week in first-letter format to day names in Google Sheets

I created a schedule of events in Google Sheets. I have a cell that contains MWF, indicating that the event is every Monday, Wednesday, and Friday. I have a different cell that reports the word ...
2
votes
1answer
1k views

How do I list Nth matches in Google Spreadsheet?

On MS XLS I can do some aggregate or leech off a structured reference, but I really don't know how to find the Nth result based on word match on Google Sheets. I have huge columns - name, role. I ...
0
votes
1answer
105 views

Split rows dynamically based on row length (COUNTA) and transpose to 2D array

I have data in this format: And I need it to be reformatted into this: Basically, each row in the initial format is composed of 4 concatenated sections of equal lengths: Part(s), Product(s), Quant(s)...
1
vote
1answer
699 views

Transform a Google Spreadsheet table? Columns by rows [duplicate]

I need to transform this table into this one, with de Services columns as rows:
1
vote
1answer
18 views

Search for multiple cities of unload

This is my table: --------------------------------- | City of load | City of unload | --------------------------------- | Enzesfeld | Lublin | --------------------------------- | ...
2
votes
1answer
2k views

Query Function with Unique References

I am currently using the QUERY function to move relative information to another sheet, which is then used for forecasting. Now what is currently happening is the same business names are showing up in ...
2
votes
1answer
544 views

Copying values between Google Sheets

I have 2 Google Sheets. I have data on Google Sheet A and I would like it to automatically transfer some values to Google Sheet b. For example, I have some email addresses and some data associated ...
1
vote
1answer
44 views

Check value even if it doesn't match validation

In my Google spreadsheet, I have a column G of dates that interviews are/have been scheduled on. There is a validation set on it to validate for dates with a warning. Every once in a while, when ...
1
vote
1answer
23 views

QUERY for like-minded rows

Scenario Consider the following query: =QUERY(A15:E861, "select B, sum(D) group by B") This works for rows with col B's that have the SAME description eg. 1 | abc | n | 10 2 | abc | n | 10 3 | cba ...
1
vote
2answers
40 views

Confirm that values in a column are alternating

I have a column, call it A, of around 1500 cells. Each cell contains either "a" or "b". For the most part, the As and Bs are alternating, but sometimes there are repeating values. An example of A1:...
3
votes
1answer
9k views

Data Validation with an IF formula

I've got a list of items that I want to include in a Drop Down. Using data validation, all I need to do is add the range and this works. However, the list has various types of data and I want to ...
1
vote
1answer
26 views

Lookup another sheet get whole row in sheet and no duplicates

I have looked through the questions but somehow I just can't find what I am looking for. What am I looking for? I have a document with 2 sheets. Let's say, sheet 1 is the backlog, sheet 2 is the ...
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 ...
0
votes
0answers
129 views

In Google Sheets, SQL Query: How to make multiple rows out of an transposed split of a column?

I have a query that returns this: =QUERY(BOMs!1:300;"SELECT BU,A,B,E,G,H,K,L,M,BC ";1) L65P6 PR BOT 33-KLN121-06X BOBINA CHIP 120 OHM L500, L503, L502, L501 2780 4 UN 0,10154 The column ...
0
votes
1answer
297 views

Google sheet query to combine values

From various examples I've hacked together a pretty neat aggregation function which gives me an overview on which activity (strings in G) I've spent non-billable work hours (numbers in E) or billable (...
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
1answer
236 views

How add column BY to a query?

How do I include the column BY in a query? I first tried =unique(query(data_table,"SELECT D, BY")) Then, I tried to surround it with single quotes =unique(query(data_table,"SELECT D, 'BY'"))
0
votes
1answer
30 views

Can we use Column Range for Comparison in Google Spreadsheets Query?

Suppose I have a table with 3 columns in a Google Spreadsheets sheet. Col1 is a name and Col2 and Col3 are numbers. Can we use a column range for number comparison such as in the example below? (what ...
0
votes
2answers
349 views

Google Sheets: Split row value and count each part

My Problem I have a survey result with a checkbox question in which more than one answer could be checked. The results are given in a single column, separated by a | sign. I would like to run a ...
2
votes
1answer
3k views

HAVING clause in Google Sheets QUERY

My Problem I have built a count query that returns the most common elements from column H in the final sheet: =QUERY(final!H2:H,"SELECT H, COUNT(H) GROUP BY H ORDER BY COUNT(H) DESC") I would like ...
2
votes
1answer
318 views

Find column headers then do multi-criteria lookup

I'm trying to create a report where I need to find each column by header, then do a multi-criteria lookup. (I need to find the proper column because the data file is a csv export from another ...
-1
votes
1answer
320 views

How to use a value in Sheet2 in the WHERE clause of QUERY in Sheet2 whose dataset is in Sheet1

I have a Sheet1 that I want to use as my dataset for a Google Spreadsheet query that looks as follows: The query is in Sheet 2 and I want to pull across a list of manufacturers who have a Type that ...
1
vote
3answers
818 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 ...
0
votes
1answer
2k views

How to keep cells “paired” with query results

I am using an importrange query from other sheet to filter data: https://docs.google.com/spreadsheets/d/1FbeoKhiJ3jqYXqfIgAK_tHXPZ_Z1Xrm33ulu9gomXuE/edit?usp=sharing The "colors" sheet has data ...
2
votes
2answers
2k views

Google Sheets Query - True/False/Other

I'm trying to get a query based on a tri-state. The possible values are TRUE, FALSE, 'Ignore' or blank. I want this to select all names (B) and emails (C) where a flag column is set to False or ...
0
votes
1answer
390 views

Google sheet - get crypto currency price from Cryptobridge Exchange API

How can I retrieve only the price from this API in a Google spreadsheet? This is the API ticker - https://api.crypto-bridge.org/api/v1/ticker For example, if I use this function =INDEX(IMPORTDATA(...
1
vote
2answers
495 views

How to select column twice in query

I have a query that is really long (about 50 columns), I don't want to split into 2 queries. But I want a column to show up twice. Is there a way to do this? below is a simple example that doesn't ...
0
votes
1answer
3k views

How to Aggregate and Pivot Simultaneously With Google Sheets Query

Hey I am trying to transform a table from this: Into this: Basically for each "Type" I want the totals for the different values for "Exists" which right now is just TRUE and FALSE. But I can only ...
1
vote
1answer
797 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 | | ...
0
votes
0answers
847 views

Merging two data sets in order to add default values for missing data

I'm trying to merge two datasets in order to insert default rows for missing data. The use case is that I have a list of dates and attendance numbers for training sessions on those dates, but if I ...
3
votes
2answers
3k views

Google Sheets Query(Importrange) with named range?

I am looking for a way to query an imported range using named range. Something similar to this, however I cannot makeit to work Sheet ID#A has a column J whose named range is "Signed" that may ...
2
votes
1answer
36 views

How to return a name alongside certain values in Google Sheets

Let's say I have a sheet of student data. column A: grade level column B: student name [not relevant: columns C - G: absences, parents' phone number, credits completed, etc] column H: GPA I want to ...
0
votes
1answer
2k views

Using Query and Importrange to pull Google Form data from one worksheet to another

This is the query code I'm using to get form responses filtered to pull only a specific user ID from a google form in one Google Sheets workbook into another workbook: =QUERY(IMPORTRANGE("KEYID#","...
3
votes
1answer
10k views

Query multiple data input ranges into 1 column output

Not 100% sure how to explain this, feel free to edit this with more intelligent language. I need to pull data from 2 tables in 1 sheet. Meaning, I need multiple query functions. Those query ...
0
votes
2answers
4k views

Google Sheets queries: using WHERE IN

My problem I have a table of ids and terms in Google Sheets. I would like to concatenate a few terms by their ids. For example, (2,3) should yield Talla de zapato de mujer, Women’s shoe size. I want ...
0
votes
1answer
1k views

Compare dates in query from IMPORTRANGE and cell

for a few days I'm stuck at comparing two dates. In a big table with dates as columns and products as rows, we want to display the amount of sales per day per product. What the following query does: ...
2
votes
1answer
2k views

Transpose Ranges repeated by row

I would like to convert several range of columns into rows, and keeping the association to the original col/row source Sample source sheet --------------------------------------------------- | Data ...
2
votes
1answer
848 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......
2
votes
1answer
397 views

Refer to the cell directly to the left of the cell with the maximal value in a range

I've got a spreadsheet with about 5,000 rows and roughly 50 columns. The columns are alternating between terms and numeric values associated with the terms (example: baseball / 1000 / football / 500 / ...
0
votes
1answer
396 views

Vlookup across multiple columns

This question is related to the same project in this question, but I'm now at a later step. I have a Google Sheet with a variety of German translations of a large set of English words. Using a ...