Find max min of subsets only after Instruction given

[RINCONPAUL]

I have a column A of NAME subsets. A col of MATCHED values adjacent and in 3rd col 'C' an INSTRUCTION, either "BACK" or "LAY".
I could find the max-min of each NAME subset's MATCHED value with an array formula, but adjacent to each INSTRUCTION occurrence, I seek to find the max or min MATCHED value thereafter for the NAME subset, and including the row the INSTRUCTION is in. In the attachment I have put the answers in cols D & E.

I would prefer a vba code if possible as there are 100's of thousands of rows and the array formula will take forever 'if not crash?'

[mikerickson]

In C2, put the CSE formula =IF(C2<>"", MAX(IF(($A$1:$A$1000=A2)*((ROW($A$1:$A$1000)>=ROW(A2))),$B$1:$B$1000)), "")
In D2, put the CSE formula =IF(C2<>"", MIN(IF(($A$1:$A$1000=A2)*((ROW($A$1:$A$1000)>=ROW(A2))),$B$1:$B$1000)), "")

(Enter these formulas with Ctrl-Shift-Enter (Cmd+Return for Mac))

Then drag down.

Adjust $A$1:$A$1000 and $B$1:$B$1000 if you have more than 1000 rows.

[RINCONPAUL]

Fantastic mikerickson! Not so hard for some?...and such a quick reply. Champion.
I can only assume that a vba solution is not applicable?

[mikerickson]

VBA is possible, but why go with inferior VBA when native excel is available.

[SamT]

I don't know how you made Range.End fail so miserably in Column C, but after I pasted all the values into a text editor, then pasted those back into C, it all worked OK. I mean Ctrl+Down Arrow would skip right past several cells with text in them, then stop on empty cells. Always the same cells.

Option Explicit

Sub SamT_MinMax()
Dim Instruction As Range
Dim FirstName As Range
Dim LastName As Range
Dim WSF As WorksheetFunction

'Application.ScreenUpdating = False 'Uncomment after testing
Set WSF = Application.WorksheetFunction

If Range("C2") <> "" Then
  Set Instruction = Range("C2")
Else
  Set Instruction = Range("C1").End(xlDown)
End If

Do
  Set FirstName = Instruction.Offset(, -2)
  Set LastName = FirstName
  Do While LastName = LastName.Offset(1)
    Set LastName = LastName.Offset(1)
  Loop
  
  Instruction.Offset(, 1) = WSF.Max(Range(FirstName, LastName).Offset(, 1))
  Instruction.Offset(, 2) = WSF.Min(Range(FirstName, LastName).Offset(, 1))

  If Instruction.Offset(1) <> "" Then
    Set Instruction = Instruction.Offset(1)
  Else
    Set Instruction = Instruction.End(xlDown)
  End If
  
Loop While Instruction.Row < Rows.Count
  
Application.ScreenUpdating = True
End Sub

[RINCONPAUL]

Well that's a first, "why go with inferior VBA"...and here's me thinking I've missed out on a lifetime of creation without it? LOL

SamT, confused by your post? Who or what were you referring to with your comment, "I don't know how you made Range.End fail so miserably...." Anyways, the results of your code never aligned with any of the INSTRUCTIONS on my sheet? Thanks for the try though.

[SamT]

Who or what were you referring to with your comment, "I don't know how you made Range.End fail so miserably...." Anyways, the results of your code never aligned with any of the INSTRUCTIONS on my sheet? Thanks for the try though.

The problem is the worksheet, not the code. I tested it thoroughly.

Try this: Select cell C1, then press Ctrl+down arrow. Range("C1").End(xlDown) is the VBA equivalent. That should select the first cell under C1 that is not empty, (assuming C2 is empty.)

That code would not work on the example you attached because somehow, something messed up the way Ctrl+down arrow works in column C. Don't ask me what or how, because what is happening is impossible.

After I replaced all of column C with new data, that code worked just fine.

Run this code one time, then try my minmax sub again

Sub FixC()
Dim Cel As Range
For Each Cel In Range("C1:C" & Cells(Rows.Count, "A").End(xlUp).Row)
Cel.Value = Trim(Cel)
Next

End Sub

[SamT]

I can't detect what is in the cells but according to this code only C14 is actually empty

Sub WhatInC()
Dim Cel As Range
For Each Cel In Range("C1:C" & Cells(Rows.Count, "A").End(xlUp).Row)
If Not IsEmpty(Cel) Then Cel.Offset(, 4) = "not Empty"
Next
End Sub

I thought it might be something unique to Excel 2007 and up, but even after saving it as an XP file, it still acted in a spooky manner.

[RINCONPAUL]

Hahahaa, you made me laugh Sam, with, "something messed up the way Ctrl+down arrow works in column C. Don't ask me what or how, because what is happening is impossible", BUT you're right! Did what you said, and it works a treat. Cheers for that. Might be time to get the MEN IN BLACK team back to investigate?

All the best,

[mikerickson]

Well that's a first, "why go with inferior VBA"...and here's me thinking I've missed out on a lifetime of creation without it? LOL
.

VBA is slower and uses more resources than native Excel. It is also less robust. It should only be used when what one wants can't be done with native excel.