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]

Simple really, you create a pivottable which has the grouping fields in the row area and the SUM field -well- in the Sum area. Then if you have new data you replace the old data withe the new and refresh the pivottable. Done.

 

[Nelson78]

Simple really, you create a pivottable which has the grouping fields in the row area and the SUM field -well- in the Sum area. Then if you have new data you replace the old data withe the new and refresh the pivottable. Done.

I'm sorry for my imperfect english, but I 'm not clear enough in the explanation.
I'm going to try again.

This part is just a part of a wide macro already done.

Imagine:

sub macro()

'do stuff for 3-4 minutes'

'-----------------------------
'BEGIN JOB TO BE COMPLETED
'then I need to calculate:'

sheets(5).range("A1") = 'what you can read in the row below
'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.

sheets(5).range("A2") = 'what you can read in the row below
'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.

sheets(5).range("A3") = 'what you can read in the row below
'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.

sheets(5).range("A4") = 'what you can read in the row below
'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.
'-----------------------------
'END JOB TO BE COMPLETED
'-----------------------------

''do stuff for 3-4 minutes'

end sub

The part in red is what I need the help for: it is the heart of a wider job.
How can I perform it without manually interaction?

Thank's.