Sep 09 2021 11:17 AM - edited Sep 09 2021 11:17 AM
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?
Sep 09 2021 11:54 AM
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.
Sep 09 2021 12:00 PM
Sep 09 2021 12:10 PM
@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
Sep 09 2021 12:59 PM
Sep 09 2021 01:36 PM
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]
Sep 09 2021 02:00 PM
Sep 09 2021 03:08 PM
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.