1

I'm new to google sheet. I have a column(E) with the date and another with a session(F), I want to merge them into one column with each date & different session just like the first few rows in column C.

enter image description here

I've tried "=ArrayFormula(concat(D2:D,concat(" ",F2:F5)))" in column C but only got the first date.

player0
  • 124,011
  • 12
  • 67
  • 124
Teresa
  • 13
  • 2
  • I think you can find an answer here: https://stackoverflow.com/questions/33086881/merge-two-python-pandas-data-frames-of-different-length-but-keep-all-rows-in-out – Omar Nov 14 '22 at 06:02

4 Answers4

0

In your cell C1, try this formula:

=ArrayFormula(E1:E&" "&F1:F)

Ping
  • 891
  • 1
  • 2
  • 10
0

Well you can simply do concatenate cells like this:

CONCATENATE(E1, " ", F1)

to get what you want I think.

Marios Michailidis
  • 11
  • 2
0

What you're looking for is a cartesian product. I don't have a single formula that does the entire thing for you, but I can suggest a two-step approach.

Get the cartesian product with this formula:

=ARRAYFORMULA(SPLIT(FLATTEN(E2:E9 & "|" & TRANSPOSE(F2:F5)), "|"))

This gives a pair of each date against each time in two result columns. You can then concatenate each row of them in a final result column.

Karthik Sivasubramaniam
  • 573
  • 7
  • 11
0

use:

=INDEX(QUERY(FLATTEN(FILTER(E2:E, E2:E<>"")&" "&
           TRANSPOSE(FILTER(F2:F, F2:F<>""))), "where not Col1 starts with ' '", ))

see: https://stackoverflow.com/a/68775825/5632629

player0
  • 124,011
  • 12
  • 67
  • 124