How to summarize date counts

Question

I have 3 Worksheets. Following is an explanation of what I am trying to do. EM11 EM12 EM01

The steps below are meant for Sheet EM11 but I want to repeat them FOR Sheet EM12 and Sheet EM01

• Select Sheet EM11
• Copy J2:J65636 and copy G2: G65636
• Create a new worksheet called EM11-Count
• Paste Column J into cell A2 of new worksheet called EM11-Count

• Paste Column G into cell B2 of new worksheet.

• Worksheet called EM11-Count now has dates in Range B2:B65636.

• Copy and paste the first date you get into cell C1
• Copy and paste the second date you get into cell D1
• Copy and paste the third date you get into cell E1
• Copy and paste the fourth date you get into cell F1
• Copy and paste the fifth date you get into cell G1
• Copy and paste the sixth date you get into cell H1 (there will only be six or seven different dates)

Now the worksheet will look something like this

Col A  Col B       Col C      Col D      Col E       
                   04/1/2009  04/2/2009  04/3/2009
DPR1   04/1/2009      
DRS6   04/2/2009      
ERJ9   04/3/2009

Look at A2:B2. Now look at C1:H1 to find the date that matches the date you have in B2. Let’s say C1 has the matching date. Go to C2 and put the number 1.

Col A  Col B       Col C      Col D      Col E       
                   04/1/2009  04/2/2009  04/3/2009
DPR1   04/1/2009   1     
DRS6   04/2/2009      
ERJ9   04/3/2009

Next Look at A3:B3. Now look at C1:H1 to find the date that matches the date you have in B3. Let’s say D1 has the matching date. Go to D3 and put the number 1.

Col A  Col B       Col C      Col D      Col E       
                   04/1/2009  04/2/2009  04/3/2009
DPR1   04/1/2009   1     
DRS6   04/2/2009              1   
ERJ9   04/3/2009

Next Look at A4:B4. Now look at C1:H1 to find the date that matches the date you have in B4. Let’s say E1 has the matching date. Go to E3 and put the number 1.

Col A  Col B       Col C      Col D      Col E       
                   04/1/2009  04/2/2009  04/3/2009
DPR1   04/1/2009   1     
DRS6   04/2/2009              1   
ERJ9   04/3/2009                         1

Now repeat this pattern until every row in A:B has been covered. Going down every row in the columns A:B random duplicates will occur. For instance the contents in A1:B1 will be the same through A2:B7 or the contents in A14:B14 will be the same through A15:B20. When these duplicates occur I want to delete the duplicate rows but increase the tally/count of the original row by the number “1”. For example, after the macro is finished scanning A:B the worksheet might look like this

Col A  Col B       Col C      Col D      Col E       
                   04/1/2009  04/2/2009  04/3/2009
DPR1   04/1/2009   12    
DRS6   04/2/2009              23      
ERJ9   04/3/2009                         57

Here is the code I currently have. As I am sure you can see it needs a lot of work. Can you help me?

Sub NWorksheetArrange()
    Application.Goto Reference:="R2C10:R65000C10"
    Selection.Copy
    Sheets.Add
    ActiveSheet.Paste
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "EM11-COUNT"
    Sheets("EM11").Select
    Application.Goto Reference:="R2C7:R65000C7"
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("EM11-COUNT").Select
    Range("B1").Select
    ActiveSheet.Paste
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    Range("B2").Select
End Sub

Answer 1

That sounds an awful lot like a "Pivot Table" ... and Excel does those automatically.

Like this: http://screencast.com/t/gpLsU50q38

To make a macro that builds the Pivot Table, do "Macro Record" ... then build the Pivot Table ... then wrap your code around the recorded Pivot Table statement.

Ron

Answer 2

You’re exactly right. Can you help me understand what it means to wrap my code around a recorded pivot table statement? I have a recorded pivot table statement below, however I have found that it doesn’t tend to work as the data changes. Any thoughts?

Sub NWorksheetArrange ()
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "EM11!R1C1:R397C12").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Order", _
        ColumnFields:="Created on"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Material").Orientation = _
        xlDataField
    ActiveWorkbook.ShowPivotTableFieldList = True
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "EM11-count"
    Sheets("EM12").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "EM12!R1C1:R178C12").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable2", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Order", _
        ColumnFields:="Created on"
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Material").Orientation = _
        xlDataField
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "EM12-count"
    Sheets("EM01").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "EM01!R1C1:R50C12").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable3", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="Order", _
        ColumnFields:="Created on"
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Material").Orientation = _
        xlDataField
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "EM01-count"
    Sheets("Sheet1").Select
    Range("A1").Select
End Sub