Moving data to column with a date

[dravg]

I've used this site many times over the past year and have always found answers to my questions. This time, I am completely stumped so hopefully I can concisely explain what I am trying to accomplish and hope someone out there can help.

I have 7 workbooks in which each workbook has over 10 worksheets and each worksheet is a pivot table. Using all of this, I need to create a scorecard. I've created a macro which opens the workbooks, goes through each tab to reformat the pivot table for the data I need, and copies and paste that data into a newly created worksheet. Once all the data is collected, the entire dataset worksheet is copied into the scorecard workbook.

The issue I am having has to do with the number of columns and where the data is placed. Some pivot tables have 12 months and some only have 1 month. When the data is copied over, these months may not be aligned properly for me to properly perform a vlookup, hlookup, or an Index Match.

Below is an example of what the final data sheet looks like where each store is a different workbook and each fruit is a specific worksheet from that workbook:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Workbook Data[/TD]
[TD]Worksheet Data[/TD]
[TD]11/1/2017[/TD]
[TD]12/1/2017[/TD]
[TD]1/1/2018[/TD]
[/TR]
[TR]
[TD]Store 1[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store 1[/TD]
[TD]Apples Sold[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Store 1[/TD]
[TD]Apples Total[/TD]
[TD]10[/TD]
[TD]7[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]12/1/2017[/TD]
[TD]1/1/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store 1[/TD]
[TD]Bananas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store 1[/TD]
[TD]Bananas Sold[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Store 1[/TD]
[TD]Bananas Total[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]5/1/2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store 2[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store 2[/TD]
[TD]Apples Sold[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store 2[/TD]
[TD]Apples Total[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]4/1/2018[/TD]
[TD]5/1/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store 2[/TD]
[TD]Bananas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store 2[/TD]
[TD]Bananas Sold[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store 2[/TD]
[TD]Bananas Total[/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The scorecard has a worksheet with 12 months for each type of fruit in which it compares the # sold and total from each store. But what I need the macro to do now is align the dates and data in order so the vlookup or index match can correctly place the result for the given month.

Any ideas, thoughts, suggestions would be greatly appreciated! Please let me know if I can clarify some more. Thank you.

 

[tonyyy]

dravg,

Welcome to the Board.

You might consider the following...

Sub AlignDates_1066181()
Application.ScreenUpdating = False
Dim r As Range, found As Range, rng As Range
Dim startRow As Long, endRow As Long, i As Long, j As Long, k As Long, lastRow As Long, lastCol As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
startRow = 1
For i = 2 To lastRow
    If Cells(i, 1).Value = "" Then
        endRow = i - 1
        lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
        For j = lastCol To 3 Step -1
            Set found = ActiveSheet.Rows(1).Find(What:=Cells(startRow, j).Value, After:=Cells(1, 3), LookIn:=xlFormulas, _
                LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
            If Not found Is Nothing And startRow <> 1 Then
                Range(Cells(startRow, j), Cells(endRow, j)).Cut Destination:=Range(Cells(startRow, found.Column), Cells(endRow, found.Column))
            ElseIf found Is Nothing And startRow <> 1 Then
                k = Cells(1, Columns.Count).End(xlToLeft).Column + 1
                Cells(1, k) = Cells(startRow, j)
                Cells(1, k).NumberFormat = "m/d/yyyy"
                Range(Cells(startRow, j), Cells(endRow, j)).Cut Destination:=Range(Cells(startRow, k), Cells(endRow, k))
            End If
            Set found = Nothing
        Next j
        startRow = i
    End If
Next i

With ActiveSheet
    Set rng = .UsedRange.Offset(0, 2).Resize(.UsedRange.Rows.Count, .UsedRange.Columns.Count - 2)
End With
rng.Sort Key1:=rng, Order1:=xlAscending, Orientation:=xlLeftToRight
End Sub

Cheers,

tonyyy

 

[dravg]

Thank you so much Tonyyy!

I will give it a try on Monday and let you know if this does the trick.