Transforming formula to VBA Code

[Newbie73]

Hello, I'm trying to transform some formulas into VBA code to make the Excel spreadsheet more efficient as it contains thousands of data.

Is there a way to do the calculation in the provided example spreadsheet by code?

The formula itself works as intended, from column A selects one ID per year based on the date from Column C. If for the same ID there's more than one data entry for the same year, sum those entries values of Column B and if in that year for that ID column D ="ir" or "r" then count as 1 (or sum if more than one entry for the same year)

Example spreadsheet:

test11.xlsx

Let me know if I didn't explain well or if you need any more information, thanks!

 

[Newbie73]

=LET(f,HSTACK(A2:A30000,B2:B30000,YEAR(C2:C30000),D2:D30000),u,UNIQUE(HSTACK(CHOOSECOLS(f,1,3))),b,BYROW(u,LAMBDA(br,SUM(IF((INDEX(f,,1)=INDEX(br,,1))*(INDEX(f,,3)=INDEX(br,,2)),INDEX(f,,2))))),c,BYROW(u,LAMBDA(br,SUM(IF((INDEX(f,,1)=INDEX(br,,1))*(INDEX(f,,3)=INDEX(br,,2))*((INDEX(f,,4)="ir")+(INDEX(f,,4)="r")),1,0)))),CHOOSECOLS(HSTACK(u,b,c),1,3,4,2))

Sorry should have added the formula itself here.

 

[Fuji]

Here's how I read your formula:

Sub test()
    Dim a, i&, y&, s$
    a = [a1].CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(a, 1)
            y = Year(a(i, 3))
            s = Join(Array(a(i, 1), y), Chr(2))
            If Not .exists(s) Then
                .Item(s) = .Count + 1
                a(.Count, 1) = a(i, 1)
                a(.Count, 2) = a(i, 2)
                a(.Count, 3) = IIf(a(i, 4) = "IR", a(i, 2), 0)
                a(.Count, 4) = y
            Else
                a(.Item(s), 2) = a(.Item(s), 2) + a(i, 2)
                If a(i, 4) = "IR" Then a(.Item(s), 3) = a(.Item(s), 3) + a(i, 2)
            End If
        Next
        [i2].Resize(.Count, 4) = a
    End With
End Sub

 

[Alex Blakenburg]

@Fuji - Wait for the OP to come back but the way I read it Output column 2 is a Sum of Column B.
But Output column 3 is a Count of anything with a IR or R in Column D (regardless of whether it is 0 or not)
@Newbie73 - please clarify.

 

[Newbie73]

Thank you Fuji! That did the trick.

Alex sorry I wasn't clear on that, but to be honest it doesn't actually matter to the real data as those events will only happen maximum of twice in a year, so both sum or count would lead to the same result in this case.

 

[Alex Blakenburg]

Thanks for clarifying. Glad that Fuji's solution worked for you.

 

[RoryA]

FWIW, if you make the source data into a table (I called it Data) then this formula should do the same as yours and I think is more efficient:

=LET(d,Data,a,INDEX(Data,,1),yc,YEAR(INDEX(Data,,3)),u,UNIQUE(HSTACK(a,yc)),seq,SEQUENCE(ROWS(u)),out,DROP(REDUCE("",seq,LAMBDA(st,cu,VSTACK(st,LET(rw,INDEX(u,cu,0),sd,FILTER(d,(a=INDEX(rw,1))*(yc=INDEX(rw,2))),HSTACK(rw,SUM(INDEX(sd,,2)),IFERROR(ROWS(FILTER(sd,(INDEX(sd,,4)="ir")+(INDEX(sd,,4)="r"))),0)))))),1),CHOOSECOLS(out,{1,3,4,2}))

 

[Newbie73]

Thanks Rory! But at least on this one Fuji code really speed up the calculation time

 

[RoryA]

So would just making the data into a table so you don't process thousands of empty rows... (You could also then use Power Query if you don't need live formulas)

 

[Newbie73]

I see Rory, this might be a basic question, sorry if so, but you mean a normal table? As my data changes frequently (it's not live, but I have a template and use it in putting new different data which varies greatly in size) would there be a easy and perhaps automatic way to creating all the necessary tables?

And would a table speed up even more a code like Fuji's for example?