SUMIFS using Dictionary

[inactiveUser462638]

Hi Excel Champs,

Please help me getting this calculated. I'm using SUMIFS formulas now but it's going very slow.

I have data like this:

Element Type	Inflow/Outflow	Forecast ID	Element Description	Date	Recording Person	Vendor / Customer	Gross Amount
HR Costs	Outflow		Refund Andre	01.01.2020	Phil	Customer1	18.00
Technology Services	Outflow		Subscription	01.01.2020	Phil	Customer2	45.00
Loans and Taxes	Inflow		Subscription	01.02.2020	Phil	Customer2	45.00
Office Costs	Outflow		Subscription	01.03.2020	Phil	Customer2	36.00
Travel, Accommodation, Expenses	Outflow		Income Tax	01.04.2020	Phil	Customer3	1337.79
Technology Services	Inflow		Subscription	01.04.2020	Phil	Customer2	36.00
Loans and Taxes	Outflow		To Customer4	01.05.2020	Phil	Customer4	0.00
Loans and Taxes	Inflow		Subscription	01.05.2020	Phil	Customer2	36.00

I need to make a CashFlow report out of above data like this:

Date	Beginning Balance	Cash Inflows	Cash Outflows	Net Cash Flows	Ending Cash Balance
01.01.2020	100000	=SUMIF()	=SUMIF()	=C2-D2	=B2+E2
02.01.2020	=F2	=SUMIF()	=SUMIF()	=C3-D3	=B3+E3
03.01.2020	=F3	=SUMIF()	=SUMIF()	=C4-D4	=B4+E4

Can you please help me with a VBA code that calculates this using the Dictionary?

Thank you very much!

 

[inactiveUser462638]

Hello @DanteAmor,

Yes, the post#9 should be the final form.

Thanks!

 

[DanteAmor]

Agree. Then try the following.
Only in cell B2 of the "Calculation Sheet Final Form" put the Beginning Balance.

The macro will work according to the structure of the sheets shown in post #9

Sub SUMIFS_using_Dictionary()
  Dim a As Variant, b As Variant, ky As Variant
  Dim dic As Object, col As Object
  Dim i As Long, j As Long, k As Long, lr As Long, lc As Long
  Dim m As Double, n As Double, bb As Double
  
  Set dic = CreateObject("Scripting.Dictionary")
  Set col = CreateObject("System.Collections.ArrayList")
  bb = Sheets("Calculation Sheet Final Form").Range("B2").Value 'Beginning Balance
  
  a = Sheets("Forecast Elements").Range("A2:N" & Sheets("Forecast Elements").Range("A" & Rows.Count).End(3).Row).Value2
  ReDim b(1 To UBound(a), 1 To 6)
  
  For i = 1 To UBound(a, 1)
    dic(a(i, 5)) = Empty
  Next
  '
  'sort by dates
  For Each ky In dic.keys
    col.Add ky
  Next
  col.Sort
  dic.RemoveAll
  For Each ky In col
    dic(ky) = "0|0"
  Next ky
  'end sort
  '
  For i = 1 To UBound(a, 1)
    m = Split(dic(a(i, 5)), "|")(0)
    n = Split(dic(a(i, 5)), "|")(1)
    If LCase(a(i, 1)) = LCase("Inflow") Then m = m + a(i, 8) Else n = n + a(i, 8)
    dic(a(i, 5)) = m & "|" & n
  Next
  '
  For Each ky In dic.keys
    j = j + 1
    b(j, 1) = ky
    b(j, 2) = bb
    b(j, 3) = Split(dic(ky), "|")(0)
    b(j, 4) = Split(dic(ky), "|")(1)
    b(j, 5) = b(j, 3) - b(j, 4)
    b(j, 6) = b(j, 2) + b(j, 5)
    bb = b(j, 6)
  Next
  '
  Sheets("Calculation Sheet Final Form").Range("A2").Resize(dic.Count, 6).Value = b
End Sub

 

[inactiveUser462638]

That's amazing!

Thank you very much for your support!

You are a lifesaver!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[inactiveUser462638]

Hello @DanteAmor!

Can you please put some comments on the code when you have time? I tried to understand it but I'm not doing a great job. I could not find what that things do: dic(ky) = "0|0", Split(dic(a(i, 5)), "|")(0).

Thank you very much!

 

[DanteAmor]

I hope the following helps you.

Sub SUMIFS_using_Dictionary()
  Dim a As Variant, b As Variant, ky As Variant
  Dim dic As Object, col As Object
  Dim i As Long, j As Long, k As Long, lr As Long, lc As Long
  Dim m As Double, n As Double, bb As Double
  
  Set dic = CreateObject("Scripting.Dictionary")
  Set col = CreateObject("System.Collections.ArrayList")
  bb = Sheets("Calculation Sheet Final Form").Range("B2").Value 'Beginning Balance
  
  a = Sheets("Forecast Elements").Range("A2:N" & Sheets("Forecast Elements").Range("A" & Rows.Count).End(3).Row).Value2
  ReDim b(1 To UBound(a), 1 To 6)
  
  'Fill the dictionary, only the unique dates are loaded.
  For i = 1 To UBound(a, 1)
    'dic(key)  = item 'In this case it is not necessary to fill an item, that's why I put empty
    dic(a(i, 5)) = Empty
  Next
  'In the next part the dates are sorted.
  'sort by dates
  For Each ky In dic.keys
    col.Add ky
  Next
  col.Sort
  'clean dictioary
  dic.RemoveAll
  'Fill the dictionary, With sorted dates
  For Each ky In col
    'key    = item
    'On each date I add a zero for the sum of the inflow
    'and another zero for the sum of the outflow
    'It is like initializing sums.
    dic(ky) = "0|0"
  Next ky
  'end sort
  '
  'Cycle to go through all the dates
  For i = 1 To UBound(a, 1)
    'I get the accumulated value of the sums from the dictionary.
    'Check the value of column 1, if it is "Inflow"
    'Assuming that in column 1 you have "Inflow",
    '              in column 5 you have 23/Jun/2020 and
    '              in column 8 you have the value 100
    '(Obviously the first time it returns 0 and 0)
    m = Split(dic(a(i, 5)), "|")(0)   'get the cumulative value of the inflow sum
    'm = split(dic(23/Jun/2020)), "|")(0)
    'm = split("0|0")), "|")(0) 'The (0) is to obtain the first value of the split
    'm = 0
    n = Split(dic(a(i, 5)), "|")(1)   'The (1) is to obtain the second value of the split
    'Then m = 0 and n = 0
    '
    If LCase(a(i, 1)) = LCase("Inflow") Then
      'then accumulate the value of column 8 in variable m
      m = m + a(i, 8)
      'm = 0 + 100
      'm = 100
    Else
      'If not, accumulate in variable n
      n = n + a(i, 8)
    End If
    'Return accumulated values to dictionary
    dic(a(i, 5)) = m & "|" & n
    'dic(23/Jun/2020) = 100 & "|" & 0
    'dic(23/Jun/2020) = "100|0"
        
    '**********
    'In the next cycle if there is another date in the data with Jun/23
    'then m = 100 and n = 0
    'if the data is "inflow" let's say 345, then m = 100 + 345
    'Return accumulated values to dictionary
    'dic(23/Jun/2020) = "345|0"
  Next
  '
  'Cycle to fill array b by scrolling through values sums stored in dictionary
  For Each ky In dic.keys
    j = j + 1 'row counter
    b(j, 1) = ky  'the date
    b(j, 2) = bb  'Beginning Balance value.
    b(j, 3) = Split(dic(ky), "|")(0)  'cumulative value for inflow
    b(j, 4) = Split(dic(ky), "|")(1)  'cumulative value for outflow
    b(j, 5) = b(j, 3) - b(j, 4) 'inflow - outflow
    b(j, 6) = b(j, 2) + b(j, 5) 'Beginning Balance + difference (inflow - outflow)
    bb = b(j, 6)  'update the value of bb with the sum (Beginning Balance + difference (inflow - outflow))
  Next
  '
  'output array  b
  Sheets("Calculation Sheet Final Form").Range("A2").Resize(dic.Count, 6).Value = b
End Sub

 

[inactiveUser462638]

Thank you very much!!

 

[jkpieterse]

Wouldn't a pivottable work here?