Hi all, I've the following that I couldn't solve. Please try to help.
I've a list of raw data on the left (Columns A, B & C).
The desired results I need is on the right (Columns E, F, G, H).
I need a macro that enables me to group them in accordance to their Dates and No. and in column H to count the number of times the combination appears.
<tbody>
</tbody>
I've the following macro that allows me to ONLY groups and counts them in accordance to the dates. However, I need to include another criteria which is No.
<colgroup><col width="64" style="width: 48pt;"></colgroup><tbody>
</tbody>
Can anyone help to modify the macro such that it will include the No. and produce the results as in the table above?
Thanks in advance!!!!!
I've a list of raw data on the left (Columns A, B & C).
The desired results I need is on the right (Columns E, F, G, H).
I need a macro that enables me to group them in accordance to their Dates and No. and in column H to count the number of times the combination appears.
Names | Dates | No. | Desired results | |||||
Tom | 12/12/2011 | 1 | Name | Dates | No. | Count | ||
Tom | 12/12/2011 | 1 | Tom | 12/12/2011 | 1 | 2 | ||
Tom | 12/12/2011 | 2 | Tom | 12/12/2011 | 2 | 1 | ||
Tom | 12/12/2011 | 3 | Tom | 12/12/2011 | 3 | 1 | ||
serene | 12/12/2011 | 1 | Serene | 12/12/2011 | 1 | 2 | ||
serene | 12/12/2011 | 1 | Serene | 12/13/2011 | 1 | 2 | ||
serene | 12/13/2011 | 1 | Tom | 12/13/2011 | 2 | 2 | ||
serene | 12/13/2011 | 1 | Tom | 12/13/2011 | 1 | 1 | ||
Tom | 12/13/2011 | 2 | Gill | 12/13/2011 | 1 | 2 | ||
Tom | 12/13/2011 | 2 | ||||||
Tom | 12/13/2011 | 1 | ||||||
Gill | 12/13/2011 | 1 | ||||||
Gill | 12/13/2011 | 1 |
<tbody>
</tbody>
I've the following macro that allows me to ONLY groups and counts them in accordance to the dates. However, I need to include another criteria which is No.
Sub MG26Dec46 |
Dim Rng As Range |
Dim Dn As Range |
Dim Twn As String |
Dim Q |
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)) |
With CreateObject("scripting.dictionary") |
.CompareMode = vbTextCompare |
For Each Dn In Rng |
Twn = Dn & Dn.Offset(, 1) |
If Not .Exists(Twn) Then |
.Add Twn, Array(Dn, Dn.Offset(, 1), 1) |
Else |
Q = .Item(Twn) |
Q(2) = Q(2) + 1 |
.Item(Twn) = Q |
End If |
Next |
Range("E3").Resize(.Count, 3) = Application.Transpose(Application.Transpose(.Items)) |
End With |
End Sub |
<colgroup><col width="64" style="width: 48pt;"></colgroup><tbody>
</tbody>
Can anyone help to modify the macro such that it will include the No. and produce the results as in the table above?
Thanks in advance!!!!!