PDA

View Full Version : [SOLVED] Find max min of subsets only after Instruction given



RINCONPAUL
08-03-2016, 05:22 PM
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
08-03-2016, 06:24 PM
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
08-03-2016, 06:39 PM
Fantastic mikerickson! Not so hard for some?...and such a quick reply. Champion. :clap2:
I can only assume that a vba solution is not applicable?

mikerickson
08-03-2016, 06:41 PM
VBA is possible, but why go with inferior VBA when native excel is available.

SamT
08-03-2016, 06:43 PM
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
08-03-2016, 07:14 PM
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...." :confused: Anyways, the results of your code never aligned with any of the INSTRUCTIONS on my sheet? Thanks for the try though.

SamT
08-03-2016, 08:02 PM
Who or what were you referring to with your comment, "I don't know how you made Range.End fail so miserably...." :confused: 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
08-03-2016, 08:27 PM
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
08-03-2016, 08:50 PM
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,
:beerchug:

mikerickson
08-03-2016, 09:03 PM
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.