Search code examples
google-sheetsfiltergoogle-sheets-formulatransposetextjoin

How to split a numbered column into 7 equal parts vertically and sort by date in google sheets?

I have asked a very similar question before, but have been unable to reverse engineer a solution for my current problem 1.

I have a column of values stacked in the order I would like, but I am trying to get them to be divided by 7 and have each value take up 7 cells. I created the first few lines of a desired output column that shows what the goal is2.

I also tried to have a sort function in there to only show values <= today(), but was unable to make it work.

Any and all help would be greatly appreciated.

Solution

  • try:

    =FLATTEN(TRANSPOSE(FILTER(Sheet1!B2:L/7, Sheet1!A2:A<>"", Sheet1!A2:A<=TODAY())))

    enter image description here

    update:

    =ARRAYFORMULA(FLATTEN(TEXT(FLATTEN(TRANSPOSE(
 FILTER(Sheet1!B2:L/7, Sheet1!A2:A<>"", Sheet1!A2:A<=TODAY()))), 
 {"@", "@", "@", "@", "@", "@", "@"})))