Using ARRAYFORMULA with Multiple cell values in to one

Question

Im trying to add few cell values to one separate (Column) Cell with Line brake for each Cell Value. (Using Google Sheets) The Formula im Using is

={"ID" ;ARRAYFORMULA(CONCATENATE(H2:H,CHAR(10),I2:I,CHAR(10),J2:J,CHAR(10),K2:K,CHAR(10),L2:L,CHAR(10),M2:M))}

The Issue im facing is all the data is coming on to Cell O2 Please check Image

What I want is for each row to get the values on relevant Column

Thanks in Advance

The Sheet Gets Updated with New Rows

Kuru KumBa
– Kuru KumBa

2021-06-07 15:44:13 +00:00
Commented Jun 7, 2021 at 15:44 — Kuru KumBa
– Kuru KumBa, Commented Jun 7, 2021 at 15:44

JPV · Accepted Answer · 2021-06-07 16:05:45Z

1

Alternatively, you can also try in O1

={"ID"; ArrayFormula(substitute(transpose(substitute(trim(query(transpose(substitute(H2:M, " ", "_")),,rows(H2:H))), " ", char(10))), "_"," "))}

answered Jun 7, 2021 at 16:05

JPV

27.4k4 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kuru KumBa Over a year ago

Im Trying to Get a Cell Value (SheetA) which was created by a ARRAYFORMULA to a separate Sheet (Sheet B). Where I need to Make Changes to it on new Sheet ( SheetB ) The Data is on Column P Cell 2 on Sheet A and data Rows are Added What Must i Use to get relevant data But it must be editable Tried IMPORTRANGE But It gives errors

JPV Over a year ago

Values imported with a formula can't be edited or the formula will brieak. Only alternative would be to import these values with a google script.

Aresvik · Accepted Answer · 2021-06-07 16:17:47Z

0

I suspect you need this in cell O1:

=ARRAYFORMULA({"ID" ;H2:H&CHAR(10)&I2:I&CHAR(10)&J2:J&CHAR(10)&K2:K&CHAR(10)&L2:L&CHAR(10)&M2:M})

Probably good to add a trim as well:

=ARRAYFORMULA({"ID" ;trim(H2:H&CHAR(10)&I2:I&CHAR(10)&J2:J&CHAR(10)&K2:K&CHAR(10)&L2:L&CHAR(10)&M2:M)})

edited Jun 7, 2021 at 16:17

answered Jun 7, 2021 at 15:53

Aresvik

4,6181 gold badge8 silver badges18 bronze badges

2 Comments

Kuru KumBa Over a year ago

Thanks Im Not an expert.. This did the job verywell

Aresvik Over a year ago

It's probably useful to add trim as per the update.

Collectives™ on Stack Overflow

Using ARRAYFORMULA with Multiple cell values in to one

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related