VBA for min & max based on multiple criteria

[andnand]

Hello everyone,

It's taking forever to calculate and write MIN and MAX based on 2 criteria for a few hundred rows. The current methods that I have written are too slow and/or give me some weird errors.

Sheet1, ColumnA(amounts), ColumnB(salesperson), ColumnC(category)

On Sheet 2, based on salespersonin ColumnA and category in ColumnB, need to write MIN amount in ColumnC and MAX amount in ColumnD

Example code for MIN:

1) Attempt:

sheet2.Range("I2:I" & Lastrow).FormulaR1C1 = "=AGGREGATE(15,3,1/((Sheet1!C[-1]=RC[-2])*(Sheet1!C[]=RC[-1]))*Sheet1!C[-2],1)"
shgroup.Range("I2:I" & Lastrow).Style = "Currency"

- Very slow

2) Attempt:

sheet2.Range("I2:I" & Lastrow).FormulaR1C1 = "=MIN((IF(Sheet1!C[-1]=RC[-2],IF(Sheet1!C[]=RC[-1],Sheet1!C[-2])))"
shgroup.Range("I2:I" & Lastrow).FormulaArray = Range("I2:I" & Lastrow2).Formula
shgroup.Range("I2:I" & Lastrow).Style = "Currency"

- Slow
- Changes to every 2 rows instead of every 1 row as intended

Only 1) works and its 222222 slow...

Please advise if you know of a faster process way.

Maybe building an array of amounts based on the 2 criteria and then building another array based on MIN and MAX of the first array values/elements. Is this a slower process because of the iteration?

Maybe some other way?

Thank you in advance!!!

 

[andnand]

I always use "call OptimizeOn" and "call OptimizeOff"

Sub OptimizeOn()

With Application
    .ScreenUpdating = False
    .DisplayStatusBar = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With

End Sub


Sub OptimizeOff()

With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
End With

End Sub

But I don't use a precise range inside the formula...does that mean that my formula will be as fast if I had used exact ranges inside the formula... I am only using the ranges when assigning the formula to specific cells...

 

[jasonb75]

does that mean that my formula will be as fast if I had used exact ranges inside the formula...

There may be some marginal differences, but it would be much closer than full column ranges inside the formula.

Ranges don't need to be precise, just realistic. As you're applying the formulas with vba, precise takes no extra effort, but if you were entering the formulas by hand it is common to round up the number of rows based on the amount of data likely to be added, or to use named ranges to make them dynamic (google can tell you a lot about that).

 

[andnand]

Got it, thank you!

 

[andnand]

Thank you Fazza, not sure how to do that.

Is that something I can automate and then right individual results to cells or stays as a pivot?

Can a process a virtual pivot get the results and paste them to the cells? or can I simply create a pivot, copy and paste the results in the original order and delete the pivot?

I love pivots but never used them on the vba side.

Thank you for replying

Hi Fazza @Fazza,

Not sure if you received my previous reply. Can you please send me a link to a complete example, would like to learn..

 

[Fazza]

hello, I don't have a link to a complete example, sorry. I don't have a complete sample of data either & I can't test in office 365, or MacOS.
There are various approaches to do this sort of thing & I see you're now after the fastest. I can't advise what is fastest for your data & set up & exact requirements.
I haven't re-read the thread but I don't remember if the table being populated is fixed or created on the fly. Populating an array in VBA memory is usually very fast.
Using a query can take a little time to connect but once that step is done can be pretty fast especially on very large datasets & more complex criteria: it has the advantage that SQL can be used instead of writing custom code (as for an array).
To learn more I think google is great for finding examples.
regards

 

[Fazza]

Is the Sheet2 list with salesperson & category names pre-defined/set up?
Or maybe set up from the values in Sheet1.

If the latter, a query is a simple - albeit not fastest due to the connection time - solution to code. Could be implemented via a recordset in ADO. SQL as posted in my first post.

 

[Fazza]

didn't immediately find an example when I googled, this thread from 2008 (a long time ago, now!) is sort of similar Lookup/Find MAX value that is also latest/most recent? - though it had a more complex requirement. it gives a starting point, anyway. for entire sheet2 table created on the fly. connection strings will be different for your version & software and the best site I know for them is ConnectionStrings.com - Forgot that connection string? Get it here!

 

[andnand]

Thank you @Fazza, sheet2 is the result of a cleaned sheet1, basically, no duplicates....and sheet1 as stated before is variable, hundreds to thousands of rows, depending on the study...

@jasonb75 helped me tons with the improvements, a game-changer and simple to implement. I was curious about queries vs arrays within excel and speed...