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.