Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
766
questions
0
votes
1
answer
30
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 ...
1
vote
1
answer
50
views
Arrayformula to get closest non-numeric value above
I have Column A with mixed data types (numbers and strings) and in Column B I want for each numbers in Column A to display the string that is the closest above. Example:
Title|
1|Title
2|Title
Next ...
- 35
1
vote
1
answer
171
views
How to copy a cell from Google Sheets without getting double quotes (" becoming ""), BUT keeping the \n (char(10)) line breaks
I have a couple of Google Spreadsheets I use to generate config files for things. The config file data ends up in a single cell, which is copied and pasted.
Problem is Google Sheets has this odd ...
- 131
0
votes
1
answer
111
views
Google sheets reference Text in Cell in Array Formula & Countif formula
So, I have a Array Formula and Countif formula in place because I need to know the total of entries in a combination of ranges. I need to know the sum of the number of cells that have "X" ...
0
votes
0
answers
54
views
Array Formula Problem - How to Fill In Blank Rows with the Same Value before a Breakpoint which advances to the next letter
I have a Google Sheet which is grouped into segments of a TV show. So there's an A segment and then a B segment and so on ... I have allocated two columns to provide two different timing functions (...
- 3
0
votes
0
answers
10
views
Google Sheets - Repeat row in coordination with other row [duplicate]
So I've been looking to do this for a long time hours now, maybe I haven't used the right search terms, but I can't find any working formulas on any discussions! Here's what I want to do:
So here's my ...
- 101
1
vote
0
answers
74
views
Use ArrayFormula to automatically update the column with a formula already containing an ArrayFormula
I am using the below formula to concatenate some data into one string:
=TEXTJOIN("",FALSE,ARRAYFORMULA(IF((Data!$F$3:$F=$C2)*(Data!$G$3:$G=$D2),Data!$L$3:$L,"")))
I however have ...
- 35
0
votes
1
answer
350
views
Query Divide by Sum Column getting Error in Google Sheets
Looking to work around with error message "Add_col_to_group_by_or_agg:Col1" from formula below:
Query(Query({data range},"Select etc."), "Select (Col1*Col3)/Sum(Col1)",0)
...
0
votes
1
answer
86
views
Google Sheets Query - Divide by Sum(Col), Error
I'm doing a simple query but keep getting the error "Add_col_to_group_by_or_agg:Col1".
The formula is
=QUERY({L42:N63}, "Select (Col1*Col3)/Sum(Col1)",0)
What I'm looking for is ...
1
vote
1
answer
27
views
Sorting consecutive dates into groups
I have a list of dates. The list has chronological gaps in it, but I'm looking to bring it all into a single cell of date ranges separated by commas, organized by consecutive dates.
Example of the ...
- 13
0
votes
1
answer
37
views
How to make Row() formula dynamic based on a cell content?
I am creating a multiple choice (see column A) and I want it to be randomized (see column C). I saw a formula in the internet, modified it, and it really does randomized the contents of each cell in ...
- 3
1
vote
2
answers
58
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 ...
- 11
-1
votes
1
answer
74
views
Inventory Management where main list highlights items checked out/ in
I am working on an inventory management list where barcodes are scanned out and scanned back in. My sheet is attached to forms. I have set the item sku# on the main inventory list to highlight red ...
0
votes
1
answer
261
views
Google Sheets - Return a text value based on another cells text value
I have a set group of "program names" in a column, and want to have another column automatically write the matching "acrononym" for its sister value.
For example: Master in Finance ...
0
votes
1
answer
125
views
Create A Result Limit for Array Column. Spill Into Next Column. (Google Sheets)
I'm looking for a formula that lets me take a dynamic column range and tell it to spill into the next column if it exceeds a certain number of rows.
The most rows I want in one column is 10,000. The ...
- 21
0
votes
1
answer
53
views
Stacking A Range of Columns Into One (W/O 50,000 Limit)
The Problem:
I have somewhere between 20 and 30 columns of varying lengths (dynamic and can change). I would love the ability to be able to stack them in Google Sheets.
I have found some solutions ...
- 21
1
vote
1
answer
45
views
Google Sheets - How to use results from INDEX formula in a new QUERY function
In Google Sheets I have an =INDEX function which is producing a number of references for data in another worksheet.
I need to use those references in a QUERY function.
I've not found a good way and ...
0
votes
1
answer
52
views
Google sheets conditional format to highlight a column containing items from another list
I have rows of data, Col. A will be the order number and there will be duplicates of the order number (this is for each individual item purchase and MUST NOT be removed to give unique numbers). Then I ...
- 191
0
votes
1
answer
69
views
Running total in Google Sheets leaving blank cells if criteria not met
I would like to keep a running total in column A if certain text is present in column B. If that text isn't present, I would like column A to be blank, but pick up counting where it left off on the ...
- 17
0
votes
1
answer
41
views
Copy/duplicate entire rows based on number in another column
I want to be able to copy/duplicate several columns of data based on their qty in another column.
I have my data in col. A- C & the qty in D. Basically it is "A-C" times D.
And on the ...
- 191
0
votes
2
answers
287
views
How to solve error The resulting array was too large in MMUlLT formula with ~9700 rows
I need to calculate concatenation of the value and how many times it was used in rows as below:
I tried to use this formula by closed-end references
=ARRAYFORMULA(IF(LEN(L3:L9700),M3:M9700&" ...
- 3
0
votes
1
answer
231
views
How to solve Error message in Google Sheet array formula: The resulting array was too large
I tried to use this formula below on my Google Sheet for around 10,000 row data:
=ARRAYFORMULA(IF(LEN(L3:L),M3:M&" - "&MMULT((M3:M=TRANSPOSE(M3:M))*(ROW(M3:M)>=TRANSPOSE(ROW(M3:M))...
- 3
0
votes
1
answer
81
views
Sum the current row plus the next 6 (7 total)
I have a large spreadsheet and I want to sum the current row + the next 6 rows in a computed column (in this case, column O). So if I'm on row 21, for example, I want to sum column G from G21 to G27. ...
- 3
0
votes
0
answers
275
views
How can I use ARRAYFORMULA with RANK?
I want to use ARRAYFORMULA with RANK in google spreadsheet but I have no idea about it.
Here's example
A
B
C
D
1
X
Y
Z
Rank of X
2
4
6
1
2
3
1
3
2
3
4
3
2
1
1
In this case, I can write ...
0
votes
1
answer
41
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 ...
- 3
0
votes
1
answer
34
views
Create a list of names of those who answered a question correctly
I have a finished Google Form connected to a Google Spreadsheet - I want to list the names of everyone who answered a specific question correctly. I've spent a while trying to figure out how to do ...
- 1
1
vote
1
answer
70
views
Apply Concatenate formula to entire column
How do I apply the formula as described here..
https://webapps.stackexchange.com/a/76395/279173
which is this part:
=CONCATENATE(""&LEFT(A1,10)&" ...")
to an entire ...
- 13
0
votes
1
answer
82
views
Array formula works in the sheet but invalid in conditional formatting
I have a list of time intervals along with their convenience ratings. I wish to highlight intervals rated anything other than impossible which conflict with those rated impossible.
I use start1 and ...
- 11
1
vote
1
answer
141
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
=...
- 11
2
votes
0
answers
48
views
If there anyway i can check if the cell contains the result of a arrayformula()?
For example i have this in A1 :
=arrayformula(B1:B100)
If in A10 i accidentally enter a value (which will overlap with arrayformula result) , it will produce error in the A1 because of existing value....
- 225
0
votes
1
answer
104
views
Grab the right most value using arrayformula
I have this sheet :
https://docs.google.com/spreadsheets/d/163jhtdKcC6Pu34jX9brolZDb5bm-PWqG7i-yAtNueoo/edit?usp=sharing
Trying to get the right most value per row
Basic formula :
=FILTER(B2:2,COLUMN(...
- 225
0
votes
1
answer
111
views
find counta() per row using arrayformula()
I have this table :
https://docs.google.com/spreadsheets/d/1ICEL33X3ADz7XEH2Cmi1p6mrYT2RqPwbtBjCHWoNkoA/edit?usp=sharing
Basically i want to do counta() per row but using arrayformula(). The basic ...
- 225
0
votes
1
answer
585
views
vlookup with multiple output and combining filter() with arrayformula()
I have 2 sheets : shot and deadline.
https://docs.google.com/spreadsheets/d/1drNUQVtAxd0HU4QCVSKJSsKHoanE735n8HsjkYpYiGs/edit?usp=sharing
This is my shot sheet :
and this is the deadline sheet :
So ...
- 225
0
votes
1
answer
71
views
Can we do arrayformula for repeating horizontal join?
I have this table :
where i want to join all data in each row start from column D to last column. I want the output just like in that C2:C5
I use this formula in C2 :
=JOIN(",",filter(D2:2, ...
- 225
0
votes
1
answer
47
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 ...
- 225
2
votes
1
answer
107
views
How can I repeat a value into a range using a formula?
I want to do something like this:
The value can be a character or some words.
If the value is number (for example = 0) then I can do:
sequence(11,6,0,0)
But I can't use a character or words.
- 225
1
vote
1
answer
462
views
Google-Sheets : ARRAYFORMULA() for multiple columns
I have this table :
https://docs.google.com/spreadsheets/d/100ij18btv5VTmnwwdaFJ8U3PEzkS8Z-duEn2PKgA6sc/edit?usp=sharing
There are 3 Sheets inside it.
The idea is to get how many ingredients used each ...
- 225
0
votes
1
answer
236
views
How to combine formulatext() and arrayformula()?
I want to use formulatext() with arrayformula() :
=arrayformula(formulatext(A1:A10))
But The arrayformula doesn't work. It should output 10 rows but it only returns a single row output.
Is there ...
- 225
0
votes
2
answers
40
views
Print True or False in calculated column using ARRAYFORMULA
I need to output either TRUE or FALSE in column E, based on the following criteria:
If A2 = "stackexchange" then (always, regardless of other data) print TRUE in E2 (otherwise print FALSE)
...
- 223
0
votes
2
answers
3k
views
How to use max() with arrayformula()
This is my sheet :
in column E, i want to find the max among col A,B,C using this simple formula in E1 :
=max(A1:C1)
But rather than pasting the formula to E2 .. E10, i want to see if possible to ...
- 225
0
votes
1
answer
40
views
Google Sheets, use arrayformula to get average score for each classroom from a list of all students in all classrooms?
Whilst not actually referring to students and test scores, the following hypothetical is the easiest was for me to explain the question.
Sheet A contains a list of all classrooms, sheet B contains a ...
- 23
1
vote
1
answer
221
views
Apply custom formula across all columns using Array Formula
I have trouble applying my custom formula across all columns using Array Formula. My custom formula is named getCountry where it could get the country based on an address. The address components e.g. ...
- 11
0
votes
1
answer
183
views
Using Cell Value as a Worksheet Name in Formula
I wanted to use a cell's content as a sheet name, i.e.
A1
A2
A3
A4
South
North
East
West
and use the cell values (South, North, East, West) to reference cells from sheets called South, North, East, ...
- 3
0
votes
1
answer
46
views
Combine data from multiple sheets to create a stacked line chart
I have multiple sheets with three columns each: Date, Invested Amt, and Return Amt. I am trying to merge the values and get a Result dataset to create a single stacked line chart of Invested vs Return ...
- 103
0
votes
1
answer
15
views
Google Sheets pulling more data than the criteria specified
I have a tab in my Google Sheet for my raw data. I have another tab which has the report form which includes to criteria fields which I want to be able use to pull the appropriate data from the raw ...
- 1
0
votes
1
answer
19
views
Trying to combine two (2) statements together
I am trying to pull data based off of selections made in a dropdown list, and would like to combine two (2) statements/formulas into one. Separately, they both work well and achieve what I want them ...
0
votes
2
answers
186
views
Can't format hardkey date data as a date in Google sheets?
I've got some date data in a Google drive sheet which is imported from a CSV.
The date data is formatted as either dd/mm/yy or dd.mm.yy as hard key data (eg. not formatted as a date)
I got to format &...
- 6,253
1
vote
1
answer
105
views
Google Sheets, using VLOOKUP and ARRAYFORMULA to search multi-line cells
I'm trying to use VLOOKUP to find the batch number of a product based on its serial number. The problem is that the serial numbers for each batch are stored in one cell per batch, separated by ...
- 23
0
votes
2
answers
97
views
How to make variables dynamic in ARRAYFORMULA/QUERY Combination
I'm trying to self populate the first column of the "Main" sheet with a query that returns the first column of the "Finance Office" sheet that matches all three names.
I came up ...
0
votes
0
answers
38
views
What is the Google Sheet query for getting rows excluding non matching rows
The tables will have multiple entries for amount debited and credited details. It also has 5% debit and credit and 1% debit and credit entries as well. Data includes unique flag for districts, Date ...