VBA code that count last nth occurrences in data range

C

chefich

New Member
Joined
Sep 18, 2017
Messages
3
Hello All,


I need VBA code that counts last four occurrences in the data range.

Example: Part 4

[TABLE="class: grid, width: 408"]
<tbody>[TR]
[TD]Col 1[/TD]
[TD]Col 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 1[/TD]
[TD]R[/TD]
[TD="colspan: 4, align: center"]Result / Last 4 occurrences[/TD]
[/TR]
[TR]
[TD]Part 2[/TD]
[TD]D[/TD]
[TD][/TD]
[TD]R[/TD]
[TD]D[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]Part 3[/TD]
[TD]L[/TD]
[TD="align: center"]Part 4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Part 4[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 5[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 2[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 4[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 1[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 3[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 5[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 2[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 3[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 5[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 2[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 1[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 4[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 4[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 1[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 2[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 3[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 4[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 5[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 2[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 4[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 1[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 3[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 5[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part 2[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Please help!

Kind Regards
Stefan
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Welcome to the forum.

You can do this with a formula:

ABCDEF

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Col 1[/TD]
[TD="bgcolor: #FAFAFA"]Col 2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Part 1[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Result / Last 4 occurrences[/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]
[TD="bgcolor: #FAFAFA, align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Part 2[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA"]L[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Part 3[/TD]
[TD="bgcolor: #FAFAFA"]L[/TD]
[TD="bgcolor: #FAFAFA, align: center"]Part 4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Part 4[/TD]
[TD="bgcolor: #FAFAFA"]L[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]Part 5[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Part 2[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Part 4[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]Part 1[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]Part 3[/TD]
[TD="bgcolor: #FAFAFA"]L[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]Part 5[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]Part 2[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]Part 3[/TD]
[TD="bgcolor: #FAFAFA"]L[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA"]Part 5[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA"]Part 2[/TD]
[TD="bgcolor: #FAFAFA"]L[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA"]Part 1[/TD]
[TD="bgcolor: #FAFAFA"]L[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA"]Part 4[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA"]Part 4[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA"]Part 1[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA"]Part 2[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA"]Part 3[/TD]
[TD="bgcolor: #FAFAFA"]L[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FAFAFA"]Part 4[/TD]
[TD="bgcolor: #FAFAFA"]L[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FAFAFA"]Part 5[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FAFAFA"]Part 2[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FAFAFA"]Part 4[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FAFAFA"]Part 1[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FAFAFA"]Part 3[/TD]
[TD="bgcolor: #FAFAFA"]L[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #FAFAFA"]Part 5[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #FAFAFA"]Part 2[/TD]
[TD="bgcolor: #FAFAFA"]R[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]{=SUM(IF($A$2:$A$50=$C$4,IF($B$2:$B$50=D$3,IF(ROW($A$2:$A$50)>=LARGE(IF($A$2:$A$50=$C$4,ROW($A$2:$A$50)),4),1))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




Or do you have a specific reason to want VBA?
 
Last edited:
Upvote 0
Hello Eric,

Thank you for your answer.
I need VBA code cause it's part of my project and I am stuck on this step.

Best Regards
Stefan
 
Upvote 0
In Excel, the line between formulas and VBA is sometimes very blurry. For example, the following macro does what you ask:

Code: 
Sub GetLast4()


    [d4] = Evaluate("SUM(IF($A$2:$A$50=$C$4,IF($B$2:$B$50=D$3,IF(ROW($A$2:$A$50)>=LARGE(IF($A$2:$A$50=$C$4,ROW($A$2:$A$50)),4),1))))")
    [e4] = Evaluate("SUM(IF($A$2:$A$50=$C$4,IF($B$2:$B$50=E$3,IF(ROW($A$2:$A$50)>=LARGE(IF($A$2:$A$50=$C$4,ROW($A$2:$A$50)),4),1))))")
    [f4] = Evaluate("SUM(IF($A$2:$A$50=$C$4,IF($B$2:$B$50=F$3,IF(ROW($A$2:$A$50)>=LARGE(IF($A$2:$A$50=$C$4,ROW($A$2:$A$50)),4),1))))")
End Sub
but as you can see, it's just the same formulas in VBA.

When you say "part of my project", do you mean a school project? Generally speaking, we don't like doing people's homework. It robs the student of the opportunity to learn. We may give some pointers however. If this is a personal or work project, we're a bit more lenient.

In any case, if I were to write a more "program-like" macro, I'd start with this line:

Code: 
lr = Cells(Rows.Count, "A").End(xlUp).Row

which gets the last row of your data. Then I'd set up 4 counters, 1 for the part number, 3 for R, D and L. Then I'd create a loop going backwards:

Code: 
For r = lr to 2 Step -1

And check each cell in your column until you get your limit. If you have a basic knowledge of VBA, that should be enough to get you going.

Good luck!
 
Upvote 0
Hi Eric,

Thank you for your help!
It's not for a school project. I am 42 years old :rolleyes: with very very limited VBA knowledge.
I will try this tomorrow.
Nave a nice evening
Cheers,
Stefan

 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
How to fix vba delay in runing the date rule
Replies
3
Views
88
Skyybot
Skyybot
Emka
  • Question Question
Web Scrap on Chrome via VBA
Replies
0
Views
104
Emka
Emka
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
933
Alex Blakenburg
A
B
  • Question Question
VBA Code to Send Mail to skip blank cells
Replies
1
Views
275
Micron
M
M
  • Solved
VBA - For Cycle with "On Error" not working on the second iteration
Replies
9
Views
152
Joe4
Joe4

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top