PDA

View Full Version : Solved: Converting 3 Rows to 8 columns



Anomandaris
05-21-2009, 07:28 AM
Hi guys,

I'm trying to convert a set of data on 3 rows, 64 columns
into an 8 by 8 grid with each grid having 3 rows...

I've attached a workbk, Sheet2 has the data from where it needs to be rearranged and copied to Sheet 1 in grid format. You can see in Sheet 1 what its meant to look like

I've numbered it 1-8, but i dont want those showing up beside the grid, its just to make it easier for you to see whats going on

I've looked at other rows to columns codes on the site but cant figure out one that will suit my conditions.


any thoughts?
thanks

Paul_Hossler
05-21-2009, 08:04 AM
Something like this? You can replace the hard coded number if you want more flexibility


Option Explicit
Sub x()
Dim iCol As Long
Application.ScreenUpdating = False
For iCol = 0 To 7
Call Worksheets("Sheet2").Cells(6, 8 * iCol + 3).Resize(3, 8).Copy( _
Worksheets("Sheet1").Cells(3 * iCol + 5, 3).Resize(3, 8))
Next iCol
Application.ScreenUpdating = True
End Sub



Paul

Anomandaris
05-22-2009, 03:35 AM
Hey thanks Paul,

but this one doesnt do exactly what i want... It takes each 8 column segment from Sheet2 and copies it into sets of rows,
I need to make it copy into columns.

So basically the 1st 8 segments would go into say column B on sheet1, then the 2nd 8 into column C, then column D and so on.

What your one does is place the 1st 8 segments into say Row 3, then the 2nd 8 segments into Row 4, then Row 5 and so on.

i'm trying to change, hopefully i can adjust it..........thanks for the code though, i'm almost there

Bob Phillips
05-22-2009, 03:57 AM
Try this



Dim iCol As Long
Dim iOff As Long

'Application.ScreenUpdating = False

With Worksheets("Sheet1")

.Cells.ClearContents

For iCol = 0 To 7

For iOff = 0 To 7

Worksheets("Sheet2").Cells(6, 8 * iCol + iOff + 3).Resize(3).Copy _
Worksheets("Sheet1").Cells(6 + iOff * 3, iCol + 3).Resize(3)
Next iOff

.Cells(6 + iCol * 3 + 1, "B").Value = _
Worksheets("Sheet2").Cells(5, iCol + 4).Value
Next iCol
End With

Application.ScreenUpdating = True

Anomandaris
05-22-2009, 08:47 AM
thanks xld, it works great!

macropod
05-22-2009, 04:36 PM
Hi Anomandaris,

You could do this without vba, using the following formula:
=OFFSET(Sheet2!$C$6,MOD(ROW()-5,3),(COLUMN()-3)*8+INT((ROW()-5)/3))

Bob Phillips
05-23-2009, 02:46 AM
If you are going to use a formula, better to use a less inefficient one than that nasty OFFSET

=INDEX(Sheet2!$C$6:$BN$8,MOD(ROW()-5,3)+1,(COLUMN()-3)*8+INT((ROW()-5)/3)+1)