I need to find the max from criteria, quickly and without an array

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
Hi guys, this is the old code, don't spend too long looking at it I'll summarise:

VBA Code:
ahtemp.Activate
Range("A1").Value = "Tourno"
Range("B1").Value = "Last Used"
Range("A2").Activate
a.Activate '(This is advert data)
Range("A2").Activate

Do Until Cells(ActiveCell.Row, "A").Value = ""
    If Cells(ActiveCell.Row, "Y").Value > 0 Then
    ctno = Cells(ActiveCell.Row, "A").Value
    luse = 0
        Do Until Cells(ActiveCell.Row, "A").Value <> ctno
            If Cells(ActiveCell.Row, "N").Value = PapNam And Cells(ActiveCell.Row, "L").Value > luse Then
            luse = Cells(ActiveCell.Row, "L").Value
            End If
        ActiveCell.Offset(1, 0).Activate
        Loop
    ahtemp.Activate
    Cells(ActiveCell.Row, "A").Value = ctno
        If luse = 0 Then
        Cells(ActiveCell.Row, "B").Value = "N/A"
        Else
        Cells(ActiveCell.Row, "B").Value = luse
        End If
    ActiveCell.Offset(1, 0).Activate
    a.Activate
    End If
ActiveCell.Offset(1, 0).Activate
Loop

We have the Advert Data file which groups each ctno (tour ref) together and they're ordered from top to bottom, so every "ABC123" is together and they're ordered oldest to newest.

The previous data guy had a fetish for loops in loops in loops but there's a more elegant formulaic solution which is basically:

MAX | IF | (Tour Ref Range = Tour Ref)*(Advert Name Range = Advert Name), Advertisement Date


This must be an array formula, and it can be applied across a column from 2 to like, 250 cells. Each line in the column has a different tour ref to test against.

So I'm thinking I can apply it to the top row in Formula format, and then use the Fill Down function to apply down the range. But believe it or not, this is slower than that nested Do Until loop mess.

Is there a faster way of doing what I want, without array formulas? Thanks!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
Excel Formula:
={MAX(IF(('Advert Data 2021.csv'!$A:$A=$E3)*('Advert Data 2021.csv'!$N:$N=$A3),'Advert Data 2021.csv'!$L:$L))}

This is the formula, and it's currently being written to cell and then filled down. For 53 rows, it takes about 6 seconds. This is an additional 60% of the time added on to each loop of my code (about 600 iterations)

So a faster way is much appreciated!
 

Forum statistics

Threads
1,144,159
Messages
5,722,833
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top