Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
59

From https://support.google.com/docs/answer/6325535?hl=en : Open a spreadsheet in Google Sheets. Paste the data you want to split into columns. In the bottom right corner of your data, click the Paste icon. Click Split text to columns. Your data will split into different columns. To change the delimiter, in the separator box, click PS: There is no option ...


28

Yes: =ArrayFormula(SUMIF(ROW(A1:A10);"<="&ROW(A1:A10);A1:A10)) The numbers in the green circle are the ones being summed, the ArrayFormula is populating all the others based on the cell it was pasted into, in this example I pasted in A11, B1, and C1 to show its effect. This spreadsheet that I made a while ago attempts to explain how MMULT can be ...


27

Assuming you want: Row | A | B | B's formula -------------------------------- 1 | 1 | 1 | =1 (or =A1) 2 | 2 | 3 | =1+2 (or =A2+B1) 3 | 3 | 6 | =1+2+3 (or =A3+B2) etc... Then you can use:=sum($A$1:A1) where A1 is the start of numbers to sum. Put that formula in B1 (or where ever you want) and fill it down, and it will sum all cells up ...


5

You can transform the columns by using TRANSPOSE, i.e., assume "First member, Second member, Third member" are A1:A3, you can use function in somewhere, = TRANSPOSE(A1:A3) will fill column as below, First member Second member Third member To repeat the column before and after team members, you can try, = TRANSPOSE (SPLIT(REPT(CONCAT(A1, ","), ...


4

Short answer Use ARRAY_CONSTRAIN(array,row_limit,column_limit) Explanation ARRAY_CONSTRAIN function could return the number of rows and columns that you want, just set the row_limit and column_limit parameters accordingly. To automatically set this parameters you could use a function like COUNT or COUNTA. To calculate the number of rows/columns ...


4

=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")))


3

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 alternative is to use the arithmetic operators (+,-,/,*). If it makes sense to assume that all ranges are of the same size there aren't blank cells between non-...


3

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&A: How to use ARRAYFORMULA and SUM together to get a column with the sum of the preceeding columns? Make a custom function Limitations: Only works when ...


3

You could split the cell's value into individual cells. Given that cell A1 contains the number 2113, put the following in cell B1: =SPLIT(REGEXREPLACE("" & A1, "(\d)", "$1,"), ",") This should result in cells B1:E1 containing each respective digit. What the formula does is three things: The expression "" & A1 converts the input number 2113 to a ...


3

A1: =ARRAYFORMULA(JOIN("-",TEXT({"7/22","7/28"}+(COLUMN(A1)-1)*7,"M/DD"))) Drag to right. Two dates are initialized and a column number*7 is added per each column. TEXT to format dates " m/dd" JOIN the 2 dates by -


2

For a conditional running average, assuming all entries in A2:A are grouped: =ArrayFormula(IFERROR((SUMIF(ROW(A2:A),"<="&ROW(A2:A),B2:B)-HLOOKUP(0,SUMIF(ROW(A2:A),"<"&ROW(A2:A),B2:B),MATCH(A2:A,A2:A,0),0))/(ROW(A2:A)-MATCH(A2:A,A2:A,0)-ROW(A2)+2))) Before the update to the newest version of Sheets a number of months ago, it would have ...


2

Use the Google Sheets array handling features and FILTER to get the desired result =FILTER({A:A,B:B,E:E;A:A,C:C,E:E;A:A,D:D,E:E},LEN({A:A;A:A;A:A})) Note: If you spreadsheet use comma , as the decimal separator, replace the comma in the above formula by backslash \


2

In array formulas instead of AND and OR functions use * and + respectively. Example AND: Instead of =ArrayFormula(and(A2:A4,B2:B4)) use =ArrayFormula(A2:A4*B2:B4) OR: Instead of =ArrayFormula(or(A2:A4,B2:B4)) use =ArrayFormula(A2:A4+B2:B4)


2

I ended up doing this the not-so-smart way: instead of making VLOOKUP happen across multiple columns, I copied all of the English terms multiple times and stacked them vertically in one column, then put the German translations vertically next to them. This wasn't exactly elegant and wouldn't have worked on a much larger data set, but it let me run the ...


2

Try =ARRAYFORMULA(IFERROR(MATCH(A2:A12&ROW(A2:A12),FILTER(A2:A12&ROW(A2:A12),A2:A12<>""),0)))


2

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 the numeric values in columns A to C from row 2 down, use simple addition instead of sum(): =arrayformula( A2:A + B2:B + C2:C ) When there are lots ...


2

This does seem very identical to an older question/answer. In fact, so close that you can get the two by just taking my previous solution and breaking it apart. =ARRAY_CONSTRAIN( SORT(ArrayFormula({TRANSPOSE(F1:1),ABS(TRANSPOSE(F1:1)-B7)}),2,1),1,1) to find the value. =MATCH(B8,A1:1,0) to find its column "number" where A = 1 I have entered the formula in ...


2

={UNIQUE(FILTER('Sheet 1'!A1:A, 'Sheet 1'!A1:A<>"")), ARRAYFORMULA(SPLIT(TRANSPOSE( QUERY(TRANSPOSE(IF(TRANSPOSE( QUERY(TRANSPOSE( QUERY({'Sheet 1'!A1:B}, "select count(Col1) where Col1 !='' group by Col1 pivot Col2", 0)), "select Col2, ...


2

all you need to do is QUERY out empty rows: =QUERY({IMPORTRANGE("1cLqgopAWG...", "Sheet1!A1:B5"); IMPORTRANGE("1564EXoW-s...", "Sheet1!A1:B5")}, "where Col1 is not null")


1

I stumbled onto the answer myself, but attached is a sample data sheet in case this helps anyone else with a similar need. The trick was taking the ending row reference "350" out from all ranges. https://docs.google.com/spreadsheets/d/1-dxpI3GpLAxkoMyscPRsCwkaec1CWRuX5-5Ej0DjVTo/edit?usp=sharing


1

EXPLANATION OF BAD FORMULA =MATCH(E6,indirect("'5 Star Gear Sets (Hidden)'!E6:E"),0) =MATCH(B6,indirect("'5 Star Gear Sets (Hidden)'!A6:A"),0) The extra formula starting with a second = is the same as wrapping the entire formula with AND( , ). =AND(MATCH(E6,indirect("'5 Star Gear Sets (Hidden)'!E6:E"),0),MATCH(B6,indirect("'5 Star Gear Sets (Hidden)'!A6:A"...


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