Macro To Look At Column C, Count Matching Cells and Put In Column D

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,403
Office Version
  1. 2016
Platform
  1. Windows
I have workbooks of 100 of thousands of rows and I need a code please that will count the number of matching cells in column D and put a running count in column D please.

Please note it has to be a code rather than a formula. Thanks

Before

Excel 2010
CD
978AUA520D 1030
979AUA520D 1030
980AUA520D 1030
981AUA520D 1035
982AUA520D 1035
983AUA520D 1035
984AUA520D 1035
985AUA520D 1035
986AUA520D 1035

<tbody>
</tbody>
Sheet1



After

Excel 2010
CD
978AUA520D 10301
979AUA520D 10302
980AUA520D 10303
981AUA520D 10351
982AUA520D 10352
983AUA520D 10353
984AUA520D 10354
985AUA520D 10355
986AUA520D 10356

<tbody>
</tbody>
Sheet1
 
Last edited:

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
Try this:

Code:
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1092265a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1092265-macro-look-column-c-count-matching-cells-put-column-d.html[/COLOR][/I]
[I][COLOR=seagreen]'' section group looping[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va
va = Range([COLOR=brown]"C1"[/COLOR], Cells(Rows.count, [COLOR=brown]"C"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
    j = i
    [COLOR=Royalblue]Do[/COLOR]
        i = i + [COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]If[/COLOR] i > UBound(va, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Do[/COLOR]
    [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = va(i - [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR])
    
    i = i - [COLOR=crimson]1[/COLOR]:    n = [COLOR=crimson]0[/COLOR]
        [COLOR=Royalblue]For[/COLOR] k = j [COLOR=Royalblue]To[/COLOR] i
            n = n + [COLOR=crimson]1[/COLOR]:  va(k, [COLOR=crimson]1[/COLOR]) = n
        [COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"D1"[/COLOR]).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = va

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,808
Messages
5,524,995
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top