CODEWITHSUNDEEP
arraysif-statementgoogle-sheetsgoogle-sheets-formulaarray-formulas
eceo
eceo

Reputation: 179

Shortest formula to add Column to Array in Google Sheets

A 2D Array with unpredictable size in Google Sheets :

name | age 
Sam  | 20
Sim  | 30
Jim  | 25

To be converted to (Added a constant string A)

name | age | Semester
Sam  | 20  | A
Sim  | 30  | A
Jim  | 25  | A

A formula like

={A1:B,"A"} Doesn't work.

Upvotes: 1

Views: 1386

Answers (4)

eceo
eceo

Reputation: 179

Figured an easy way to do this,

=query(A1:B,"select A,B,'A' where A is not null label 'A' ''")

This solves the problem.

Upvotes: 1

nabais
nabais

Reputation: 2037

in my opinion you can put the following on your C1 cell:

={"Semester";arrayformula(if(len(a2:a),"A",))}

Then, if you make a filter and sort, it will preserve the values of the whole column:

enter image description here

Upvotes: 0

player0
player0

Reputation: 1

shortest would be:

=INDEX(IF(A2:A="",,"A"))

Upvotes: 0

marikamitsos
marikamitsos

Reputation: 10573

You can try

=ArrayFormula(IF(V2:V="",,"A"))

enter image description here

Without a formula
Or you can place A in cell C2 and double click on the hair cross on the bottom right corner of the cell.

Upvotes: 1

Related Questions