Solved: Converting 3 Rows to 8 columns

[Anomandaris]

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]

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]

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]

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]

thanks xld, it works great!

[macropod]

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]

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)