Hi,
Although I frequent Mr. Excel to figure out resolutions for my VBA issues, this is my first post here coz after racking my head on this repeatedly I have not gotten anywhere
The data scenario:
I have over 15,000 rows of data in a format similar to the sample table below:
[TABLE="width: 355"]
<tbody>[TR]
[TD="align: center"]S.No[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Month counter[/TD]
[TD="align: center"]Max S.No for a given Account and Month[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]WXYZ[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]WXYZ[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]WXYZ[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]WXYZ[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]15[/TD]
[/TR]
</tbody>[/TABLE]
I need to get the max S.No for each account for each month in the 4th column (column D).
The problem:
I am able to use the MAXIFS formula =MAXIFS($A$2:$A$16,$B$2:$B$16,$B2,$C$2:$C$16,$C2) to get the results that I want in column D and also created a VBA version of the formula i.e.
I am able to run the above VBA code in about 10 seconds through my entire data set (15k rows) but need the VBA code to run on some older versions of Office and just realised that MAXIFS is supported only in Excel available with Office365 (with subscription) and not in any of the non-subscription versions of Office
Request:
Can someone please kindly help in coming up with a VBA version of an alternative for MAXIFS that can be used for a data set of over 15k records in ~10-15 seconds?
Although I frequent Mr. Excel to figure out resolutions for my VBA issues, this is my first post here coz after racking my head on this repeatedly I have not gotten anywhere
The data scenario:
I have over 15,000 rows of data in a format similar to the sample table below:
[TABLE="width: 355"]
<tbody>[TR]
[TD="align: center"]S.No[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Month counter[/TD]
[TD="align: center"]Max S.No for a given Account and Month[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]WXYZ[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]WXYZ[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]WXYZ[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]ABCD[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]WXYZ[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]15[/TD]
[/TR]
</tbody>[/TABLE]
I need to get the max S.No for each account for each month in the 4th column (column D).
The problem:
I am able to use the MAXIFS formula =MAXIFS($A$2:$A$16,$B$2:$B$16,$B2,$C$2:$C$16,$C2) to get the results that I want in column D and also created a VBA version of the formula i.e.
Code:
Dim i As Long
For i = 2 To 16 Step 1
Sheet1.Cells(i, 4).Value = Application.WorksheetFunction.MaxIfs(Sheet1.Range(.Cells(2, 1), .Cells(16, 1)), _
.Range(.Cells(2, 2), .Cells(16, 2)), .Cells(i, 2).Value, _
.Range(.Cells(2, 3), .Cells(16, 3)), .Cells(i, 3).Value)
Next i
Request:
Can someone please kindly help in coming up with a VBA version of an alternative for MAXIFS that can be used for a data set of over 15k records in ~10-15 seconds?