Questions tagged [google-sheets-arrayformula]

Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.

Filter by
Sorted by
Tagged with
0
votes
2answers
23 views

How to filter after parsing CSV values contained in Google Sheets cells

I would like to be able to match all cells in column A (titles) that have one of the comma-separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the ...
1
vote
2answers
25 views

Searching a list and replacing value if any item matches any item in the other list in Google Sheets

I have a list of usernames on two different sheets. I need to scan the first list of usernames to see if they appear in the second, and if they do, replace them with a predefined username. I can't ...
0
votes
1answer
24 views

Pull unique values from other sheets as new sheets keep getting added

I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master ...
0
votes
1answer
26 views

Merging cells based on same ID but divided into categories (columns)

I receive a pretty extent list of material that has been delivered and every single product is in a separate row. That means for one order I have easily a lot over 15 rows and I need to merge the ...
0
votes
1answer
30 views

How to use arrayformula to find and get data from only a certain, dynamic set of columns?

I have a table where my headers are dynamically generated from another source and can show up with any header values, in any order. In the example below I've got the header values: AA - BB - CC - ...
0
votes
2answers
27 views

How do I “bin” results in Google Sheets?

I am trying to bin my results so my groupings have lower granularity - going from a 1-10 scale to a 1-5 scale. For example, any result 1 or 2 would be renamed to 1 in the next column, any result 3 or ...
0
votes
1answer
20 views
1
vote
2answers
93 views

Take cells from a column, populate in two different columns following a pattern & delimiters

To better explain what result I need, I made a text block below as a pseudo sheet to illustrate. Col A (source) is the manually entered text data. Col B and C (desired result) will have formulas so ...
5
votes
2answers
8k views

Filter() - referencing source array in criteria

The filter() function looks through a source array, and filters it according to condition =FILTER(A:A,ISNUMBER(A:A)). My source array is a rather long formula, so I end up repeating the same formula ...
1
vote
2answers
55 views

Can I use arrayformula in Google Sheets to count things over a variable range?

Is it possible to create a single arrayformula that does the same thing as the following column of formula: A1: =COUNTA(B1:B1) A2: =COUNTA(B1:B2) A3: =COUNTA(B1:B3) ... A9: =COUNTA(B1:B9) My best ...
0
votes
1answer
1k views

ARRAYFORMULA() won't iterate over range

I'm trying to get all rows of data into a sheet where column headers match. Please have a look at this sample sheet. I need to achieve this dynamically so I'm using ARRAYFORMULA(). Source data +----...
1
vote
1answer
85 views

Adding missing dates to Google Sheets

I'm pulling data from HubSpot to Google Sheets via 3rd party connector. I have a raw data set on Sheet 1 and then I'm pulling certain data from Sheet 1 to other Sheets with QUERY function. For ...
0
votes
2answers
61 views

Can Weekday value be shown in natural (Su, Mo, Tu..) format in Array Formula?

For Google sheet with the following data in A2:B4 06/29/2019 1:25 PM 1 06/30/2019 1:32 PM 1 07/01/2019 1:40 PM 1 The output will show Monday 1 1 Saturday 1 1 Sunday 1 1 Can this be ...
0
votes
1answer
40 views

Look up a list embedded in a Google Sheets from a Google Forms, and add attributes to the corresponding row?

I'm running an event for a large number of children and need a way to let them register quickly as they come in, to the event. We're expecting about 400 kids who have pre-enrolled and we will have a ...
-1
votes
1answer
13 views

Sorting column A with drop-down and column B with checkbox

I have a spreadsheet that I use to run a foosball tournament, and in column A I have a dropdown data validation that will let me select a players name. In Column B I have a checkbox that I check once ...
2
votes
1answer
2k views

How can I fill the diagonal of a matrix with zeros?

I have an empty matrix: I would like to fill the diagonal of a matrix with zeros: Is there any clever way to do it? The matrix can be pretty large and it's tedious to do this manually. I could use ...
2
votes
2answers
280 views

Add missing dates to list of dates with values

Current Data: 9/20/2018 6 9/22/2018 3 9/23/2018 1 Desired Output: 9/20/2018 6 9/21/2018 9/22/2018 3 9/23/2018 1 I would like to have the missing dates added to Column A while the ...
5
votes
2answers
931 views

Fill zeros for the dates when no data was recorded

I have a table with dates (in increasing order) and corresponding values. Some dates are missing, like 3/27 below. I'd like to insert them in Date column, and have 0 entered in the Value column for ...
1
vote
1answer
22 views

Output counted list with extra qualifier

I currently have a table with the following columns: Comm ID Item Name Months Orichalcum Tempest Jade Green Iron Weltsilver Ambergelt Beggar's Lye Dragonbone Iridescent Gloaming ...
0
votes
1answer
53 views

Simple Scorecard Conditional Formatting

I simply want to create a scoresheet comparing scores to par. If less than par then a red number, if more than par then highlight red, if equal to par then highlight green. Here is what I'm trying ...
2
votes
1answer
37 views

How to sum the values of same vlookup results?

I have this formula it gets the single data from another file from a sheet at column 7. =ARRAYFORMULA( if( len($A2:$A); iferror( vlookup( ...
3
votes
1answer
2k views

Unique Values From a Matrix

I have a spreadsheet that looks like this: Column A | Column B | An unimportant value 1 | Value 1, Value 2 2 | 3 | Value 3 4 | Value 3, Value 1 | Don't ...
2
votes
1answer
26 views

How to sum amount with a criteria separately by day in Google Sheets?

I am using Google Sheets to track income and expenses. I have a separate file for each month and a master file to summarize each month. The file for monthly account contains a couple of sheets - each ...
0
votes
1answer
302 views

Concatenating URL with a value to create link, but links only partially work

I have a column of cells in Google spreadsheet with values such as: b2397 b9504 b3333 b8655 bxxxx I would like to combine each value with a URL, for example: http://websitex.com?id= ...
0
votes
1answer
194 views

Find previous non-blank cell, but without a specified range in Google Sheets

I've come across plenty of formulas to find blank cells within a certain specified range, but can't seem to figure this out / find an answer. I have a spreadsheet kind of like this: . Where column ...
1
vote
2answers
973 views

How can I create a recursive formula in Google Sheets?

I'm making a task tracker in Google Sheets and am trying to implement subtasks. I am trying to create a formula that checks a row's "Parent Task" column, and if there is a parent task, indents the ...
0
votes
1answer
413 views

Using array formula in Google Sheet

Trying to make an ARRAYFORMULA that executes a query function which takes a reference to the current position of the row number and the specified column name. This is my version of formula: =...
0
votes
1answer
28 views

Sequential numbers via formula - Google sheets - IF & MAX

I use this formula to find highest value in column A of other sheet, and then add 1 to it to get new unique number for a record ID, and I use... =if(B3<>"",max(Backend!A3:A)+1,) with this ...
0
votes
2answers
32 views

Showing top 5 most frequent values and counts of each from a data range

I have a Google sheet with text strings listed down columns D through G. I want to, on another sheet, display a list of the top, say, 5 most frequent unique values from the first sheet in descending ...
0
votes
1answer
328 views

Getting data from another Google sheet that isn't in the same order

I have a very basic knowledge of deeper functions of Google sheets and solving this has been a little bit of pain in the butt so far. I have a spreadsheet A column A contains ID column I that has ...
1
vote
1answer
28 views

Autopopulate a RANGE of Cells from one sheet (tab) to another in a single workbook

All I want to do is have cells in one sheet (tab) in a workbook auto-populate with data and formatting of a different sheet (tab) in the same workbook. I know how to do this one cell at a time, BUT is ...
0
votes
1answer
10 views

How do I insert different cell data from a table into a hyperlink?

I have a table of different cell data that I want to insert into different hyperlinks for each item: =HYPERLINK("https://www.otcmarkets.com/stock/CELL DATA TO BE INSERTED/security","SECURITY") Cell ...
1
vote
1answer
142 views

How to automatically fill missing data in Google Spreadsheet

I'm searching for the exact same thing as Google Spreadsheet: How to fill in missing values (inbetween filled values) automatically I made a spreadsheet for tracking weight, where each row is one day,...
1
vote
2answers
113 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 ...
0
votes
1answer
118 views

How to ignore certain strings when creating unique list of words in Google Sheets?

In Google Sheets, I am using the formula to create a unique list of words: =unique(transpose(split(ArrayFormula(concatenate(B2:E"+")),"+"))) But I want to filter out certain strings. My unique list ...
0
votes
2answers
12 views

How to create a unique list from a selected range of cells

I have a list in cells A1:A16 of foods and want to create a unique list with each item only appearing once in the list: Eggs Bacon Hashbrowns Baked Beans Avocado Coffee Bread Bread Sandwhich meet ...
-1
votes
2answers
25 views

How can I group by one column to join values in multiple rows?

Let's say I have a survey of people in teams, and what fruits they like: User | Team | Fruits ---------------------- Dave | Mine | Banana, Apple Steve | Mine | Apple, Orange Matt | Yours | ...
-1
votes
1answer
17 views

Odd result in Google Sheets calculation

I am editing an existing spreadsheet in Google Drive and the formula below is giving me an answer I cannot figure out. =if(isBlank({H6}),ifError(1/0),(( ArrayFormula( ROUND((P6*1.2)))-0.05))) where ...
1
vote
2answers
1k 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 ...
0
votes
1answer
24 views

How to automatically add a row above a particular cell?

I'm sending some data from Zapier to Google Sheets and every time Zapier creates a new row, all formulas from that row are gone. I'm looking for a script that would allow the following solution: I ...
0
votes
2answers
44 views

Update each cell in column from upper right adjacent cell

How can I make the start mileage in each cell update from the end mileage in the upper right adjacent cell? Preferably using something like a single array formula that can be updated. I think I'm ...
-1
votes
1answer
9 views

How to split a cell into key/value rows?

Apple,3,Orange,4,Mango,10 How can I split this cell into key/value rows? Apple (a1), 3(a2) Orange (b1), 4(b2) If I just call split(), I get: Apple 3 Orange 4
0
votes
1answer
20 views

Value transformation in Google Sheets

I'm working with a table of data as following: User ID: Event name: 111 Event 1 222 Event 2 333 Event 3 111 Event 4 444 Event 5 111 ...
-1
votes
1answer
15 views

Outputting a cell x number of times in an array format

Using Google Sheets: Need to display a vertical list, where each occurrence in the A column of sheet1 is repeated based on the number in the B column of sheet1. I've created a dummy file to show what ...
0
votes
0answers
56 views

Multiple filter Array formula in google sheets

I am trying to get my array formula to be able to filter on multiple drop down menus. I have 2 separate formulas that work but I cannot combine them. Is there a way to combine them to make the Array ...
4
votes
2answers
789 views

CountIf in upper cells with ArrayFormula

I have some values in the column A and in the column B want to calculate concatenation of the value and how many times it was used in rows above. In the usual formula, I would've written in B2 =A2 &...
0
votes
1answer
55 views

Sumproduct within Arrayformula [duplicate]

This is my formula in cell D2: =SUMPRODUCT(B:B=B2;F:F>F2)+1 The values in column are all of the same length, and are continually updated by contributions from google forms. Therefore a condition ...
1
vote
1answer
2k views

Using SUMPRODUCT with ARRAYFORMULA

I have the following random data in sheet one A orange D yellow C yellow B orange B orange C blue A yellow D blue C orange C blue In sheet two I use the following query function: A1=...
0
votes
1answer
11 views

Creating a formula based on TODAY's day

I have column A (list of stock prices at close of day) A1=4/1/2019 | stock price A2=4/2/2019 | stock price A3=4/3/2019 | stock price each day will be added to the sheet automatically I want to take ...
0
votes
3answers
27 views

Summing every other row depending on the classification of the row above

I've got a confusing question, I can't figure it out. I have this spreadsheet of states population and their political control, each on separate alternating rows (political control is designated with ...