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!

 

[RoryA]

If you put an empty table in your template, it will expand as you add data to it.

It won't make any difference to that code since it just uses the current region of data (if you have any completely blank rows within your data, it won't process all of it, but presumably your data isn't that bad?)

 

[Newbie73]

Thanks I'm looking into it! Bit off topic, but how would then I add the table section into formulas? Got plenty to update if I go that way! For example:

=LET(src,Data!A2:M56739, fsrc,FILTER(src,INDEX(src,,1)<>""), shortID,INDEX(fsrc,,1), dates,INDEX(fsrc,,12), maxdate,MAX(dates),
uidmd,SORT(UNIQUE(FILTER(shortID,dates=maxdate))), uidnmd,SORT(UNIQUE(VSTACK(shortID,uidmd),,1)),
utd,FILTER(fsrc,ISNUMBER(MATCH(shortID,uidmd,0))), nutd,FILTER(fsrc,NOT(ISNUMBER(MATCH(shortID,uidmd,0)))),
VSTACK(SORT(utd,{1,12},{1,-1}),EXPAND("",,COLUMNS(fsrc),""),VSTACK(SORT(nutd,{1,12},{1,-1}))))

If I make a table and named Data or whatever, how would I update that formula?

 

[RoryA]

Replace Data!A2:M56739 with just Data

 

[Fuji]

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.

So, I read it wrong...
If IR with 0 happens, it calculates wrong...
Please replace 2 lines of a(i, 2) with 1.
1)

                a(.Count, 3) = IIf(a(i, 4) = "IR", 1, 0)  '<--- 1 was a(i,2)

2)

                If a(i, 4) = "IR" Then a(.Item(s), 3) = a(.Item(s), 3) + 1  <--- 1 was a(i,2)

 

[Newbie73]

You mean if IR on column D and 0 on column B? That will never happen either, but a same ID can have IR in two different dates but in the same year (or IR and R). In this case shall I still replace the code lines?

Speaking of it just noticed that "IR" is in the code but not "R". I will do some further testing during the weekend with more time

 

[Fuji]

OK, then you can leave it as it was.

 

[Newbie73]

Just did more testing, the only thing I would still need is to add the "R" alongside the "IR" with exactly the same effect. I've mentioned on my topic but then to be fair forgot to add one in the example sheet. I've added it now on the 2nd row. Is it a matter of just adding or "R" or more complicated?

Thanks!

 

[Fuji]

Do you wnat the count of R add to the same output column or add a new column for R in the result?
Do you have anything other than CLEAR, R or IR?

 

[Newbie73]

Same output column as "IR". It should only be those 3, if anything else likely an error/mistake on the data

 

[Fuji]

Use one of the marked lines that you prefer.

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) <> "CLEAR", a(i, 2), 0)  '<--- this line for sum
                'a(.Count, 3) = IIf(a(i, 4) <> "CLEAR", 1, 0)  '<--- this oine for count
                a(.Count, 4) = y
            Else
                a(.Item(s), 2) = a(.Item(s), 2) + a(i, 2)
                If a(i, 4) <> "CLEAR" Then a(.Item(s), 3) = a(.Item(s), 3) + a(i, 2)  '<--- this line for sum
                'If a(i, 4) <> "CLEAR" Then a(.Item(s), 3) = a(.Item(s), 3) + 1   '<--- this oine for count
            End If
        Next
        [i2].Resize(.Count, 4) = a
    End With
End Sub