An better/faster formula?

[thereseexceljakubiak]

Hi guys? can anyone suggest a faster formula to achieve this:


https://dl.dropboxusercontent.com/u/35552808/SAMPLE_chandoo.xls


So, essentially, i have a list that I need to compare to my master database. If those list of IDs exist in the database, count the number of times where their corresponding dates fall within a range.


I have three Sumproducts in my file right now and it slow my entire computer down. I essentially need at least 80,000 formulas... eek! I have a 80 data tables with 100 data cells in each.


The sample sheet explains a little better?

 

[SirJB7]

Hi, therese!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/An%20better_faster%20formula_%20-%20SAMPLE_chandoo%20%28for%20therese%20at%20chandoo.org%29.xls


First sheet has 4 dynamic named ranges:

IdList, column A

MisteryList, row 1 date columns

IDMisteryTable, matrix (Id,Mistery)

SelectedIdList, column O


Second sheet has 3:

TransposedMisteryList, column A

DateList, row 1

SummaryTable, matrix (TransportedMistery,Date)


This is the VBA code:

-----

[pre]

Option Explicit

Sub CorinthiansNaoAnda()
'
' constants
'  ws & ranges
Const ksWSData = "RAW Data"
Const ksId = "IdList"
Const ksMistery = "MisteryList"
Const ksIdMistery = "IdMisteryTable"
Const ksSelectedId = "SelectedIdList"
Const ksWSSummary = "REPORT"
Const ksDate = "DateList"
Const ksTransposedMistery = "TransposedMisteryList"
Const ksSummary = "SummaryTable"
'
' declarations
Dim rngI As Range, rngM As Range, rngIM As Range, rngSI As Range
Dim rngTI As Range, rngD As Range, rngS As Range
Dim I As Long, J As Long, K As Long
'
' start
'  ranges
Set rngI = Worksheets(ksWSData).Range(ksId)
Set rngM = Worksheets(ksWSData).Range(ksMistery)
Set rngIM = Worksheets(ksWSData).Range(ksIdMistery)
Set rngSI = Worksheets(ksWSData).Range(ksSelectedId)
Set rngD = Worksheets(ksWSSummary).Range(ksDate)
Set rngTI = Worksheets(ksWSSummary).Range(ksTransposedMistery)
Set rngS = Worksheets(ksWSSummary).Range(ksSummary)
'
' process
'  initialize
rngS.ClearContents
'  main
With rngIM
For I = 1 To .Rows.Count
' selected?
For K = 1 To rngSI.Rows.Count
If rngI.Cells(I, 1).Value = rngSI(K) Then Exit For
Next K
If K <= rngSI.Rows.Count Then
' dates
For J = 1 To .Columns.Count
If .Cells(I, J).Value <> "" Then
' in range?
For K = 1 To rngD.Columns.Count
If .Cells(I, J).Value <= rngD.Cells(1, K).Value Then Exit For
Next K
' add
If K <= rngD.Columns.Count Then _
rngS.Cells(J, K) = rngS.Cells(J, K) + 1
End If
Next J
End If
Next I
End With
'
' end
Set rngS = Nothing
Set rngTI = Nothing
Set rngD = Nothing
Set rngSI = Nothing
Set rngIM = Nothing
Set rngM = Nothing
Set rngI = Nothing
Beep
'
End Sub

[/pre]
-----


As I wrote in 2nd worksheet, I don't get the figures of the provided table. Please check it.


Just advise if any issue.


Regards!

 

[thereseexceljakubiak]

Hi SirJB7...once again, to my rescue!


Thank you SO very much for this... I will test it out today but knowing you, I am sure that it is awesome and works amazingly!


I do apologize, the numbers in the Report Table, were just for display purposes...not accurate.


Thanks, SirJB7!

 

[SirJB7]

Hi, therese!

Yes, test it and check elapsed time, since I preferred a VBA solution until a lot of SUMPRODUCT volatile functions over several columns of 80 K cells.

Regards!

 

[thereseexceljakubiak]

SirJB7,


Thanks for your help. I uploaded another sample file (with your macro, only modified for my file):


https://dl.dropboxusercontent.com/u/35552808/CHANDOO_SIRJB.xlsm


the macro is working, but was working on the first column in the REPORT. I think maybe, my "fictious" numbers might have been confusing.


In my modified sample file, I have filtered my Raw Data to the List(on Sheet2), only to illustrate how to get the proper Counts for the REPORT. I would like to avoid this step, with a macro. I dropped in some sample CountIf formulas, just to achieve the correct numbers.


So, the goal is with the identified "LIST" (total count of 228), where those IDs exist in the RAW DATA, I need counts for the columns listed in the REPORT that fall within the Date Range. I have listed the columns that i only need in the REPORT, off the RAW DATA.


if you have some time, can you please take a look at this?

 

[SirJB7]

Hi, therese!


My mistake, this instruction:

If K <= rngSI.Columnss.Count Then

should have been:

If K <= rngSI.Rows.Count Then

Code updated at previous post and fixed file uploaded. Please download it again from the same link.


Regards!


PS: I changed the dates for testing purposes. Didn't checked your new file.

 

[SirJB7]

Hi, therese!


With your new file data I get these figures, maybe you can verify them:

-----

[pre]

TOTAL LIST	ACT (#)		ACT (#)		ACT/FCST (%)	FCST (#)	FCST (#)
228		04/05/2013	11/05/2013	18/05/2013	25/05/2013	01/06/2013
MS004
MS006		228
MS009		228
MS014		21
MS027		228
MS030		228
MS045		21
MS071		228
MS088.1		228
MS094		21
MS095
MS097
MS103
MS106
MS107.1
MS109
MS126		144			83
MS143		144			83
MS149		16
MS155
MS160

[/pre]
-----


I suggest you to replace the whole code copying and pasting into your new file since I've moved up an If statement so as to not check against all dates if the cell was empty, to shorten process time.


Regards!

 

[thereseexceljakubiak]

Thanks SirJB7, I will run it through my file!

 

[thereseexceljakubiak]

Im soooo sorry to keep bugging you SirJB7, but I dont know why I cant figure this out!


https://dl.dropboxusercontent.com/u/35552808/CHANDOO_SIRJB.xlsm


Here I have uploaded my file. I just can't figure out how to get the macro modified to fill report like I want.


When I run the macro (yours but modified).. I still only get the counts populating in the first column in the report and very strange...the counts are not accurate....and it populates counts outside of the Report range.. into rows way below Row 23 in REPORT.

 

[SirJB7]

Hi, therese!


Your new uploaded file has a different structure of the summary table (two data lists and two mistery lists). I think you've correctly updated the part of the code that I modified in my fix up, but you only duplicated ranges rngD/rngD2 and rngT/rngT2 and you didn't use them within the code, which you left unmodified.


You have two paths:

a) Try to include processing of the duplicate ranges within the same code structure: hardest but most efficient.

b) Duplicate procedures and in one of them use the original ranges and in the other use the duplicates.


I won't be able to help you further until Monday.


Regards!