How to count matches in two columns and reset counter?

Question

Here is my data before:

A   Ron
A   Ron
B   Jeremy
C   Jeremy
C   Teddy
C   Teddy
C   Teddy
D   George
D   George

This is what I want to see after:

A   Ron 2
B   Jeremy  1
C   Jeremy  1
C   Teddy   3
D   George  2

Here is my non-working script:

Sub Macro()
i = 1
Dim lngRow As Long
For lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) = Range("A" & lngRow - 1) And _
    Range("B" & lngRow) = Range("B" & lngRow - 1) Then
        i = i + 1
        Range("C" & lngRow - 1).Value = i
    Rows(lngRow).Delete
End If
Next
i = 1
End Sub

Something is off with the counter, but I can't tell what it is.

Would you be interested in non-VBA solutions? – CallumDA Feb 16 '17 at 00:49 — CallumDA, Feb 16 '17 at 00:49

score 1 · Answer 1 · answered Feb 16 '17 at 01:40

Your (second) i = 1 is in the wrong place - you need to reset i every time there is a change in the "key":

Sub Macro()
    i = 1
    Dim lngRow As Long
    For lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If Range("A" & lngRow) = Range("A" & lngRow - 1) And _
           Range("B" & lngRow) = Range("B" & lngRow - 1) Then
            i = i + 1
            Range("C" & lngRow - 1).Value = i
            Rows(lngRow).Delete
        Else
            i = 1
            'Also need to set the previous "total" here, just in case
            'it is the only record for this "key"
            Range("C" & lngRow - 1).Value = i
        End If
    Next
End Sub

score 1 · Answer 2 · edited May 23 '17 at 11:45

1

How about some SQL:

SELECT F1, F2, COUNT(*)
FROM [SheetName$]
GROUP BY F1, F2

Connect to the Excel worksheet using ADO, and paste the results into a new sheet using CopyFromRecordset.

A similar example here, and some references to ADO can be found here.

edited May 23 '17 at 11:45

Community

1
1

answered Feb 16 '17 at 02:05

Zev Spitz

13,950
6
64
136

That did it. I knew it was something like that, but I got started down the wrong path, and couldn't recover all by myself. Thanks!! – ASH Feb 16 '17 at 02:22

score 1 · Answer 3 · answered Feb 16 '17 at 12:18

you could use Dictionary object:

Sub Main()
    Dim cell As Range, dataRng As Range

    Set dataRng = Range("A1").CurrentRegion
    With CreateObject("Scripting.Dictionary")
        For Each cell In dataRng.Columns(1).Cells
            .Item(Join(Application.Transpose(Application.Transpose(cell.Resize(, 2).Value)), "|")) = .Item(Join(Application.Transpose(Application.Transpose(cell.Resize(, 2).Value)), "|")) + 1
        Next cell
        dataRng.ClearContents
        dataRng.Columns(1).Resize(.Count).Value = Application.Transpose(.keys)
        dataRng.Columns(1).Resize(.Count).TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:="|"
        dataRng.Columns(3).Resize(.Count).Value = Application.Transpose(.Items)
    End With
End Sub

Wow! Very cool! I never knew you could do that with VBA! Thanks for sharing!!! — ASH, Feb 16 '17 at 19:28
Well, I think I'll run the one from YowE3K, just because I understand the logic better. I really appreciate your effort though!! It's definitely interesting to see how completely different methodologies can get the exact same results!! — ASH, Feb 16 '17 at 23:47

How to count matches in two columns and reset counter?

3 Answers3