Combine date and time cells, and move data from cells to match

ikith · Nov 18th 2013

I'm stuck, I have a spreadsheet with date spanning across the top and vendor as well as count data running down the side for each day.

What I need is to combine the date and time and move the data accordingly.

For example I have this data:
[ATTACH=CONFIG]57500[/ATTACH]

And I need to move the data like this:
[ATTACH=CONFIG]57501[/ATTACH]

These are just examples, the real sheet is about 1 year and on each day it runs from 09:00 to 04:00.

jindon · Nov 18th 2013

Re: Combine date and time cells, and move data from cells to match

Try

Code

Sub test()
    Dim a, i As Long, ii As Long, n As Long
    With Cells(1).CurrentRegion
        ReDim a(1 To .Rows.Count * .Columns.Count, 1 To 3)
        n = 1: a(n, 1) = "Date/Time": a(n, 2) = "Count": a(n, 3) = "Vendor"
        For ii = 3 To .Columns.Count
            For i = 2 To .Rows.Count
                n = n + 1
                a(n, 1) = .Cells(1, ii).Text & " " & .Cells(i, 2).Text
                a(n, 2) = .Cells(i, ii).Value
                a(n, 3) = .Cells(i, 1).Value
            Next
        Next
        .Offset(, .Columns.Count + 2).Resize(n, 3).Value = a
    End With
End Sub

Display More

ikith · Nov 18th 2013

Re: Combine date and time cells, and move data from cells to match

You sir, are amazing!

Combine date and time cells, and move data from cells to match

Participate now!

Share