VBA to Divide Rows and Transfer to Multiple Columns

[inbp]

Hi Excel Developers!


I need your help.


I have a data in "Data" Sheet with 3 columns (A,B,C) and 1500 rows. I 3 columns remains always same but rows changes (Range from 100 to 5,000)


I want to divide rows number as total rows number divided by 5


1500/5=300


Now i want


300 rows data in (A,B,C) Column

300 rows data in (D,E,F) Column

300 rows data in (G,H,I) Column

300 rows data in (J,K,l) Column

300 rows data in (M,N,O) Column


You can see my file as well with data and required sheet.


https://www.dropbox.com/s/vug8k0bgj1rlqo4/Data%20Transpose%20with%20Division.xlsx


Rows number are dynamic so the result after divide by 5 will distribute in 15 columns form 3 columns.


Thanks & Regards

Muhammad Shakeel

 

[inbp]

is there no solution?

 

[Hui]

Muhammad


Setup two Named Formula

Data: =Data!$A$1:$C$1500

Rows: 5

Then on a new worksheet in Cell A1: put

=IF(OR(ROW()>(ROWS(Data)/Rows),COLUMN()>Rows*3),"",INDEX(Data,(INT((COLUMN()-1)/3))*(ROWS(Data)/Rows)+ROW(),IF(MOD(COLUMN(),3),MOD(COLUMN(),3),3)))

Copy across / down to suit

 

[inbp]

HUI


Thanks its working awesome