Questions tagged [google-sheets-arrays]

Use for questions about using arrays in Google Sheets

Filter by
Sorted by
Tagged with
17
votes
3answers
40k views

Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with ...
10
votes
3answers
18k views

Can you do a UNION SELECT in a Google Sheet?

I've got a Google Sheet linked to a form which collects replies from three different people on the same subject using the same form and editing three times. This creates a sheet which contains three ...
6
votes
2answers
1k 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 ...
5
votes
2answers
5k views

Google Spreadsheets function to copy array

Is there an array function to copy an array? I use the workaround =TRANSPOSE(TRANSPOSE(A1:A30)) currently. An example of when this is useful: you have a worksheet of raw data, and another worksheet ...
5
votes
2answers
9k 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 ...
4
votes
1answer
4k views

Display the contents of a named range elsewhere in the sheet

I have a sheet like so: A B 1 W X 2 Y Z I have the range A1:A2 named as "Column1". I want to display that range elsewhere. Now if I wanted to display it as a row, I know =TRANSPOSE(Column1) works. ...
3
votes
2answers
8k views

Summing top three values in a column from a Google sheet

I have a Google Sheet with a variable number of data rows. I would like to sum the three highest values from a certain column, but they won't necessarily be in the top three cells. An empty cell ...
3
votes
2answers
116 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: john@smith.com 3 Name: Dan Jones 4 Email: dan@jones.com ... many rows which I'd like to get into this format:...
3
votes
1answer
596 views

Filter in Filter function Google sheets

I have a Sheet with the prices of some products Each column is a parameter. How to use the FILTER() function in order to filter by multiple conditions In this case I need to filter and see only ...
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
2answers
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
1answer
707 views

Combining data from two columns to one (in separate rows - not comma separated) and removing blank lines

I have a Google Sheet which receives information from a google form. I would like to collate some of this data in a separate sheet (within the same document). I would like the new sheet to combine ...
1
vote
1answer
6k views

How do I filter the array returned by a filter or other formula?

I want to filter an array returned by a filter to remove blank columns. Here is a picture of an example sheet: Here is the example sheet. I am filtering the large table by the rows that are of type ...
1
vote
1answer
34 views

Create list of cell addresses contained in a range

I'm looking for a single arrayformula to convert a specified range, such as B2:D4, into a list of addresses (not values!) contained in that range, in this case B2,B3,B4,C2,C3,C4,D2,D3,D4. Attempts ...
1
vote
1answer
26 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. ...
1
vote
1answer
29 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:...
1
vote
2answers
67 views

Is there a way to generate many rows and columns from a single value?

Let's say I have a sheet with a single column that lists all of my clients with each row being a unique value: e.g: client1,client2,client3,client4,... Goal: I'd like that in a separate sheet, 2 ...
1
vote
1answer
67 views

How to sum values between two dates?

I have a table made up of numbers followed by dates like the one below only with more columns (link below). How would I sum only the values that occur in one specific year? For example, in this case,...
1
vote
2answers
294 views

Remove Count column by repeating rows in Google Sheets

I have a worksheet, 'wi', in a Google Sheet similar to the following example but with an undetermined number of rows: Count | Entry | Description -------|-------|----------------- 2 | ...
1
vote
1answer
30 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 ...
1
vote
1answer
21 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 ...
1
vote
1answer
130 views

Google Sheets - Identify if one of many matches any in an array

I am trying to determine if an array of values in Google Sheets has any matches within another array of values. The data set is constantly expanding, so I want to use an Arrayformula to handle it. ...
1
vote
1answer
95 views

Google sheets Xlookup dont work but need results in table the max date on the condition of userlist

Need some help with some data processing. Link of the sheet Example - I need on : Page-2-Seznam članov > Coloumn C : It needs to be the max dates. The function need to search on the liste the ...
1
vote
1answer
37 views

How to reference the result of a IMPORT function as a defined name in a single cell

Note: The motivation for my question is just formula readability. My sheet is working fine. Let's pretend I have an IMPORTXML function that's currently returning 2 columns and a varying number of ...
1
vote
0answers
194 views

Split rows by comma into multiple rows and divide between splits

I have a Google Sheets spreadsheet (sheet 1) which I use as source for a function to split a row into several rows in a results worksheet (sheet 2). The split function divides the row when it finds ...
1
vote
2answers
4k views

Filtering Pivot table by multiple values

I have a pivot table below that I am trying to filter dynamically based on a separate list and am trying to figure out how to do this by referencing the relevant field. In the below example I have ...
0
votes
1answer
28 views

Use a filter on an array of values

I want to create a spreadsheet which has an array of values split over 1 or more columns. I can do this successfully with the following formula: ={"Picard","Q","Data"} This puts each of these values ...
0
votes
1answer
20 views

Add single value as constant line to diagram

I have a range A1:B of which I make a simple line diagram. I also have a single value in another cell, say C1, which I want to use as a constant reference line in the diagram. I know I could populate ...
0
votes
1answer
39 views

Converting multiple columns to list

I'm currently using Google Sheets and I'm capturing data with Google Forms. What I'm receiving from Forms, inside the corresponding sheets is an entry with timestamp, clientID, item1, quantity1, ...
0
votes
2answers
42 views

View only few non-continuous columns

=FILTER(Matrice_data;Matrice_nom=G2) Pretty straight forward. I type a name in G2 and I get all rows with this name. My problem is this one: the "matrice_data" contains many columns, like 30. I want ...
0
votes
1answer
25 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,...
0
votes
1answer
29 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, ...
0
votes
1answer
20 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 ...
0
votes
0answers
8 views

Semicolon and commas for array [duplicate]

Okey I'm trying to use Arrays in Google sheets and I'm loosing it soon. And there's not any answers our there. So in us and so on you use ={1,2;3,4} And you get 1 2 3 4 But in Sweden we ...
0
votes
0answers
36 views

Loop through array from importxl in google sheets?

I want to scrape this website[1] for dividend payouts and payout dates (called "utdelningar") in the right lower part. Using the following importxml[2] i get the entire list of dates etc that i want ...
0
votes
0answers
32 views

Passing array from Google Form to script

I've currently a form that a user fills in and the data is then entered into a spreadsheet. All this works well. I now need to make one of the inputs an array so that more than one value can be ...
0
votes
1answer
67 views

More compact version of nested substitute function possible?

I want to make about a hundred changes in a column on a spreadsheet. These changes have to be remade each time a new copy of the original comes. The changes are spread over several thousand rows. ...
0
votes
1answer
90 views

Move existing SUM downward when IMPORTRANGE expands

I'm building a complex Google Sheets document and for one sheet I use the function IMPORTRANGE. It's very handy and imports new rows from another sheet when they are added. The problem is this: After ...
0
votes
1answer
22 views

How to duplicate range of cells n-times(range can variate and must be based on column)

Can someone explain for me how with the help of formula I can solve this issue? We have an input range of Tags (letters and numbers). Whole massive must be duplicated in the same order n times. The ...
0
votes
2answers
1k views

Consolidate columns from a dynamic range into a single column in Google Sheets

How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case, I want non-unique cells. Also, each cell in the range ...
0
votes
1answer
537 views

Duplicate values in dynamic sorting

I have an issue with duplicate sorting values for a formula in Google Sheets. I'm using two tables to order 10 scores (see pic). The values in Column D are dragged from another sheet. The values in ...