Shortcut in Transposing Rows to Column

**stormracela** · 03-05-2013, 08:53 AM

Good Morning, I just want to know if there is a short cut for the 'Transpose" function doing rows to column. I have 6000 rows that I need to transpose but I want to transpose 10 rows to 10 columns in row #1, then another 10 rows to 10 columns in row #2 etc.

ex.
rows 1-10 transpose to A1, A2, A3, A4, A5, A6, A7, A8, A9, A10
rows 11-20 transpose to B1, B2, B3, B4, B5, B6, B7, B8, B9, B10
rows 21-30 transpose to C1, C2, C3, C4, C5, C6, C7, C8, C9, C10

I attached a sample file for example.

Thanks in advance!

**outofthehat** · 03-05-2013, 01:04 PM

Try the attached

Sheet 1 has your original data. I named column A sourcedata to make life easier.

Sheet 2 has a formula in cell A1 which I copied across and down.
=INDEX(sourcedata,ROW()*10-(10-COLUMN()))
Once you have this, you can copy > paste special > values if you want to get rid of the formulae.

**stormracela** · 03-05-2013, 10:39 PM

Wow, this is so great! It took me 1 hour to do a hundred and I just figured to ask from your help after doing 500

But anyway, thank you so much outofthehat! You made life easier for me! Mwah!

**MyOpSys** · 03-07-2013, 02:46 PM

Great Solution and simple! I found your solution after browsing a number of macros to do something similar and joined the forum. This is by far the simpler solution, easy to adjust for however many groups of rows you are working with and results immediately showed where my data needed cleanup also. Thanks Out of the Hat!

**bhavin_1981** · 03-22-2013, 11:51 AM

Hi,

I am looking for a formula to convert multiple columns into 1 column, I want the formula to start from cell C2

A B C D E F
Date Draw No.Numbers Numbers Numbers Numbers Numbers
20/02/2013 84 81 66 23 56 17
13/02/2013 83 29 37 51 89 61
06/02/2013 82 48 62 31 6 34
30/01/2013 81 13 23 4 61 83
23/01/2013 80 71 54 19 72 37

Please help....