Sum Frequencies in VBA in Excel

SailorJerry7030 · #1 04-30-2018, 04:45 AM

Good Morning All!

I hope I have an easy one for you this morning. I've got about 96k rows of policy numbers and 12 columns starting in column B (Jan-Dec). If a policyholder called within that month it displays how many times they called. I'm not concerned with how many calls they made, only if there is a value > 0 within that cell. The goal here is to sum the clusters of consecutive months they called in. If they call in just in January but not in February, I don't care. If they call in January and February, but not March, that would be considered 1 cluster. If they called in January, February, March, not in April, not in May, called in June, not called in July, called in August, called in September, didn't call us the rest of the year, the sum of clusters would be 2 (Jan-Mar) and (Aug-Sept), don't care about the 1 off month of June where they called since it's not a cluster. I've been trying to incorporate code similar to "=SUM(--((FREQUENCY(IF(A2:H2>=50, COLUMN(A2:H2)),IF(A2:H2<50,COLUMN(A2:H2))))>=3))" granted this series of code is for a completely different project unrelated, but I feel like it could be applicable by changing the cells and values (50 and 3).

I hope this goal makes sense and someone can help. If you need anymore information, let me know. The simpler the better.

ArviLaanemets · #2 04-30-2018, 07:08 AM

Having Policy in column A, and months in columns B:M

Code:

=AND(SUM(B2)>0,C2>0,D2=0)+AND(SUM(C2)>0,D2>0,E2=0)+AND(SUM(D2)>0,E2>0,F2=0)+AND(SUM(E2)>0,F2>0,G2=0)+AND(SUM(F2)>0,G2>0,H2=0)+AND(SUM(G2)>0,H2>0,I2=0)+AND(SUM(H2)>0,I2>0,J2=0)+AND(SUM(I2)>0,J2>0,K2=0)+AND(SUM(J2)>0,K2>0,L2=0)+AND(SUM(K2)>0,L2>0,M2=0)+AND(SUM(L2)>0,M2>0)

Enter the formula into cell N2, and copy down.

SailorJerry7030 · #3 04-30-2018, 07:10 AM

Quote:

Originally Posted by ArviLaanemets

Having Policy in column A, and months in columns B:M

Code:

=AND(SUM(B2)>0,C2>0,D2=0)+AND(SUM(C2)>0,D2>0,E2=0)+AND(SUM(D2)>0,E2>0,F2=0)+AND(SUM(E2)>0,F2>0,G2=0)+AND(SUM(F2)>0,G2>0,H2=0)+AND(SUM(G2)>0,H2>0,I2=0)+AND(SUM(H2)>0,I2>0,J2=0)+AND(SUM(I2)>0,J2>0,K2=0)+AND(SUM(J2)>0,K2>0,L2=0)+AND(SUM(K2)>0,L2>0,M2=0)+AND(SUM(L2)>0,M2>0)

Enter the formula into cell N2, and copy down.

Works like a charm

butttttttt is there a way to put this into VBA code? I only ask because this is going to apply to 96,000 records

ArviLaanemets · #4 04-30-2018, 08:08 AM

Quote:

Originally Posted by SailorJerry7030

is there a way to put this into VBA code? I only ask because this is going to apply to 96,000 records

Is the formula slowing the worksheet down? I ask because this is simple arithmetical formula which needs not much resources.

When yes, then of-course you can write a VBA procedure, which counts the number of rows in UsedRange, and processes then every datarow in your table, calcualtes same formula in VBA (you have to remember, that in VBA FALSE is 0 like in worksheet, but TRUE converts to -1, so you hafe to multiply the result with -1) for every row, and writes the result into proper column in your table. You can run the procedure from button on worksheet, or you can create a shortcut key combination to run it.

I myself don't have Office available for today and tomorrow.

Pecoflyer · #5 04-30-2018, 08:09 AM

Perhaps post a sample sheet with some data and expected results? ( Go advanced - Manage attachments)

SailorJerry7030 · #6 04-30-2018, 08:16 AM

Quote:

Originally Posted by Pecoflyer

Perhaps post a sample sheet with some data and expected results? ( Go advanced - Manage attachments)

No problem! Here is the test data. Test 1, Test 2, etc will be policy #'s and column Q is where I'd like the sum of the clusters to be placed. The Excel Code that ArviLaanemets provided works perfectly and gives the correct results in column S. However, I'm trying to use code in VBA to achieve these results as it will be for a larger data set.

NoSparks · #7 04-30-2018, 09:25 AM

Oh-no, Pecoflyer's going to delete everything above until you supply links to your cross posts

SailorJerry7030 · #8 04-30-2018, 09:27 AM

Quote:

Originally Posted by NoSparks

Oh-no, Pecoflyer's going to delete everything above until you supply links to your cross posts

Didn't even think about that haha umm..I think I only made 2 other posts, let's see...

https://www.ozgrid.com/forum/forum/h...uencies-in-vba

https://www.mrexcel.com/forum/excel-...ithin-vba.html

better?

NoSparks · #9 04-30-2018, 09:51 AM

Using NBVC's formula, adjusted for the columns you actually want, and the sample workbook you posted over there....see if this works

Code:

Sub Clusters()
Dim lr As Long
With Sheets("Sheet3")
    lr = .Cells(2, "A").End(xlDown).Row
    .Range("Q2").FormulaArray = "=SUM(--((FREQUENCY(IF(B2:M2>0, COLUMN(B2:M2)),IF(B2:M2=0,COLUMN(B2:M2))))>1))"
    .Range("Q2").AutoFill Destination:=Range("Q2:Q" & lr)
End With
End Sub

SailorJerry7030 · #10 04-30-2018, 10:02 AM

Quote:

Originally Posted by NoSparks

Using NBVC's formula, adjusted for the columns you actually want, and the sample workbook you posted over there....see if this works

Code:

Sub Clusters()
Dim lr As Long
With Sheets("Sheet3")
    lr = .Cells(2, "A").End(xlDown).Row
    .Range("Q2").FormulaArray = "=SUM(--((FREQUENCY(IF(B2:M2>0, COLUMN(B2:M2)),IF(B2:M2=0,COLUMN(B2:M2))))>1))"
    .Range("Q2").AutoFill Destination:=Range("Q2:Q" & lr)
End With
End Sub

Perfect! I was having difficulty fitting that formula into the code, but this does it!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word & Excel 2010 - Best Options To Auto Insert MySQL & Excel Data?	Hoser	Word	1	03-17-2017 03:47 PM
Embeding Excel Docs in Word - Receiving Memory Error Message if Excel is open	kdash	Word	0	05-06-2015 09:38 AM
How To Open an Macro From 2003 Excel in 2013 Excel Spread Sheet?	ADubin	Excel Programming	3	02-08-2015 04:57 AM
[Excel 2007] Building Power Point Slides from data in an Excel Table	bremen22	Excel Programming	1	08-07-2013 11:01 AM
Excel 2011 can't open old Excel 98 or Excel X files	FLJohnson	Excel	8	05-09-2012 11:26 PM