Moving data to column with a date

dravg

New Member
Joined
Aug 7, 2018
Messages
2
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
dravg,

Welcome to the Board.

You might consider the following...

Code:
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
 
Upvote 0
Thank you so much Tonyyy!

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

Forum statistics

Threads
1,224,903
Messages
6,181,655
Members
453,059
Latest member
jkevin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top