Interlace data.

[serge]

Hi,
It's for a game that have 2 draws per day and I need to reorganize them into columns : Date, Price, Draw numbers, what formula should be use to interlace those 5 columns into 3 ?
Thank you.

 

[Fluff]

How about in H3 copied down

=INDEX($A$3:$A$40,CEILING(ROWS(H$3:H3)/2,1))

and in I3 copied down & across

=INDEX((B$3:B$40,E$3:E$40),CEILING(ROWS(I$3:I3)/2,1),1,MOD(ROWS(I$3:I3)-1,2)+1)

 

[kvsrinivasamurthy]

In H3 copied down.

=INDEX($A$3:$A$17,1+INT((ROWS($H$3:$H3)-1)/2))

In I3 copied down to columns I and J

=INDEX($B$3:$F$17,MATCH($H3,$A$3:$A$17,0),COLUMNS($I3:I3)+IF(MOD(ROWS($I$3:$I3)-1,2)=0,0,3))

 

[mumps]

VBA approach:

Sub InterlaceData()
    Application.ScreenUpdating = False
    Dim v As Variant, arr() As Variant, i As Long, x As Long
    v = Range("A3", Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value
    ReDim Preserve arr(1 To UBound(v) * 2, 1 To 3)
    For i = LBound(v) To UBound(v)
        If x = 0 Then x = x + 1 Else x = x + 2
        arr(x, 1) = v(i, 1)
        arr(x, 2) = v(i, 2)
        arr(x, 3) = v(i, 3)
        arr(x + 1, 1) = v(i, 1)
        arr(x + 1, 2) = v(i, 5)
        arr(x + 1, 3) = v(i, 6)
    Next i
    Range("H3").Resize(UBound(v) * 2, 3).Value = arr
    Application.ScreenUpdating = True
End Sub

 

[serge]

Wow, Thank you to all of you those formulas work Great, I really appreciate all of your help.
Thank you all.

 

[Fluff]

Glad we could help & thanks for the feedback.