google-sheetsgoogle-sheets-formula

Pickup n columns following a pattern

I try to find a nice way to select columns following a specific pattern.. the columns I want appear in my example every 3 columns (Col2, Col5, Col8)

Start Data

kwh Price Price per kwh kwh Price Price per kwh kwh Price Price per kwh
2022 2022 2022 2023 2023 2023 2024 2024 2024
Jan 1570 236,9 0,16 1199 204,3 0,18 1689 389 0,24
Feb 1590 241,2 0,16 1465 284,1 0,2 1151 281,4 0,25
Mar 873 131,8 0,16 928 187,4 0,21 967 242 0,26
Apr 771 117,2 0,16 903 179,5 0,2 639 158,8 0,25
May 290 42,1 0,15 476 91,5 0,2 586 135,4 0,24
Jun 229 32,7 0,15 279 51 0,19 282 71,9 0,26
Jul 210 29,9 0,15 256 47,4 0,19 277 65 0,24
Aug 159 21 0,14 198 40,8 0,21 230 54,9 0,24
Sept 209 29,9 0,15 260 55,5 0,22 263 62,6 0,24
Oct 342 51 0,15 259 55,3 0,22 401 96,8 0,25
Nov 567 86,8 0,16 811 180,7 0,23 683 204,7 0,3
Dec 1632 267,5 0,17 1541 356 0,24 1051 261,7 0,25

Expected Data

2022 2023 2024
Jan 1570 1199 1689
Feb 1590 1465 1151
Mar 873 928 967
Apr 771 903 639
May 290 476 586
Jun 229 279 282
Jul 210 256 277
Aug 159 198 230
Sept 209 260 263
Oct 342 259 401
Nov 567 811 683
Dec 1632 1541 1051

I can make it easy and to a simple manual VSTACK ={vstack(A2:A14)\vstack(B2:B14)\vstack(E2:E14)\vstack(H2:H14)}) or CHOOSECOLS or a QUERY assigning each columns I need.

I am looking for a solution where the n pattern is more dynamic, I explored a solution using SEQUENCE and CHOOSECOLS, this solution =LET(table;A2:J14;interval;3;{CHOOSECOLS(table;1)\CHOOSECOLS(table;SEQUENCE(1;(COLUMNS(table)-1)/interval;2;interval))}) is great but I am sure there can be other ways out of my reach like using INDIRECT, ADDRESS...storing values in accumulators and stuff like that.

Solution

  • You may try:

    =filter(B5:K17;(B4:K4="")+(B4:K4="kwh"))

    OR

    =choosecols(B5:K17;1;sequence(3;1;2;3))

    enter image description here