Skip to main content
The 2024 Developer Survey results are live! See the results
18 votes
Accepted

How can I create a unique list of data across multiple rows AND columns?

=UNIQUE({A:A;B:B;C:C}) =ARRAYFORMULA(UNIQUE(QUERY(TO_TEXT({A:A;B:B;C:C}), "select Col1 where Col1 is not null")))
user0's user avatar
  • 26.8k
13 votes

How can I create a unique list of data across multiple rows AND columns?

=unique(flatten(A1:Z)) I think this will work and it worked for me.
Nis's user avatar
  • 131
12 votes

How to Fill a Column with Sequential Dates in Google Sheets

The next level would be to use ARRAYFORMULA: =ARRAYFORMULA(TEXT(ROW(INDIRECT("A"&DATEVALUE("2018-4-13")& ":A"&DATEVALUE("2018-4-30"))), "dd/mm/yyyy"))
user0's user avatar
  • 26.8k
10 votes
Accepted

How to use custom function with arrayformula?

In order to use a custom function with array formula, it should be able to return to get as input and return as result array of values. The custom function provided as example has no input parameter ...
Rubén - Volunteer Moderator -'s user avatar
9 votes
Accepted

Google Sheets: Is there kind of map function?

Alternatives: Use ArrayFormula / MMULT functions Limitations: Some formulas should be "redesigned in order to be used with ArrayFormula / MMULT or other similar built-in functions. Related Q&...
Rubén - Volunteer Moderator -'s user avatar
8 votes

Can you do a UNION SELECT in a Google Sheet?

It is possible to make a union in Google Spreadsheet very easily nowadays with this formula: ={'Sheet1'!A2:A;'Sheet2'!A2:A;'Sheet3'!A2:A} See more info in Google Docs Help: Using arrays in Google ...
Emilio Nicolás's user avatar
7 votes
Accepted

Summing top three values in a column from a Google sheet

Formula =ArrayFormula(SUM(IFERROR(LARGE(A1:A2000,{1,2,3}),0))) Explanation LARGE returns the nth greater element. IFERROR if the first argument returns an error, returns the second argument. {1,2,3}...
Rubén - Volunteer Moderator -'s user avatar
7 votes

LEFT JOIN two and more tables in Google Sheets

one-cell solution (paste in D1): ={"material", "required", "total"; ARRAYFORMULA(IFERROR(VLOOKUP(B2:B, Sheet2!A2:C, {2,3}, 0), )), ARRAYFORMULA(IF(LEN(A2:A), C2:C*VLOOKUP(B2:B, Sheet2!A2:C, 3, 0), )...
user0's user avatar
  • 26.8k
7 votes
Accepted

Convert Text String into Date/Time Value

={DATE(RIGHT(A1, 4), MONTH(MID(A1, 5, 3)&1), REGEXEXTRACT(A1, "\d+")), TEXT(INDEX(SPLIT(A1, " "), 1, 4), "hh:mm:ss")} with timezone in account: ={DATE(RIGHT(A1, 4), MONTH(MID(A1, 5, 3)&1), ...
user0's user avatar
  • 26.8k
6 votes

Cumulative Sum without Script

The accepted answers are far too complicated for running tallies. A much easier answer is: =SUM(A$1:A1) Substitute the A for the column you want. Substitute 1 for the row you want. Click the small ...
Altimus Prime's user avatar
6 votes

Cumulative Sum without Script

While the accepted answer is on spot it has a major disadvantage due to the fact that the last parameter of SUMIF needs to be always a "valid range" so when it comes to some more advanced running ...
user0's user avatar
  • 26.8k
6 votes

How to use AVERAGE inside ARRAYFORMULA in Google Sheets

Aggregation functions like AVERAGE, SUM and other similar that get as parameters array of values and just return a single value are not able to return array of values by using ARRAYFORMULA. One ...
Rubén - Volunteer Moderator -'s user avatar
6 votes

Escape single quote in cell content in a QUERY

Plain simple quote "inversion": =query('Book list'!A:F,"select B,C,D,E where F=""free"" and D="""&E1&"""")
Rafal's user avatar
  • 61
6 votes
Accepted

Rearrange Name Format in Google Sheets using one formula only

Delete everything from B:B (including the header) and try this formula in B1: =ArrayFormula({"Reformatted Name";IF(A2:A="",,TRIM(REGEXREPLACE(REGEXREPLACE(A2:A,"Jr$|Jr.$|Sr$|...
Erik Tyler's user avatar
  • 5,078
5 votes

LEFT JOIN two and more tables in Google Sheets

Recently I create a script to do LeftJoin on tables, my script resolve your case. The script join up to 3 right tables, but can be easyli increased to more. So enjoy. Supose you have all in A1:C5 and ...
SauloAlessandre's user avatar
5 votes
Accepted

Pie chart with sub-slices per category in Google Sheets?

your dataset of A1:C is not in "good" shape for creating a chart, so first, you will need to re-shape it by adding few formulas in auxiliary columns which you can hide when done in cell E1 paste this ...
user0's user avatar
  • 26.8k
5 votes
Accepted

In ARRAY_LITERAL, an Array Literal was missing values for one or more rows

...understanding the ARRAY_LITERAL ERROR: until both queries/filters/formulas output something then all is good: however if one of those queries/filters/formulas doesn't have anything to output it ...
user0's user avatar
  • 26.8k
5 votes
Accepted

Print incremental numbers in Google Sheets

It is as simple as using the following sequence formula =SEQUENCE(A1) Or you even could have something like this: =SEQUENCE(A2,1,A4,A6) You could even use the SEQUENCE function for dates =...
marikamitsos's user avatar
  • 12.9k
5 votes

ISBLANK function returning unexpected results

To check if ALL cells in range A2:C2 are blank =AND(ARRAYFORMULA(ISBLANK(A2:C2))) To check if ANY cell in range A2:C2 is blank =OR(ARRAYFORMULA(ISBLANK(A2:C2)))
NDavis's user avatar
  • 153
5 votes
Accepted

Is there a way to return multiple columns with XLOOKUP within an ARRAYFORMULA?

Use map(), like this: =map(A2:A, lambda(a, xlookup(a, Sheet2!A2:A, Sheet2!D2:G))) See map() and lambda().
doubleunary's user avatar
  • 15.1k
4 votes

How to Fill a Column with Sequential Dates in Google Sheets

Logic: In first column we can put current date using =Today() this will give current date. In order to get next date we must increase the date by 1. To get next date we can add =Today()+1 If you ...
Xitij Thool's user avatar
4 votes

Numeric before query but query results are not a numbers

The fix I used for this issue was to coerce the strings into a number by using ARRAYFORMULA(A1:A+0) in a new column adjacent to the original query (let's say column B). I then used another query on ...
Friendly Neighbor's user avatar
4 votes

Google Sheets Arrayformula on both horizontal & vertical cells?

Digging the web I have found a Google Post that states: Aggregating functions such as sum() will not produce multi-cell results in an arrayformula() wrapper. To calculate row-by-row totals of ...
Riccardo's user avatar
  • 589

Only top scored, non community-wiki answers of a minimum length are eligible