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

How to summarize date counts

Answers (1)

Related Questions