-1

The data in my original spreadsheet is listed horizontally. For instance:

A B C D E

F G H G I

J K L M N

O P Q R S

I would like to arrange this table in a vertical way. like this below:

A

B

C

D

E

F

G
byte me
  • 770
  • 6
  • 13
Sumit Kasana
  • 3
  • 2
  • Welcome to SO. Please know this is not a code writing service, so please read the following: [How to Ask](https://stackoverflow.com/help/how-to-ask) and [How to Create a Minimal Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). Provide code that you have tried and if/when you run into issues/errors post them too. – Zack E Jan 10 '20 at 17:11
  • Look at the code in [this answer](https://stackoverflow.com/a/13176360/4717755) to help get you started. – PeterT Jan 10 '20 at 17:12
  • Is your table jagged, or do all rows have the same number of columns with values? – BigBen Jan 10 '20 at 17:18
  • Hi, All rows have the same no. of columns with values. – Sumit Kasana Jan 10 '20 at 17:25

1 Answers1

2

This is doable with INDEX and some math:

=INDEX($A$1:$E$4,ROUNDUP(ROW(1:1)/COLUMNS($A$1:$E4),0),MOD(ROW(1:1)-1,COLUMNS($A$1:$E4))+1)

Explanation:

  • ROUNDUP(ROW(1:1)/COLUMNS($A$1:$E4),0): creates the repeating sequence: 1,1,1,1,1,2,2,2,2,2... when dragged down.

  • MOD(ROW(1:1)-1,COLUMNS($A$1:$E4))+1: creates the repeating sequence: 1,2,3,4,5,1,2,3,4,5... when dragged down.

This question also explains the creation of those repeating sequences.

enter image description here

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • Great function! It would be useful to include a breakdown of the formula in the answer to make it more obvious how this is working though. As an aside you could replace the instances of 5 with COLUMNS($A$1:$E$4) which could also lead to easier implementation of dynamic resizing of a named range for more advanced calculations as well. – Tragamor Jan 10 '20 at 17:53
  • @Tragamor yeah I thought about using COLUMNS, that's a good idea. – BigBen Jan 10 '20 at 17:55
  • 1
    @BigBen Thank you very much, I am new to the community and i will make sure to follow the proper asking rules from next time. As for this one, you saved me. Thanks a lot. – Sumit Kasana Jan 11 '20 at 16:59