data manipulation

[joserdsousa]

hello there guys


I'm having trouble passing this data from this format:
"


Day
| 00:15 | 00:30 | 00:45 ... | 23:45
01/jan | 32 | 30 | 40 ... | 45
02/jan | 45 | 50 | 52 ... | 60




"




to this format:


"


01/jan | 00:15 | 32
01/jan | 00:30 | 30
01/jan | 00:45 | 40
.
.
.


01/jan | 23:45 | 45
02/jan | 00:15 | 45
02/jan | 00:30 | 50
02/jan | 00:45 | 52
.
.
.
02/jan | 23:45 | 60




"


I'm doing a transposition of the data, but that is
taking too much of my time. I have almoust 1 million of cell values
to organize in that way...




if anyone could help me I would be much gratefull!

 

[offthelip]

you haven't said where you want the results so I have written it out to a new tab " temp"

Sub test2()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
' I assume there are 96 colums (24*4)
inarr = Range(Cells(1, 1), Cells(lastrow, 96))
Dim outarr()
ReDim outarr(1 To 96 * lastrow, 1 To 3)


indi = 2
For i = 2 To lastrow
  For j = 2 To 96
   outarr(indi, 1) = inarr(i, 1)
   outarr(indi, 2) = inarr(1, j)
   outarr(indi, 3) = inarr(i, j)
   indi = indi + 1
  Next j
Next i


    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Temp"
    End With
ActiveWorkbook.Worksheets("temp").Select
Range(Cells(1, 1), Cells(96 * lastrow, 3)) = outarr


End Sub

 

[joserdsousa]

Thank you.

I'll try this sollution and if it works I'll tell you.

Many thanks.