Forum Discussion

Copper Contributor

Sep 09, 2021

Take a range and space it out into columns

I am trying to grab all the values in a range (C2:C224 is the current one, but it will sometimes be a smaller or larger range in the same column) from one sheet and move those to another sheet but I ...

excel

PeterBartholomew1

Silver Contributor

Sep 09, 2021

JennaSmith495

If your horizontal array of headers contains only blanks between adjacent labels you could use an Excel 365 formula

= LET(
  k,   SEQUENCE(1, 4*ROWS(Labels),4),
  hdr, INDEX(Labels, QUOTIENT(k,4)),
  IF(MOD(k,4)=0, hdr, ""))

This is also possible in older versions of Excel by using column numbers but is more obscure.