Forum Discussion

JennaSmith495's avatar
JennaSmith495
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 want them in the same row spaced out to every 4th column, leave the cells in between blank. Is there a formula for this?

excel

7 Replies

  • PeterBartholomew1's avatar
    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.

  • mathetes's avatar
    mathetes
    Gold Contributor
    Sep 09, 2021

    JennaSmith495 

     

    No specific formula comes to my mind to do the spacing. The TRANSPOSE function can take the range and render it in columns rather than rows adjacent to the original or in another sheet.

    =TRANSPOSE(C2:C224)

    BUT, may I ask you why? Will you be filling in the blank columns once they're there? 

     

    I'm wondering if there might be a different way to accomplish whatever your long term goal is.

     

    Most of us who use Excel a lot are resistant to designs that involve a lot of blank rows or columns....it's one thing to space things out for a printed report, but another to try to work with data that has lots of spaces.....

    So that's why I ask what otherwise might sound like none of my business.

    • JennaSmith495's avatar
      JennaSmith495
      Copper Contributor
      Sep 09, 2021
      It is for a report that displays scientific data. The spacing is to have a section for each sample.
      • mathetes's avatar
        mathetes
        Gold Contributor
        Sep 09, 2021

        JennaSmith495 wrote: It is for a report that displays scientific data. The spacing is to have a section for each sample.

         

        Help me understand: are the current cells (C2:C224) labels for the samples, dates, what?

         

        In general, Excel tables work better when each ROW is a complete record unto itself. Most of the Excel functions that summarize, tabulate, etc., are perfectly fitted for series of records arrayed like that, in rows. 

        https://exceljet.net/excel-tables

         

Resources