0

I have 100 rows and 100 columns 100x100 of data. I need to transfer all the data to 10000 rows and 1 column 10000X1. This means all the data will be in a single column eg: A1:A10000

This is done on a microsoft excel sheet

Can someone teach me how to do it thanks

Community
  • 1
  • 1
Computernerd
  • 7,378
  • 18
  • 66
  • 95
  • Check this out as you merely want to [convert multiple columns into rows with one or two columns..:](http://stackoverflow.com/questions/14533030/i-need-a-excel-vba-code-to-convert-a-multiple-column-table-into-a-single-column/14537355#14537355). However it's best if you could show us some sample data. – bonCodigo Jan 27 '13 at 16:11

1 Answers1

2

This EXCEL formula works well:

=OFFSET($A$1,MOD(ROW()-ROW($F$1),ROWS($A$1:$A$5)),TRUNC((ROW()-ROW($F$1))/ROWS($A$1:$A$5)),1,1)

Taken from this response: http://www.mrexcel.com/forum/excel-questions/444343-multiple-columns-into-one-column.html

Note there are a ton of ways to do this in VB if you are looking for that. Also you will need to adjust your ranges to 100x100 (currently 4x5).

Matthew
  • 9,851
  • 4
  • 46
  • 77
  • 1
    If you use in `INDEX($A$1:$CV$100,` instead of `OFFSET($A$1,` the calculation should be significantly faster but lead to the same result! – Peter Albert Jan 27 '13 at 21:45