Take a range and space it out into columns

Copper Contributor

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?

7 Replies

@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.

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

@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

 

This first one I am working on is the site code but there are many cells where I'm looking to do the same thing for the sample date and same ID, etc. The point of this report is for it to be readable to the average client that might not have a ton of scientific knowledge. I'm not sure I have much wiggle room to change the format our scientist wants.

@JennaSmith495 

 

My experience with arrays of tables and an array of clients is that people respond to clarity, whether it's in rows, columns or separate pages. Even scientists appreciate clarity, and some are capable of changing previous convictions. [insert smiley face here]

I have a clear report example and am trying to automate. Thank you for the suggestions, but I am going to continue to search for how to do this.

@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.