Forum Discussion
How to transpose and skip?
Hello everyone, thanks for being so helpful. I have columns where I need to transpose them into rows. I need to copy only every 6th column in the source, but not skip cells in the row destinations. I am using Excel 2022.
4 Replies
=INDEX($A$1:$AB$1,ROW($A$1)+(ROW(A1)-1)*6)
Maybe with this formula as shown in the attached file.
- first: what version of Excel are you using? excel 365?
second: I'm guessing you don't just want a column of letters: a, b, c, d, ... and that the real sheet isn't a,b,c,... but some other values, right? I will also assume by 'skipping' that the cells inbetween are blank (i.e. it is NOT "a" "blah" "blah" "blah" "blah" "blah" "blah" "b" ...)
so assuming the above then try:
=LET(IN, TRANSPOSE(A1:Z1),FILTER(IN,IN<>"",""))
if the second is not the case you use a more general solution like:
=LET(IN, TRANSPOSE(A1:Z1), multiple,7, FILTER(IN,MOD(SEQUENCE(ROWS(IN),,0),multiple)=0,""))- Thank you, I rewrote my original post.
