Questions tagged [google-sheets-arrays]
Use for questions about using arrays in Google Sheets
61
questions
-2
votes
1answer
30 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 ...
0
votes
1answer
26 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
2answers
36 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 ...
0
votes
0answers
10 views
Totals on query table
I would like to get totals on the bottom of a table created with a query function. I have managed to total one column but when I try and get a total for the next column it displays the total 1 cell ...
0
votes
1answer
66 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 ...
0
votes
1answer
34 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 ...
0
votes
1answer
33 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 ...
0
votes
1answer
34 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
=...
0
votes
1answer
35 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 ...
0
votes
0answers
39 views
Array Forumlas and Sumifs in Google Sheets
I have a inventory spreadsheet that I am using to keep track of my inventory and I want to add a new location to track. I added the location into my forms and they populated on the spreadsheet fine. ...
0
votes
1answer
210 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 ...
1
vote
1answer
51 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 "...
0
votes
1answer
26 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 ...
1
vote
3answers
32 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 ...
2
votes
1answer
66 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 ...
1
vote
1answer
24 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 ...
0
votes
1answer
331 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 ...
1
vote
1answer
1k 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:
=...
0
votes
1answer
45 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 ...
0
votes
1answer
29 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 ...
0
votes
1answer
24 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, ...
0
votes
3answers
422 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 ...
1
vote
1answer
178 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/...
0
votes
2answers
46 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,...
1
vote
1answer
65 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
votes
1answer
38 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, ...
3
votes
2answers
508 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:...
1
vote
1answer
35 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. ...
0
votes
1answer
33 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 ...
1
vote
1answer
30 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
1answer
27 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 ...
0
votes
1answer
72 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 ...
1
vote
2answers
71 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 ...
0
votes
1answer
437 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
2answers
214 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 ...
1
vote
1answer
1k 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.
...
0
votes
1answer
31 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
64 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, ...
1
vote
1answer
46 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
130 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
63 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 ...
3
votes
1answer
2k 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 ...
1
vote
1answer
88 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
0answers
275 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 ...
0
votes
1answer
37 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 ...
1
vote
2answers
410 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 | ...
0
votes
2answers
2k 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 ...
1
vote
2answers
5k 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
2answers
52 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
658 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 ...