Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
1
vote
2answers
17 views
Conditional formatting rule that compares against a range of values
I have a list of 2019 dates (col A) and holiday dates (col G). Wherever there's a date in col A that matches any date in the range G2:G20, I would like to highlight it in grey. See this sample ...
0
votes
2answers
12 views
How to scan rows for match with ArrayFormula?
Trying to search a row for a given value, but want to make an ARRAYFORMULA so that every time a new row is added the search will be performed on that row.
Currently using =IF(COUNTIF($A2:$EI2,"="&...
1
vote
1answer
24 views
JOIN two columns in one cell
What should I do if I want every time I fill out column C, to all data from B6 to C14 would appear on the notes?
1
vote
1answer
12 views
Combing two ranges with identical column structure from two different sheets (in same spreadsheet)
I'm doing a simple ticketing system, where I have two sheets, "Form Responses" and "Email Responses" both of which are populated automatically as their names imply (one from a form, one from an email ...
1
vote
1answer
9 views
How to combine dynamic columns in Google Sheets while retaining data in other rows?
I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns.
I want to ...
1
vote
1answer
16 views
Creating a parent ID cell in Google Sheets
I've got a spreadsheet of various categories and I'd like to create a column which gets the parent name of that particular category.
They are currently broken down as follows:
01.01 Category 1
01.02 ...
1
vote
1answer
20 views
Pie chart with sub-slices per category in Google Sheets?
I have a sheet that contains a list of books, with metadata about those books. Two columns that exist are the library within which the book is stored, and the condition of the book. I'd like to create ...
0
votes
4answers
23 views
How to pull the page number and the number of images listed from one sheet and then populate cells in another sheet accordingly?
Sorry, I didn't know how to summarize it in the title. Basically, I have a spreadsheet where I'm keeping track of the number of images that need to be on each page of a document I'm working on.
On ...
1
vote
2answers
22 views
Query using date range doesn't work for specific data
I have a spreadsheet (URL below). I've set up a query to make sure I understand how to do a query with a date range. Then I tried to create a similar query on the "real" data. The "Week 1" worksheet ...
1
vote
2answers
56 views
Cartesian Product with constraints
I have a really big database that I have to sort and divide as the example below:
F1 F2 F3 C1 C2 C3 C4 results
P1 X X X X X P1 ...
0
votes
1answer
14 views
Number in a cell linked with a text
I would like to ask if there is a solution to my problem:
I have 200 numbers from 1 to 200 which are linked with text. For example: 1 = car, 2 = bicycle and so on. I would like to write a number (1) ...
1
vote
1answer
13 views
Apply a formula to each cell in column before computing a sum of the cells
How could I apply a formula to each cell in a column before computing a sum of the cells in Google spreadsheet?
In other words, I have a column of cells with numbers. And what I want is to iterate ...
1
vote
1answer
11 views
How to concatenate cells using ARRAYFORMULA across rows and columns?
I have a document that's a recurring CSV import/export.
I'm trying to concatenate images separated by ", " and using ARRAYFORMULA to make it all the way down.
The problem is, I don't know how many ...
1
vote
1answer
18 views
Populating alternatives in question 2 based on answers in question 1
I have ten people who sell something to companies, and I want to track what they do IN GOOGLE FORMS. Every agent has his own 10 companies that he contacts.
So, in Question 1, I will have a drop-down ...
-1
votes
3answers
52 views
Finding closest value within a range of cells spread across the rows
Attached the Google Drive link and we have to refer to scenario 2 sheet of the spreadsheet.
https://drive.google.com/drive/folders/1HKePsd4SPSNdPujnwKLvq2i8xeg5sQgy
Here some value is given in rows ...
0
votes
2answers
7 views
Import range from 3 sheets/tabs into 4th
I currently have 3 sheets (tabs) in a Google Sheets workbook. Each column header (Row 1) in the 3 sheets are identical. I'd like to create a 4th sheet that combines ALL the contents in the 3 sheets ...
1
vote
2answers
42 views
Google Sheets Query with Date and Time
I need help with a query. I need to filter on some Google Form data and only show the results that have been submitted AFTER a specific timestamp.
Timestamp Cell = J2 - This cell is in the following ...
0
votes
1answer
18 views
Automatically (protect or update or retain) a cell's formula when a row is added from “Zapier and Twilio”
When a row is added in this case from a (Zapier into a Google sheet, the formula in a cell not used by the incoming data is deleted.
How can I retain/protect the original formula in column AE being =...
0
votes
3answers
44 views
Finding row number from value which is in between ranges
I have attached a picture containing two column B and C. If my input to this table is 2.3, it should return me row number where my input value is greater than or equal to B column value and less than ...
2
votes
2answers
44 views
ARRAYFORMULA with IF, DETECTLANGUAGE and LEN
I'm trying to make an array formula to detect the number of English characters in a range.
When I use the following for individual cells:
=IF(DETECTLANGUAGE(ARRAYFORMULA(A5:A))="en",LEN(ARRAYFORMULA(...
1
vote
3answers
135 views
Find the closest value within a range of cells and output cell reference
There are values from F column to AJ column in row 30. My input would be available in C column in row 30. As given in the picture, my input would be 118.45, I would like to find the closest value to ...
-1
votes
1answer
30 views
ArrayFormula syntax to reference the previous row for calculating the current row
Sample sheet here (in sheet3): https://docs.google.com/spreadsheets/d/1hXZzwRnYpso_Q4PHd6M818oE347yRBAh9AqTLhqX8TM/edit#gid=318977824
In part 3 of Arrayformula Alternatives series of question, I ...
1
vote
2answers
29 views
Create random pairs without duplicates
I try to randomly pair people into unique pairs.
I have two columns one with helping values created via Rand() and one that ranks upon these values with
=INDEX($A$2:$A$164,RANK(S2,$S$2:$S$164))
...
1
vote
1answer
17 views
Is it possible to return a range from an IFS or SWITCH?
I am trying to simplify some 'if else if else' code by replacing it with a single IFS or SWITCH expressions. I have simplified all the code to be trivial but show the problem.
This code works and two ...
1
vote
2answers
27 views
Google Sheets' Query or Filter solution
I have a simple table with columns:
A (Donor ID)
B (Donation date)
C (Donation Amount)
I need to extract the following report:
For each donor ID most recent donation date and amount donated ...
0
votes
3answers
20 views
Picking the Total from the Next Week
I have the next year mapped out (one line for each day) and have the week subtotal at the end of each week (which is a Friday).
I am wanting the Next weeks subtotal to show in another cell for that ...
0
votes
3answers
23 views
Average formula for Google Sheets with substitution over some figures
I have a row of figures from 0 to 2, some cells are empty. I need to count an average for them, but with the rule that would count 2 as 1.
Thanks
0
votes
3answers
38 views
LEFT JOIN two and more tables in Google Sheets
Say I have two tables. Table A (material requirements):
product_id | material | required
1 | A | 0.5
1 | B | 0.7
2 | A | 0.2
3 | A | 0....
0
votes
2answers
28 views
Create a row based on the sum of every column in a sheet
I have a spreadsheet with a data set containing hours:minutes:seconds. This dataset is a record of logged times per user (rows). The number of rows or columns isn't fixed. The number of columns is ...
1
vote
2answers
7 views
I am trying to obtain formula to increment cell by 11 rows for Google Sheets
Here is what I am doing:
=IF('unique stock'!C11<=0,'unique stock'!C11,"")
and this goes on...
=IF('unique stock'!C22<=0,'unique stock'!C22,"")
=IF('unique stock'!C33<=0,'unique stock'!C33,"...
1
vote
1answer
15 views
How to stack ranges horizontally?
If {range1; range2; range3} (I believe this is called embedded arrays) outputs the combined data in a column - top to down, what puts them side by side next to each other?
The 3 ranges may or may ...
2
votes
3answers
27 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,", ")...
0
votes
1answer
24 views
Blank Redundant Month
I have a large list of days and quantities that I wanted to chart so we could see how many of something we sold on a specific day.
Give this goes back 600 days, there is a lot of data so I have been ...
1
vote
2answers
25 views
Perform operation on specific column in filtered range, right after filtering
I have this formula in E2: =FILTER(A1:C20,B1:B20=2) and need to perform substitute like:
spreadsheet here
I'm hoping to do the formula in a single cell, without additional columns.
1
vote
3answers
33 views
Identify max value, divide others by half, then total all
This may be too specific of a formula, but I'm hoping there is a function or script that will accomplish it.
I need a Google Sheet to identify the largest number in a row and divide all of the OTHER ...
0
votes
2answers
62 views
ArrayFormula for conditional consequential sum-up?
I have a table which looks like this:
And formula in cell B3: =IF(A3>5, B2, B2+1) and on every next row a corresponding variation of this formula.
I would like to know how to create ArrayFormula ...
1
vote
1answer
25 views
Formula for filtering all duplicate data pairs in Google Sheets
I'm making a table to check duplicate data.
Where data that wants to be duplicated is in the "Sampel1" column and the duplicate checking results are in the "Status" column.
The formula that I have ...
1
vote
1answer
27 views
Get URL from web page using IMPORTXML
Though I am new to web scraping, but I have a hand on experience of web scraping using VBA (Excel) I am facing the following problem:
I have tried all methods (ImportXML, ImportHTMP, ImportData, etc.)...
1
vote
1answer
10 views
LOOKUP for data from other sheet
So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail.
So what I'd like to do is, ...
1
vote
3answers
52 views
SUM columns from other sheets and group by month, but also show the individual sheets values before doing SUM
Currently, I have a spreadsheet which has a number of customer sheets, each with a pivot table showing the month in the row I and the total income for each month in row L.
On a separate sheet I have ...
0
votes
2answers
17 views
How to round float to bigger integer?
In Google Sheet, we can use ROUND() to round float to the nearest.
like
5.2 = 5
5.5 = 6.
What if I want it:
5.2 = 6
5.5 = 6.
If the number exceeds by a decimal, then rounded off to +1.
Any idea?
1
vote
1answer
18 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 ...
1
vote
2answers
22 views
How to check if the 1st letter in a cell is UPPERCASE
I'm trying to find out if the 1st letter in a cell is UPPERCASE. I know how to convert case using PROPER(), UPPER(), and LOWER(), but not how to detect case.
The following checks every word in the ...
1
vote
1answer
21 views
Can results of ARRAYFORMULA be used as values in an annotated timeline?
I am trying to create an annotated timeline with the following data, which exists on another sheet:
I am using an ARRAYFORMULA to retrieve the text. I want to display these values as an annotated ...
1
vote
1answer
24 views
How can I create a unique list of data across multiple rows AND columns?
Google Sheets has a UNIQUE() function which when given a column of values, will output a column of all unique values.
This works well for column-specific data, however, you have multiple columns and ...
0
votes
2answers
41 views
How do I tie an enum to a set of numeric values?
I created a list of items Data Validation in a sheet corresponding to time-frequency:
weekly,monthly,annually
I want to multiply values in a numeric column with the corresponding weekly values to ...
0
votes
2answers
29 views
ArrayFormula for consequential sum-up?
Here's a sample test spreadsheet - https://docs.google.com/spreadsheets/d/
What do I have to do in the ONE cell in column D such that I won't have to copy and paste the same formula in column C ...
0
votes
1answer
21 views
Autofill a cell when dates are entered in 3 other cells
I'm trying to create a way to keep track of our jobs by creating a Google spreadsheet. We can only order our final product when the digital and hard copy proof is approved and we have an order from ...
0
votes
1answer
18 views
Create sequential dates with each date repeated multiple times
For every day of the year, I need the day repeated in 10 rows. How can I accomplish this efficiently?
e.g.
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/...
0
votes
2answers
41 views
Match word in a cell with a column of words
I need to match a word to a list of words and then insert the category name in the cell next to it.
Eg. I will in Column A type a list of words like Apple, Red Apple, Coke, Carrot
The formula must ...
