• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Transpose from 4 Columns to 12 Columns (from 12000 rows to 4000 rows)

I

inbp

Member
Hi All,


I am in trouble at office, i am doing a very boring & repeated task on daily basis,


can you people give me a VBA or any formula to make me awesome at office.


I have data in 4 columns and 12000 rows, i want to divide it in 12 columns & 4000 rows.


But problem is that it will be done on page wise.


Let Suppose i have data in


A2 to D120001


in my 1st page rows are A47.


i want to show data on first page as


Fristly Data is in (A2:D139)


now i want this


A2:D47(Firstly it is A2:D47)

F2:I47(Firstly it is A48:D93)

k2:N47(Firstly it is A94:D139)

A48:D93 (Firstly it is A140:D185) and so on


This is one page setting i want this for 260 and more pages.


you can say that data of 3 pages now in a page.


I have also attached the file. Link is below


https://www.dropbox.com/s/5tzsemebz5a4ywr/Data%20Transpose.xlsx


If any ambiguity i will elaborate again.


Regards,

Muhammad Shakeel
 
Asallam wallekum.. Shakeel Bhaijaan,


Can you please try the below code..

[pre]
Code: 
Sub DataTranspose()
Sheets.Add.Name = "Output"
Set dest = Sheets("Output")
Set Source = Sheets("Data")
j = 1
For i = 1 To Source.Range("A" & Rows.Count).End(xlUp).Row Step 46
Source.Cells(i + 1, 2).Resize(46, 4).Copy dest.Cells(1, j)
j = j + 4
Next i
Source.Range("B1:E1").Copy
With dest
.Range(.Cells(1, 1), .Cells(1, .Columns.End(xlToRight).Column)).Insert (xlShiftDown)
End With
Application.CutCopyMode = False
End Sub
[/pre]

For more detail.. Please download the below file..

https://dl.dropbox.com/u/78831150/Excel/Data%20Transpose%28Shakeel%29.xlsm


Regards,

Deb
 
Waalaikum Aslam! Dear Bahi Deb..


Thanks you have done almost.


i need one thing more..Please if you can..


I want this to do the pages from up to down pages not right to left


Please see the file


https://www.dropbox.com/s/5tzsemebz5a4ywr/Data%20Transpose.xlsx


i will be very greatful bhai if you do it.


Thanks & Regards

Muhammad Shakeel
 
Hi Shakeel ,


I think Deb is busy ; can you copy the code below , and paste it in your file , instead of the original code ? The revision is minor , but should take care of your requirement.

[pre]
Code: 
Sub DataTranspose()
Application.ScreenUpdating = False
Set dest = Sheets("My Requirement")
dest.Cells.Clear
Set Source = Sheets("Data")

k = 1
For i = 1 To Source.Range("A" & Rows.Count).End(xlUp).Row Step 46
Source.Cells(i + 1, 2).Resize(46, 4).Copy dest.Cells(k, j * 4 + 1)
j = j + 1
If j >= 3 Then
j = 0
k = k + 46
End If
Next

Source.Range("B1:E1").Copy
With dest
.Range(.Cells(1, 1), .Cells(1, .Columns.End(xlToRight).Column)).Insert (xlShiftDown)
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
[/pre]
Narayan
 
I was also working on a code. I will post it anyway.


Insert a sheet and name it as "Output".

and then test the code:

[pre]
Code: 
Public Sub DataArrange()
Dim i As Long, j As Long
'First clean up any old data that is on Output sheet
With Sheets("Output")
If .Range("A" & Rows.Count).End(xlUp).Row > 1 Then
.Range("A2:O" & .Range("A" & Rows.Count).End(xlUp).Row).Delete xlUp
End If
End With

'Copy and transpose data
With Sheets("Data")
j = 1
For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Step 46
Select Case j
Case 1
.Range("B" & i).Resize(46, 4).Copy _
Sheets("Output").Cells(Rows.Count, j).End(xlUp)(2)
j = 6
Case 6
.Range("B" & i).Resize(46, 4).Copy _
Sheets("Output").Cells(Rows.Count, j).End(xlUp)(2)
j = 11
Case 11
.Range("B" & i).Resize(46, 4).Copy _
Sheets("Output").Cells(Rows.Count, j).End(xlUp)(2)
j = 1
End Select
Next i
End With

End Sub
[/pre]
 
Hi Shakeel..


As I can see.. Narayan & Shri already did the job..

Sorry for not able to check after posting.. :(


BTW.. If you are doing the same.. just only for printing in some HARDCOPY.. then why not copy from Excel > Paste in Word > and convert to Column 3/4..

and if you are doing this just for data arrangement in SOFTCOPY.. then single column, is more dependable and robust..


@ Narayn & Shri..

Thanks a lot..


Regards,

Deb
 
You must log in or register to reply here.
Back
Top