In the attached file there is a macro called blah which when run produces a new sheet. The result is in a different format from yours. Your format produces at last 12 rows of data per Member Number, this one displays the maxima in green and the minima in red, and if they're on the same line the line is not repeated, so fewer lines per Member Number are needed. Also note that the minimum (or maximum) for a given column/Member Number may appear on two lines on your original data; in which case both are output. There's an example of the output in the sheet called Sheet1 (2) (which can be deleted since running the macro will prodce a fresh one exactly the same).
It looks like your data has been imported from some other kind of output - I was wondering whether it was from a database and the database might be query-able with SQL?
Anyway, for others, the macro's code:
Code:
Sub blah()
On Error GoTo GoHere
Application.ScreenUpdating = False
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Set NewSheet = ActiveSheet
With NewSheet
.Columns("B:C").Delete
.Rows("1:7").Delete
.Rows("2:3").Delete
.Range("U1").Value = "include"
With Intersect(.UsedRange, .Columns(1))
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
.Offset(, 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
lr = NewSheet.UsedRange.Rows.Count
Range("I2").FormulaArray = "=RC[-6]=MAX(IF(R2C1:R" & lr & "C1=RC1,R2C[-6]:R" & lr & "C[-6]))"
Range("I2").Copy Range("J2:N2")
Range("I2:N2").Copy Range("I3:N" & lr)
Range("O2").FormulaArray = "=RC[-12]=MIN(IF(R2C1:R" & lr & "C1=RC1,R2C[-12]:R" & lr & "C[-12]))"
Range("O2").Copy Range("P2:T2")
Range("O2:T2").Copy Range("O3:T" & lr)
.Offset(1, 20).Resize(.Rows.Count - 1).FormulaR1C1 = "=COUNTIF(RC[-12]:RC[-1],TRUE)"
.Offset(, 20).AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd
With .Offset(1, 2).Resize(.Rows.Count - 1, 6).SpecialCells(xlCellTypeVisible)
.Select
For Each cll In .Cells
If cll.Offset(, 6).Value = True Then cll.Interior.ColorIndex = 35
If cll.Offset(, 12).Value = True Then cll.Interior.ColorIndex = 22
Next cll
End With
.Resize(, 8).SpecialCells(xlCellTypeVisible).Copy .Offset(.Rows.Count)
.AutoFilter
.EntireRow.Delete
End With
.Cells(1).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
.Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Clear
.UsedRange.RemoveSubtotal
.Cells(1).Select
End With
GoHere:
Application.ScreenUpdating = True
End Sub
Bookmarks