MAX and Min value

  • Thread starter Thread starter chandra sekaran
  • Start date Start date
C

chandra sekaran

Dear all

i know little bit vba code i have problem with below data what
is the max and Min value of S.No. any one help me

No amt max min
15 150
15 50 50
15 175 175
25 26
25 -45 -45
25 222
25 656
25 999 999


Advacne thanks
chandru
 
chandra said:
i know little bit vba code i have problem with below data what
is the max and Min value of S.No. any one help me
Click to expand...

I'm guessing this is supposed to say something along the lines of: "I only
know a little bit of VBA. I have a problem: how do I determine the maximum
and minimum values of a series of 'groups', as demonstrated below?"
No amt max min
15 150
15 50 50
15 175 175
25 26
25 -45 -45
25 222
25 656
25 999 999
Click to expand...

The below code should do want you need. There are 5 constants that need
correct values (no changes needed if your column headings are at A1 and the
data is formatted exactly as your sample data is).

Note that this doesn't care if the 'No' values are grouped together or not
(i.e. sorted or unsorted). It also doesn't worry about duplicate values --
the first match is the one selected. (In the above example data, if 'No
25' had 'amt 999' listed twice, then only the first one would be marked as
the max.)

-----begin code-----
Private Type MinMax
No As Long
min As Long
max As Long
minrow As Long
maxrow As Long
End Type

'-----THESE CONSTANTS ARE IMPORTANT AND MUST BE VERIFIED-----
'input start row
Private Const startrow = 2
'input columns
Private Const No_col = 1
Private Const amtcol = 2
'output columns
Private Const maxcol = 3
Private Const mincol = 4

Sub MinMaxByNum()
ReDim mm(0) As MinMax
cnt = -1
'this loop steps through the rows
For r = startrow To Cells.SpecialCells(xlCellTypeLastCell).Row
'this loop does the actual work of checking
For n = 0 To cnt
'in here, we've found the proper No; now check the amt
If Cells(r, No_col).Value = mm(n).No Then
If Cells(r, amtcol).Value > mm(n).max Then
mm(n).max = Cells(r, amtcol).Value
mm(n).maxrow = r
End If
If Cells(r, amtcol).Value < mm(n).min Then
mm(n).min = Cells(r, amtcol).Value
mm(n).minrow = r
End If
GoTo iterate
End If
Next n
'if we get here, it's a new No
cnt = cnt + 1
ReDim Preserve mm(cnt)
With mm(cnt)
.No = Cells(r, No_col).Value
.min = Cells(r, amtcol).Value
.max = Cells(r, amtcol).Value
.minrow = r
.maxrow = r
End With
'if it's *not* a new No, we jump to here (from the GoTo above)
iterate:
Next r
'at this point we're essentially done; just need to print the results
For n = 0 To cnt
Cells(mm(n).minrow, mincol).Value = mm(n).min
Cells(mm(n).maxrow, maxcol).Value = mm(n).max
Next n
End Sub
-----end code-----

This could also be done using normal spreadsheet functions -- for example:
C2:
=IF(MAX(B$2:B$4)=B2,B2,"")
D2:
=IF(MIN(B$2:B$4)=B2,B2,"")
....although spreadsheet functions aren't my strong point.
 
chandra sekaran said:
i know little bit vba code i have problem with
below data what is the max and Min value of S.No. [....]
No amt max min
15 150
15 50 50
15 175 175
25 26
25 -45 -45
25 222
25 656
25 999 999
Click to expand...

Assume the data above are in columns A through D, with 15 in A2 and 150 in
B2. If you want a non-VBA solution, you could put the following array
formulas [*] into C2 and D2 and copy down:

C2: =IF(B2=MAX(IF($A$2:$A$9=A2,$B$2:$B$9)),B2,"")
D2: =IF(B2=MIN(IF($A$2:$A$9=A2,$B$2:$B$9)),B2,"")

[*] Enter an array formula by pressing ctrl+shift+Enter instead of Enter.
Excel will display an array formula surrounded by curly braces in the
Formula Bar, i.e. {=formula}. You cannot type the curly braces yourself.
If you make a mistake, select the cell, press F2 and edit, then press
ctrl+shift+Enter.

But if you really need VBA code, the following might work for you if the
assumptions are valid.

-----

Option Explicit

Sub doit()

' *** change where data begins ***
Const upperLeft As String = "A2"

Dim n As Long, i As Long
Dim num As Long
Dim minAmt As Long, minRow As Long
Dim maxAmt As Long, maxRow As Long
Dim rng As Range, data

' copy data
' assume at least 2 rows of data
Set rng = Range(upperLeft, Range(upperLeft).End(xlDown)).Resize(, 2)
data = rng
n = UBound(data, 1)
ReDim res(1 To n, 1 To 2) ' for min, max results

' assume data are grouped by num in first column
num = data(1, 1)
minAmt = data(1, 2): minRow = 1
maxAmt = minAmt: maxRow = 1
For i = 2 To n
If data(i, 1) = num Then
If data(i, 2) < minAmt Then
minAmt = data(i, 2): minRow = i
ElseIf data(i, 2) > maxAmt Then
maxAmt = data(i, 2): maxRow = i
End If
Else
res(maxRow, 1) = maxAmt
res(minRow, 2) = minAmt
num = data(i, 1)
minAmt = data(i, 2): minRow = 1
maxAmt = minAmt: maxRow = 1
End If
Next
res(maxRow, 1) = maxAmt
res(minRow, 2) = minAmt

rng.Offset(0, 2) = res
End Sub
 
Back
Top