Skip to main content

Questions tagged [google-sheets-arrays]

Use for questions about using arrays in Google Sheets

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

MORE DETAILED explanation of ERROR: Array result was not expanded because it would overwrite data in B6 [duplicate]

Here are screenshots from my spreadsheet that has the issues I discussed in the previous question: IMPORTRANGE(,) works in a single cell, however, when adding the same formula to the next cell in the ...
Newton's cat's user avatar
-2 votes
1 answer
151 views

Solving ERROR: Array result was not expanded because it would overwrite data in B6 when using IMPORTRANGE

I am trying to make a Google Sheets book to keep track of my schedule. When importing data across sheets, I used IMPORTRANGE(url, range) to import data from Sheet1 to Sheet3. This worked at first; ...
Newton's cat's user avatar
1 vote
1 answer
24 views

Formula to return a value when the adjacent column's text contains any value from an array of text strings

In Google Sheets I have an array named "Tech_issues" with the following values: Array name value 1 value 2 value 3 value 4 Tech_issues crash froze glitch error I'm looking to create a ...
flipskyrain's user avatar
0 votes
2 answers
48 views

How to use a cell's array value

Simple question, but I can't figure it out. In the spreadsheet above I have a table in A1:B5. I have a filter in A7 and its value is an array. In A11, I want to textjoin all the values of the filter ...
Hugh Beyer's user avatar
1 vote
1 answer
319 views

How to QUERY - group SUMed results? [duplicate]

I'm new to query and can't figure out how to get grouped results of sum in two columns. There's data set in the picture of names and points which are in two columns and query formula to sum and group ...
Jan's user avatar
  • 11
0 votes
1 answer
32 views

Count the number of occurrences where a cell value does NOT contain a value from a pre-defined array

I want to count the number of occurrences where a cell value does NOT contain a value from a pre-defined array. Audi BMW Audi Mercedes Ford BMW Ford =COUNTIFS(A:A,NOT({"","Audi",&...
Adam's user avatar
  • 469
2 votes
3 answers
287 views

Count occurences

I would like to make a count based on fulfilling two conditions in a column across a range of columns. For the sake of easily explaining things, I have decided to make a table of names and foods, I've ...
Dots's user avatar
  • 23
0 votes
1 answer
464 views

Make an array expand to the left of the cell with the formula

Is there a way (or workaround) to make an array expand to the left of the cell with the formula? (Above the formula would also work) Here's a spreadsheet with more detail on what I'm looking for. (I'm ...
Phil Anderson's user avatar
0 votes
3 answers
627 views

Google Sheets - List values if date in corresponding column is the same as the date in Cell D1

In Sheet 1 I have a table of orders due for a delivery. Column A is a list of the order numbers, and column H is the delivery date. In Sheet 2, I want to create a list of the order numbers that have a ...
alphadavid's user avatar
1 vote
1 answer
40 views

How to use ARRAYFORMULA to remove values that contain brackets?

I am trying to write a formula that pulls all data from column A that doesn't contain brackets. Source Data Desired Result (no values containing brackets)
Colin Mills's user avatar
0 votes
1 answer
65 views

Google sheets array search

I am trying to write a formula where Cells A2:A16 are searched for numerical values and the results are output in cells C2:F2. Within the data that will populate in A2:A16, there will only be 0-4 ...
Colin Mills's user avatar
0 votes
1 answer
108 views

Google Sheets - Sum Filter of Table 1 in each row of Table 2

I have table of transactions with categories (let's say 1-5) and assigned dollar values, and another table with a list of each category. I want to sum up all the matching categories from Table 1 with ...
jpfieber's user avatar
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, ...
Anastasia's user avatar
1 vote
1 answer
194 views

Import and sum data from multiple sheets

I'm at a loss as to do what I'm wanting. Sheet Main: A B 1 TA 30 2 TB 50 3 TC 12 Sheet TA: A B 1 C .45 2 D .36 3 E .12 Sheet TB: A B 1 C .44 2 G .03 3 R .15 Sheet TC: A B 1 C .10 2 D ...
Cactus Dude's user avatar
0 votes
1 answer
419 views

Parsing the result of an array formula inline?

I am trying to use =GOOGLEFINANCE(B2, "high", DATE(2022,7,26)). According to google's docs, using historical values in this formula will result in an array result... So, it pumps values all ...
DraxDomax's user avatar
  • 121
0 votes
2 answers
69 views

How can I create an unique list of data across columns containing text "Retailer Name"?

I have certain data where I want the unique Retailer name across multiple sheets and multiple columns containing "Retailer Name" as the heading. I've shared the editable Google Sheet link, ...
Santosh J's user avatar
1 vote
1 answer
1k views

Regexextract to get a text in the middle of string

SAMPLE SHEET I have data in a column in this format: 1 P01 - Product Name here - PAID IN FULL 2 P02 - Product2 Name here - PARTIALLY PAID 20 1 P03 - Product2 Name here I've been trying to ...
Genny's user avatar
  • 365
2 votes
1 answer
792 views

Google Sheets - Group/Aggregate data

I would like a formula that groups/aggregates the data of Column A, B, C and D if column A is the same string The issue is the end of the string in column A with different sizes at the end of the ...
magelincs's user avatar
-1 votes
1 answer
68 views

[SUM IF CELL ABOVE EQUALS CELL BELOW]

[SUM IF CELL ABOVE EQUALS CELL BELOW]. * link to the file https://docs.google.com/spreadsheets/d/1GFS6J1vX88vsOCsKzrMB7Zf82m9QOHqu6oAmO4eJwH8/edit#gid=0 * in column f of the file I'd like to get the ...
Giovanni Capuano Solution's user avatar
0 votes
2 answers
80 views

How to create Google Sheet app that retrieves a mix of random words

I have a Google Sheet with a lot of columns, each of them containing words from a category. I would love to create a formula that can retrieve random words and put them together. For example: Colum 1 [...
Alorve's user avatar
  • 1
0 votes
1 answer
579 views

Automatically apply a formula and a when a new row gets added via a form API

Hi I have a wordpress form, linked to a google sheet that transfers over entries as they come in, now I have some formulas applied, but I don't want to have to add the formula to each new row that ...
Shak's user avatar
  • 3
-2 votes
1 answer
71 views

i can't find the problem to this formula

=UNIQUE(QUERY({IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP Directory!A2:U");IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP ...
criss's user avatar
  • 1
1 vote
1 answer
38 views

Need To Randomize Selections From A List But Ensure Uniqueness

I'm building a tool to randomize setup for a very large modular card game. There are several lists from which I need to select 2 to 6 non-duplicate entries, and each list is on a separate tab from the ...
Rich Pizann's user avatar
1 vote
2 answers
61 views

Array Formula omits IF / AND functions

I'm using a series of IF and IF/AND functions to assign a set value according to, sometimes one set or two set of colums. While creating a range and Array Formula to apply that to all posible new ...
Diego's user avatar
  • 11
1 vote
1 answer
1k views

How to add to each element of an array

I'm trying to take each element of an array that's generated from a SPLIT function and do some math on them, specifically, I want to add 3 to each element. Cell starts off with this: 1,2,3,4,5 SPLIT ...
midoriha_senpai's user avatar
0 votes
1 answer
150 views

How to randomize words contained in a cell separated by Alt+Enter [ char(10 ]

How to randomize words contained in a cell separated by Alt+Enter [ char(10 ] , like a new line. I found a formula but what it does is create a range of columns first. But I can't do it because there ...
DoraeP's user avatar
  • 1
1 vote
1 answer
382 views

referencing a column in a Google sheets array literal

Is there a way to refer to columns in a literal array like so: =FILTER({lengthy_arrayformula_1, lengthy_arrayformula_2}, condition referencing column 2 of the array literal in previous argument) or =...
Ehsan Amini's user avatar
1 vote
1 answer
132 views

Why my Arrayformula doesn't work

I have this google sheet : link is here : https://docs.google.com/spreadsheets/d/1_-AcgJud2DclKtarcJj39DNB84CzthUzO_n5F8mBgKY/edit?usp=sharing On the top is my matrix table. It has key on the left ...
andio's user avatar
  • 397
1 vote
1 answer
143 views

How to stack two columns in Google Sheet with column name in another column?

I have these columns in the Google Sheet. Date A Date B 1/1/2020 3 5/2/2021 328.28 1/2/2020 41 5/3/2021 322.75 1/9/2020 23 5/25/2021 342 1/12/2020 54 5/28/2021 ...
Alfi's user avatar
  • 11
0 votes
1 answer
2k views

How to have a query wrapped in an arrayformula auto-populate many rows

I have a query that queries over a set of rows. I want to run this query, itself, for each row in another set of rows. The one value that changes in the query for each row is something in the where ...
Chris Morris's user avatar
1 vote
1 answer
837 views

Split text in Google Sheets with a delimiter which contains spaces

I have a column (A) in Google Sheets where each row contains the following string: "TEXT on MORE_TEXT" or, for more specific example "occurred on December 17, 2020". The "...
YBB's user avatar
  • 77
0 votes
1 answer
63 views

Sum column value if any of several criteria is met in the adjacent column

I have a sheet in which I have 2441 words (hashtags) on their own rows in the first column and in the next (adjacent) column, a number related to them (occurrences of the hashtag in our data). I want ...
Yolko's user avatar
  • 3
1 vote
3 answers
146 views

Count instances of character in a column

Is there a way to count instances of characters down a column, without creating further columns that do row-by-row counts of each character? Below is an outline of what I'm trying to do. Note that no ...
Alfo's user avatar
  • 117
2 votes
1 answer
183 views

How to use a query to find a row of same name but last entry, then copy a value next to it

I am trying to use a query to copy values from one column to another. Both source and destination columns have a name on their left columns, and the name must match. The source names could be ...
sofname's user avatar
  • 137
1 vote
1 answer
66 views

Problem with results of filter function from tabs that have no data yet

I made a master sheet that combines all data from 12 tabs within the workbook sheet. These tabs use query functions and importrange from other sheets. Some of sheets have no data yet like May 2021 ...
Hassan Almaateeq's user avatar
0 votes
1 answer
1k views

Sheets ArrayFormula with SUMIF

In a 'Records' sheet I have columns of timestamp, a duration, and a category (i.e. 13 minutes of 'B' was done at 18/01/2021 00:00). In the 'Analysis' sheet I have a field where the Type can be ...
user3623437's user avatar
1 vote
1 answer
6k views

Using query function with ' where Col1 or Col2 is not null'?

=query({TOIM;IMTO},"SELECT * where Col1 is not null ORDER by Col11",0) Where TOIM,IMTO are named ranges. I want to rewrite the formula with two criteria (OR). In other words like this: =...
Hassan Almaateeq's user avatar
0 votes
1 answer
143 views

How can gaps be quantified within a combined set of variable arrays?

I'm using Google Sheets to build a crop planning tool for farmers (kinda like a gantt chart). I need help quantifying the number of days within a given timeframe that no crops are planted in a bed. In ...
Todd Jones's user avatar
0 votes
1 answer
59 views

Search through multiple tabs and pull results

Test Sheet for my use case: https://docs.google.com/spreadsheets/d/1_gcyygG8IhVc-4mXf8n09IFG5ZIYuK_UxWxPLQ5av-k/edit?usp=sharing I've had some input from another user on the site helping me search ...
Willmk's user avatar
  • 1
0 votes
1 answer
38 views

How can I filter several pages of data and ignore those that are blank in the process and return the rest?

Each day I have several tabs that update different football leagues data from websites using importhtml. I filter each individual tab onto one separate tab. When one tab is empty and contains no data, ...
MR H's user avatar
  • 11
0 votes
3 answers
1k views

=IMPORTHTML forces content to date format

I am a beginner with code and formulas here, so I was wondering if anyone could do exactly what was done on this question: Trying to use Google Sheets importHTML() to import a table. It forces content ...
John Tokish's user avatar
1 vote
1 answer
2k views

How do you perform a bulk find & replace using a list of search phrases and their replacements?

This is not my wheelhouse and I'm realizing I'm in a bit over my head. Any help would be greatly appreciated. What I'm trying to accomplish https://docs.google.com/spreadsheets/d/...
David S's user avatar
  • 13
0 votes
3 answers
1k views

Use array as function parameters

Given a function that returns an array, is it possible to "spread" the return values as the arguments to another function? For example, I'd like to pass the result of SPLIT("example.com,...
Joe Taylor's user avatar
1 vote
1 answer
122 views

Insert query into a specific cell based on a dynamic reference

I have 4 queries. The amount of results from each will vary week-by-week. I have a formula to calculate the number of rows of each query. I want the queries set out in the Google Sheet so that the ...
esteban8888's user avatar
-1 votes
1 answer
125 views

How do I convert rows with 45 columns into 15 separate rows, each with 3 columns?

I am working with a google sheet that gets data from an email parser. Each time an email comes in, a single row is created, and fills in these columns: B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, ...
Adam Park's user avatar
3 votes
2 answers
864 views

How to convert repeating rows into columns in Google Sheets

I have data in Google Sheets that looks like A B 1 Name: John Smith 2 Email: [email protected] 3 Name: Dan Jones 4 Email: [email protected] ... many rows which I'd like to get into this format:...
Daniel May's user avatar
1 vote
1 answer
60 views

Search a Google Spreadsheet and return results that are 'clickable' that takes you to the sheet and row

I have a spreadsheet with many sheets. (Data1 Data2 and Data3). One sheet is named Search. This search sheet will search all 3 sheets (Data1,Data2,Data3) Results will return on the sheet named Search. ...
Donavon's user avatar
  • 31
1 vote
1 answer
1k views

How to replace or substitute one column of data with their corresponding value from a list

I'm trying to replace a column of data (Prodct SKU) on one sheet (a) with the corresponding value taken from 2 columns on sheet (b) (Product SKU & Product ID). I've attached the spreadsheet to ...
Stephen's user avatar
  • 13
1 vote
1 answer
33 views

I want to 1) get unique names AND 2) strip out vacancy AND 3) alpha sort AND 4) append Last name, first name

I want to 1) get unique names AND 2) strip out vacancy AND 3) alpha sort AND 4) append Last name, first name. The names are in Sheet1 and the results are in Sheet2. I'm using =SORT(UNIQUE(Sheet1!A3:...
M. Schultz's user avatar
1 vote
1 answer
213 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 ...
simplycoding's user avatar