VBA code that count last nth occurrences in data range

[chefich]

Hello All,


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

Example: Part 4

Col 1	Col 2
Part 1	R	Result / Last 4 occurrences
Part 2	D		R	D	L
Part 3	L	Part 4	2	1	1
Part 4	L
Part 5	R
Part 2	D
Part 4	R
Part 1	R
Part 3	L
Part 5	R
Part 2	R
Part 3	L
Part 5	R
Part 2	L
Part 1	L
Part 4	R
Part 4	D
Part 1	R
Part 2	D
Part 3	L
Part 4	L
Part 5	R
Part 2	D
Part 4	R
Part 1	R
Part 3	L
Part 5	R
Part 2	R

<tbody>
</tbody>

Please help!

Kind Regards
Stefan

 

[Eric W]

Welcome to the forum.

You can do this with a formula:

	A	B	C	D	E	F
1	Col 1	Col 2
2	Part 1	R	Result / Last 4 occurrences
3	Part 2	D		R	D	L
4	Part 3	L	Part 4	2	1	1
5	Part 4	L
6	Part 5	R
7	Part 2	D
8	Part 4	R
9	Part 1	R
10	Part 3	L
11	Part 5	R
12	Part 2	R
13	Part 3	L
14	Part 5	R
15	Part 2	L
16	Part 1	L
17	Part 4	R
18	Part 4	D
19	Part 1	R
20	Part 2	D
21	Part 3	L
22	Part 4	L
23	Part 5	R
24	Part 2	D
25	Part 4	R
26	Part 1	R
27	Part 3	L
28	Part 5	R
29	Part 2	R

<tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula D4 {=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))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Or do you have a specific reason to want VBA?

 

[chefich]

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

 

[Eric W]

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

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:

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:

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!

 

[chefich]

Hi Eric,

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