Running similar to sumproduct calculation using VBA.

[clide91]

Hello,
I am trying to count occurences of a combination of 3 columns. The formula I have counting them is "=SUMPRODUCT(($F$2:$F2=$F2)*($L$2:$L2=$L2)*($N$2:$N2=$N2))". So this counts the unique occurences of these three values in this row from row 2 up to this particular row. However since I am working with 18,000+ rows, having this formula slows down the file considerably. What I am trying to do then is to turn this into VBA code that will run all of the calculations and then put the values into the columns instead of the formula.

Thank you!

 

[shg]

If you sort the table by those three columns, you can generate the same result with a much lighter formula:

[Table="width:, class:grid"][tr][td] [/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][td]

P​

[/td][/tr]
[tr][td]

1​

[/td][td]

Hdr1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

Hdr2​

[/td][td][/td][td]

Hdr3​

[/td][td]

Count​

[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td]

2​

[/td][td]

1​

[/td][td]O2: =1 + AND(F2=F1,L2=L1,N2=N1)*N(O1)[/td][/tr]

[tr][td]

3​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td]

2​

[/td][td]

2​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td]

3​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

2​

[/td][td][/td][td]

1​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

2​

[/td][td][/td][td]

3​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

2​

[/td][td][/td][td]

3​

[/td][td]

2​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

3​

[/td][td][/td][td]

2​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td]

1​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td]

3​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

11​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

2​

[/td][td][/td][td]

1​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

12​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

2​

[/td][td][/td][td]

1​

[/td][td]

2​

[/td][td][/td][/tr]

[tr][td]

13​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

2​

[/td][td][/td][td]

2​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

14​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

2​

[/td][td][/td][td]

2​

[/td][td]

2​

[/td][td][/td][/tr]

[tr][td]

15​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

3​

[/td][td][/td][td]

2​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

16​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

3​

[/td][td][/td][td]

2​

[/td][td]

2​

[/td][td][/td][/tr]

[tr][td]

17​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

3​

[/td][td][/td][td]

3​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

18​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td]

1​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

19​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td]

1​

[/td][td]

2​

[/td][td][/td][/tr]

[tr][td]

20​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td]

2​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

21​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

2​

[/td][td][/td][td]

3​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

22​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

3​

[/td][td][/td][td]

1​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

23​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

3​

[/td][td][/td][td]

2​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

24​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

3​

[/td][td][/td][td]

3​

[/td][td]

1​

[/td][td][/td][/tr]
[/table]

 

[clide91]

My only issue with this is the amount of data that I'm trying to run the code on, as well as that I am constantly adding hundreds more rows daily so this would increase manual labor of constantly sorting and running the macro. Trying to do this all in VBA.

 

[shg]

You could record a macro that does the sort and adds the formula.

 

[clide91]

ATM I have it changed to run the formula from a recent date through to the end of the data to cut down on computing time and then repasting the formula columns in as values once its done to cut down on lag in the file afterwards. To compute the initial 17,000 or so lines I took the brunt of that force on my computer over the last hour to get that out of the way. With frequent runnings of the code it shouldn't take more than a minute to run and update. Would still like to look for a purely VBA computation solution though.

 

[Fazza]

hi, Clide

Please can you provide some sample data & a description of what you want to do?

I'm thinking a database-like non-formula solution is best. So maybe a pivot table, or query table, or if using VBA other approaches such as SQL+ADO recordset. SUMPRODUCT formulas, even in VBA, are not efficient: and best avoided at all times if you have any serious amount of data.

regards