Forum Discussion
Find Minimum Value from a dynamic range
Hi Friends, please help with my requirement. Each block is having some Data. As and when there is a value in the column M, then it should pick the Min/Max value from the next block. For example, in the above Data, as soon as the formula finds a value in Colum M (8 in this case), then immediately it should pick the Min/Max value from BLOCK 2 and print it in the block 2 where i put question Mark (?). Once it finds 6, it should pick the value from Block 3. Since these blocks are not fixed range, unable to hardcode the range. Hence, Please help with the right formula. Thanks in advance.
I use 365 formula solutions for Excel solutions when possible. The starting point is to identify any given block by index (thick block lines communicate structure to the viewer but are difficult to maintain and not easily handled by formula).
Blockλ = LAMBDA(b, LET( n, ROWS(dataSet), seqNo, SEQUENCE(n), blockNo, SCAN(0,seqNo, LAMBDA(acc, s, IF(ISTEXT(INDEX(BlockID, s)), acc + 1, acc)) ), DROP(FILTER(dataSet, blockNo = b), , -1) ) ); BlockMinλ = LAMBDA(b, MIN(Blockλ(b)));The first function scans the block labels incrementing the index column whenever it finds a label. That allows each block of values to be identified and the second function returns the minimum over the block. A similar function is used to locate the value that shows whether the next block is to be displayed.
Sub min() Dim i As Long Dim j As Long Dim k As Long Dim maxrow As Long Range("O:O").Clear Cells(5, 15).Value = "Min" maxrow = Cells(Rows.Count, 8).End(xlUp).Row For i = 6 To maxrow If Cells(i, 13) <> "" Then j = i + 1 For k = j To maxrow If Cells(k, 13) <> "" Then Cells(k, 15).Value = Application.WorksheetFunction.min(Range(Cells(j, 8), Cells(k, 12))) Exit For Else End If Next k Else End If Next i End SubMaybe with this code. In the attached file you can click the button in cell Q3 to run the macro.
