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!!!

 

[jasonb75]

How many rows of data do you actually have in sheet1?

You're processing an array over a whole column (multiple times) so if in reality you only have 10k rows of data then 99% of the processing effort is being wasted.

 

[jasonb75]

Also I note that you have used the option to ignore hidden rows in your first attempt, but there is no other indication of this anywhere in your post so it is not clear if this is a requirement or not.

Array calculations in aggregate nullify the ignore hidden rows option, so if it is a requirement then the use of subtotal and offset will be needed.

I will help you with the correct code but some clarification is needed first.

 

[Fazza]

what about using a pivot table?

or a query?
SELECT salesperson, category, MIN(amounts) AS [TheMinimum], MAX(amounts) AS [TheMaximum]
FROM [Sheet1$]
GROUP BY salesperson, category

No formulas & no code. Both of these are fast over very large datasets

 

[andnand]

Also I note that you have used the option to ignore hidden rows in your first attempt, but there is no other indication of this anywhere in your post so it is not clear if this is a requirement or not.

Array calculations in aggregate nullify the ignore hidden rows option, so if it is a requirement then the use of subtotal and offset will be needed.

I will help you with the correct code but some clarification is needed first.

Sorry Jasonb75, I am very confused with your posts.

There are a few hundred rows of data which is mentioned at the beginning of the post. 3 is what I was recommended to use and is not only for hidden rows, hidden rows is not part of the requirements.

I am not converting the aggregate solution (1) to an array formula, it was an alternate solution because the array formula (.FormulaArray) jumps one row every row when transformed to an array in the formulas in the cells, I don't know why.

Thank you for replying

 

[andnand]

what about using a pivot table?

or a query?
SELECT salesperson, category, MIN(amounts) AS [TheMinimum], MAX(amounts) AS [TheMaximum]
FROM [Sheet1$]
GROUP BY salesperson, category

No formulas & no code. Both of these are fast over very large datasets

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

 

[andnand]

Re-writing the post/thread:

There are 3 columns of data in sheet1 (hundreds of rows, even thousands in a few cases), Amount (ColA), SalesPerson (ColB), Category (ColC).

In sheet 2 there are 2 columns of data, SalesPerson (ColA) and Category (ColB).

What is the fastest way using VBA to automatically add in sheet2, MIN Amount (ColC) and MAX Amount (ColD) based on SalesPerson (ColA) and Category (ColB).

Thank you

 

[jasonb75]

This does what you have asked for, although I will not say that it is the fastest way. There are other formulas that will most likely do the job faster but they are not supported in all versions of excel.

Sub andnand()
With Application
    .ScreenUpdating = False
    .Calculation = xlManual
End With
Dim sh1LastRow As Long, sh2LastRow As Long
sh1LastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
sh2LastRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row

Sheet2.Range("C2:C" & sh2LastRow).FormulaR1C1 = "=IFERROR(AGGREGATE(15,6,Sheet1!R2C1:R" & sh1LastRow & "C1/(Sheet1!R2C2:R" & sh1LastRow & "C2=RC1)/(Sheet1!R2C3:R" & sh1LastRow & "C3=RC2),1),""No Data"")"
Sheet2.Range("D2:D" & sh2LastRow).FormulaR1C1 = "=IFERROR(AGGREGATE(14,6,Sheet1!R2C1:R" & sh1LastRow & "C1/(Sheet1!R2C2:R" & sh1LastRow & "C2=RC1)/(Sheet1!R2C3:R" & sh1LastRow & "C3=RC2),1),""No Data"")"
Sheet2.Range("C2:D" & sh2LastRow).Style = "Currency"
With Application
    .ScreenUpdating = True
    .Calculation = xlAutomatic
End With
End Sub

 

[andnand]

Dear jasonb75, sorry I took so long to reply (we had some tough days here....) and thanked you for your help.

I was expecting your code to work a bit faster than my aggregate code and to my surprise, it can't even be compared. What did I do wrong?, why is your code much faster/efficient?, just want to learn...

Again, thank you!!!!

PD Cant even imagine what could be faster... I use 365/2003

 

[jasonb75]

Turning off calculation and screenupdating while the code is running can make a lot of difference (first 4 lines of the code, the last 4 turn them back on after).

By far the biggest improvement though will be only processing the formula on the rows that have data. Each sheet in excel contains 1048576 rows, your formula was processing all of them!
As I pointed out in an early post, doing that on a sheet that only has 10,000 rows of actual data means over 99% wasted processing effort (time), even less data would mean even more time wasted on the empty rows.

As a rough example, for each second it takes to process 17,500 rows of data using my method, your full column formula would take a full minute.