Questions tagged [google-sheets-arrays]

Use for questions about using arrays in Google Sheets

Filter by
Sorted by
Tagged with
22 votes
3 answers

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 ...
kirk tab's user avatar
  • 331
14 votes
3 answers

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 ...
AnneB's user avatar
  • 187
6 votes
2 answers

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 ...
user avatar
5 votes
2 answers

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 ...
Steve Bennett's user avatar
3 votes
1 answer

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 ...
Damien Bargiacchi's user avatar
2 votes
1 answer

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 ...
sofname's user avatar
  • 137
1 vote
2 answers

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 | ...
Tom's user avatar
  • 163
1 vote
2 answers

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 ...
Tom's user avatar
  • 163
0 votes
1 answer

Google sheets array search

I am trying to write a formula where Cells A2:A16 are searched for numerical values and the results are output in cells C2:F2. Within the data that will populate in A2:A16, there will only be 0-4 ...
Colin Mills's user avatar
0 votes
3 answers

=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 ...
John Tokish's user avatar
0 votes
1 answer

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, ...
Felix Pau's user avatar
0 votes
1 answer

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. ...
Sherwood Botsford's user avatar