Vba and big table of data: a dictionary is (probably) needed

[Nelson78]

Hello everybody.

I have a big table of data (it could join 20k records deployed from column A to column AF).
I have to do some operations which, in my opinion, could require a dictionary.

The driver is in column B and it could be, to simplify, a list of surname (suppose two of them repetead in the 20k records, let me say SMITH and ROBERTSON, but they could be more).

Column N is populated by positive integers.
Column AF is populated by dates.

Column J is populated by positive integers, and it is what I have to sum.


I need to calculate the sum in column J of combinations in columns B/N/AF

SMITH, value in column N = 0, values in column AF between two variables of date type (suppose date1 and date2), sum of values in column J.
SMITH, value in column N > 0, values in column AF between two variables of date type (date1 and date2), sum of values in column J.
ROBERTSON, value in column N = 0, values in column AF between two variables of date type (date1 and date2), sum of values in column J.
ROBERTSON, value in column N > 0, values in column AF between two variables of date type (date1 and date2), sum of values in column J.

How could I perform the task?

 

[jkpieterse]

Can you post (a link to) some sample data and the expected outcome? I would guess a pivottable would work with some smart filtering

 

[Nelson78]

Can you post (a link to) some sample data and the expected outcome? I would guess a pivottable would work with some smart filtering

Here the model.

https://imgur.com/GBLGgMm

Where can I share the file?

 

[Nelson78]

Can you post (a link to) some sample data and the expected outcome? I would guess a pivottable would work with some smart filtering

It could be something like this, but I have problems in managing the dates.

   Dim Dic As Object
   Dim Cl As Range
   Dim Ky As Variant
  
   Set Dic = CreateObject("scripting.dictionary")
  
   For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
      If Not Dic.Exists(Cl.Value) Then Dic.Add Cl.Value, CreateObject("scripting.dictionary")
      Dic(Cl.Value)(Cl.Offset(, 12).Value) = Dic(Cl.Value)(Cl.Offset(, 12).Value) + Cl.Offset(, 8).Value
   Next Cl


For Each Ky In Dic.Keys
      With Sheets(2).Range("A" & Rows.Count).End(xlUp)
         .Offset(1).Resize(Dic(Ky).Count).Value = Ky
         .Offset(1, 1).Resize(Dic(Ky).Count, 2).Value = Application.Transpose(Array(Dic(Ky).Keys, Dic(Ky).Items))
      End With
   Next Ky

 

[Nelson78]

It could be something like this, but I have problems in managing the dates.

   Dim Dic As Object
   Dim Cl As Range
   Dim Ky As Variant

   Set Dic = CreateObject("scripting.dictionary")

   For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
      If Not Dic.Exists(Cl.Value) Then Dic.Add Cl.Value, CreateObject("scripting.dictionary")
      Dic(Cl.Value)(Cl.Offset(, 12).Value) = Dic(Cl.Value)(Cl.Offset(, 12).Value) + Cl.Offset(, 8).Value
   Next Cl


For Each Ky In Dic.Keys
      With Sheets(2).Range("A" & Rows.Count).End(xlUp)
         .Offset(1).Resize(Dic(Ky).Count).Value = Ky
         .Offset(1, 1).Resize(Dic(Ky).Count, 2).Value = Application.Transpose(Array(Dic(Ky).Keys, Dic(Ky).Items))
      End With
   Next Ky

Maybe I've taken some step forwards about dates management.

But I cannot perform a nested dictionary.

I mean, I need to collect all the combinations of these:
(Cl.Value)(Cl.Offset(, 12).Value)(Cl.Offset(, 30).Value)
and perform the sum on Cl.Offset(, 8).Value

 

[jkpieterse]

You could upload your file to a service like OneDrive or DropBox and create a sharing link which you post here?

 

[Akuini]

Where can I share the file?

You can post your data as a table using XL2BB add-in, you can find it here:

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

OR

You can just select & copy the range in your sheet then paste it into the reply box, but using this method there won't be any column letter & row number.

Note:
We can't attach a file in this forum.
You could upload your workbook (without sensitive data) to a free site (such as dropbox.com or google drive), then put the link here.

 

[Nelson78]

You could upload your file to a service like OneDrive or DropBox and create a sharing link which you post here?

This way?

Filebin :: bin kn5no2gfhk6f9z3f

Upload files and make them available for your friends. Think of it as Pastebin for files. Registration is not required. Large files are supported.

filebin.net

 

[jkpieterse]

This is done very easily using a pivottable, no VBA required.

 

[Nelson78]

This is done very easily using a pivottable, no VBA required.

The process should be embedded in a larger automation.
I've never worked this way with a pivottable: how could I proceed?