Questions tagged [google-sheets-arrays]
Use for questions about using arrays in Google Sheets
23
questions
1
vote
1answer
26 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 ...
0
votes
0answers
26 views
Unable to filter rows in a sheet populated by QUERY
I am trying to filter one column A-Z to pair items listed in more than one selling app.
However, if I apply an A-Z filter for a column with item codes, the sheet rearranges everything per the filter ...
1
vote
1answer
25 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
42 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
54 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
77 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
16 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
0answers
31 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 ...
1
vote
2answers
56 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
102 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
2k 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
34 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
300 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 ...
3
votes
2answers
5k 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 ...
5
votes
2answers
898 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 ...
4
votes
1answer
3k 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. ...
1
vote
1answer
5k 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 ...
2
votes
1answer
648 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 ...
9
votes
3answers
13k 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 ...
12
votes
4answers
27k 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 ...
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 ...
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 ...
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 ...
