1

OK, I will try to explain this well. An odd problem to solve, and it's WAY beyond my skill level (novice). Excel 2011 for Mac.

The workbook has 11 sheets. Sheet names are all 'Month Year'. (Eg: sheet 1 is titled "June 2013") Sheet names are reverse chronological. (June 2013, May 2013, April 2013 etc) Each sheet has data in the same layout: A1 is the sheet's name. B1 through to a varying endpoint on B hold dates. (approx two weeks but varies greatly between sheets) A2 and downward in A is all names, as "last,first". The remaining columns below B1 and outward are either blank, 0's, or 1's (attendance for date at row 1).

What I need to do: Get ALL of the data in one sheet, with dates in chronological order along row 1 and names in alphabetical order down column A. Without messing up the associations between the names and the 0/1/blank values that existed on the original sheet.

What I have done: I did this manually using the Excel GUI on a very similar sheet. It took forever! I also have been writing or sourcing Subs to do some of the other work needed to these sheets to get them ready for this big rearranging. But I am already at my limits writing super simple "find rows with the word 'total' in them" sorts of stuff.

I know WHAT to do here, but have no clue HOW.

Start with the oldest sheet, copy into a new sheet(YearSheet). Take names from 2ndOldest, paste into A under names already there. Take dates and the cells beneath them into YearSheet, but staggered out on the columns so they begin where the first sheet left off. Repeat again with nextYoungest, same deal. Names under names, dates and data shoved out along the letter axis to prevent overlap with prior dates. Eventually it's a long list of names in A, and a descending step-pattern of data blocks in the remainder. Sort it all by A, alphabetically. Find and compress identical names into one row, without losing the data along the way (Why does Excel only keep top left? Aaargh!)

So, I know that's a lot to ask here. Have no idea if this is too much or over the top for a question, but I am just stumped so am posting it in hopes somebody can make sense of the VBA to do it.

ZygD
  • 22,092
  • 39
  • 79
  • 102
user2601892
  • 23
  • 2
  • 7
  • Are the names on every sheet in the workbook the same? – Ripster Jul 29 '13 at 13:29
  • You could unpivot the data from each sheet into one big list - see [this SuperUser answer](http://superuser.com/a/583083/5308) for some sample code. Once you have done that, a normal pivot table could be used to summarise all of the data – barrowc Jul 30 '13 at 06:13
  • @Ripster - Sheet names are all different - the month names AND the years change. Crap, thought I was clear in the above. Sorry. @ barrowc - Data not in a pivot table yet, so can't unpivot it. Also, that code from the link is beyond my comprehension without good comments in it explaining stuff. Sorry, utter newbie and can't read code with new objects/methods/etc without someone telling me whatthey do/are. – user2601892 Jul 30 '13 at 08:39

1 Answers1

1

I created a workbook based on your description to use as sample data.

enter image description here

I wrote this macro

Sub Main()
    Dim CombinedData As Variant
    Dim TotalCols As Integer
    Dim TotalRows As Long
    Dim PasteCol As Integer
    Dim PasteRow As Long
    Dim i As Integer
    Dim PivSheet As Worksheet


    ThisWorkbook.Sheets.Add Sheet1
    On Error GoTo SheetExists
    ActiveSheet.Name = "Combined"
    On Error GoTo 0
    Range("A1").Value = "Name"

    For i = ThisWorkbook.Sheets.Count To 1 Step -1
        If Sheets(i).Name <> "Combined" Then

            Sheets(i).Select
            TotalCols = Sheets(i).Columns(Columns.Count).End(xlToLeft).Column
            TotalRows = Sheets(i).Rows(Rows.Count).End(xlUp).Row
            PasteCol = PasteCol + TotalCols - 1
            If PasteRow = 0 Then
                PasteRow = 2
            Else
                PasteRow = PasteRow + TotalRows - 1
            End If

            'Copy Date Headers
            Range(Cells(1, 2), Cells(1, TotalCols)).Copy Destination:=Sheets("Combined").Cells(1, PasteCol)

            'Copy Names
            Range(Cells(2, 1), Cells(TotalRows, 1)).Copy Destination:=Sheets("Combined").Cells(PasteRow, 1)

            'Copy Data
            Range(Cells(2, 2), Cells(TotalRows, TotalCols)).Copy Destination:=Sheets("Combined").Cells(PasteRow, PasteCol)
        End If
    Next

    Sheets("Combined").Select
    ActiveSheet.Columns.AutoFit
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A1"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
        .SetRange ActiveSheet.UsedRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    Set PivSheet = Sheets.Add
    ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                    SourceData:=Sheets("Combined").UsedRange, _
                                    Version:=xlPivotTableVersion14).CreatePivotTable _
                                    TableDestination:=PivSheet.Range("A1"), _
                                    TableName:="PivotTable1", _
                                    DefaultVersion:=xlPivotTableVersion14


    For i = 1 To PivSheet.PivotTables("PivotTable1").PivotFields.Count
        With ActiveSheet.PivotTables("PivotTable1")
            If i = 1 Then
                .PivotFields(i).Orientation = xlRowField
                .PivotFields(i).Position = 1
            Else

                ActiveSheet.PivotTables("PivotTable1").AddDataField .PivotFields(i), _
                                                                    "Sum of " & .PivotFields(i).Name, _
                                                                    xlSum
            End If
        End With
    Next

    Application.DisplayAlerts = False
    Sheets("Combined").Delete
    Application.DisplayAlerts = True

    PivSheet.Name = "Combined"
    CombinedData = ActiveSheet.UsedRange
    Cells.Delete
    Range(Cells(1, 1), Cells(UBound(CombinedData), UBound(CombinedData, 2))).Value = CombinedData
    Range("A1").Value = "Name"
    Range(Cells(1, 1), Cells(1, UBound(CombinedData, 2))).Replace "Sum of ", ""
    Columns.AutoFit
    Exit Sub

SheetExists:
    Application.DisplayAlerts = False
    Sheets("Combined").Delete
    Application.DisplayAlerts = True
    Resume
End Sub

Which produces this result: enter image description here

This was written in Excel 2010 in windows. I don't know what the differences are between the pc and mac versions but this may work for you.

Ripster
  • 3,545
  • 2
  • 19
  • 28
  • Holy COW, Ripster! I am still getting all the sheets into the starting format (as you have it- correctly). Will try this as soon as sheets are cleaned up for it and be back to accept/ask more. Wow, thats too cool you wrote all that! – user2601892 Jul 30 '13 at 23:10
  • Bummer, it errors out. ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:=Sheets("Combined").UsedRange, _ Version:=xlPivotTableVersion14).CreatePivotTable _ TableDestination:=PivSheet.Range("A1"), _ TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion14 Says Run-time error '5' Invalid procedure call or argument. I'm on Excel 2011 for Mac. Think it's the version number? – user2601892 Jul 31 '13 at 01:53
  • Whoops, and it appears to empty the sheet of all data as it processes it, leaving a default title and no data where there used to be a real sheet of stuff. (Before it gets through to that error.) – user2601892 Jul 31 '13 at 02:00
  • It could be an issue with the version like you said. You could try changing the version and see if that helps. If you record a macro to create a pivot table and show me the code it produces I should be able to figure out what is going on and update it for you. – Ripster Jul 31 '13 at 02:32
  • Sorry for the delay in responding. Sleep, work... Where will i find the resulting code from the wizard build of the pivot? I am new to pivot tables so have only used the wizard to make them. – user2601892 Jul 31 '13 at 23:30