Excel Vba Dynamic number of rows transpose to columns

Question

I am relatively new to vba and finding it difficult to write a code for this dynamic range. Input data has date from 11/07/14 to 11/14/14 Hours from 00:00 to 23:00 Hrs. I need to transpose the Hours in such a way that for each date i have value for each hour (in columns). Please let me know if i was not clear with the question. Thanks a million in advance

Input data

Date        Hours      Name          %Value
11/07/14    00:00    P4127C11       20   
11/07/14    01:00    P4127C11       30
   .                                
   .                          
11/07/14    23:00    P4127C11       24     
11/08/14    00:00    P4127C11       15    
    .   
    .    
11/11/14    00:00    P4127C11       25      
    .        .         .            .    
    .        .         .            .     
11/11/14    23:00    P4127C11       31

Output Data

Date       Name      00:00   01:00   02:00 . . .  .   23:00      
11/07/14  P4127C11    20       30      .      . ..  .  24    
11/08/14  P4127C11    15        .      .    .. . . .    .    
   .      
   .     
11/11/14  P4127C11    25        .     .    .          31

Thanks @Tmdean i was trying to format the data

Nadeem
– Nadeem

2014-11-09 19:34:18 +00:00
Commented Nov 9, 2014 at 19:34 — Nadeem
– Nadeem, Commented Nov 9, 2014 at 19:34

Tmdean · Accepted Answer · 2014-11-09 19:35:28Z

0

This is just a pivot table. You can solve it by creating a pivot table with date and name in the row values, time in the column values, and max of %value in the values.

answered Nov 9, 2014 at 19:35

Tmdean

9,31945 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nadeem Over a year ago

actually i wanted to do an excelvba code for this so i can manipulate the output further

Rafal Wegner · Accepted Answer · 2014-11-09 23:06:47Z

@Nadeem, one of probably many ways to solve it with vba is to load data into an array of defined type. Look at this code, it should provide some ideas you can use.

Private Type tPivotValues
    sDate As String
    sName As String
    aHour() As String
End Type

Sub q26832297()

Dim i, j As Long
Dim aPivotValues() As tPivotValues
Dim iArrIndex As Integer


    ReDim aPivotValues(0 To 1)

    iArrIndex = 0
    'reading first date of first element
    aPivotValues(iArrIndex).sDate = Sheets(1).Cells(2, 1).Value
    aPivotValues(iArrIndex).sName = Sheets(1).Cells(2, 3).Value
    j = 0
    ReDim aPivotValues(iArrIndex).aHour(0 To 23)

    For i = 2 To Sheets(1).Cells(1, 1).End(xlDown).Row
        'if date in row different than in array element
        If aPivotValues(iArrIndex).sDate <> Sheets(1).Cells(i, 1).Value Then
            iArrIndex = iArrIndex + 1
            aPivotValues(iArrIndex).sDate = Sheets(1).Cells(i, 1).Value
            aPivotValues(iArrIndex).sName = Sheets(1).Cells(i, 3).Value
            'setting hours counter to 0
            j = 0
            ReDim aPivotValues(iArrIndex).aHour(0 To 23)
        End If
        If aPivotValues(iArrIndex).sDate = Sheets(1).Cells(i, 1).Value Then
            aPivotValues(iArrIndex).aHour(j) = Sheets(1).Cells(i, 4).Value
            j = j + 1
        End If
    Next i

    'printing data to different sheet
    For i = LBound(aPivotValues) To UBound(aPivotValues)
        Sheets(2).Cells(i + 1, 1).Value = aPivotValues(i).sDate
        Sheets(2).Cells(i + 1, 2).Value = aPivotValues(i).sName
        For j = LBound(aPivotValues(i).aHour) To UBound(aPivotValues(i).aHour)
            Sheets(2).Cells(i + 1, j + 3).Value = aPivotValues(i).aHour(j)
        Next j
    Next i

End Sub

Collectives™ on Stack Overflow

Excel Vba Dynamic number of rows transpose to columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related