Extracting multiple max & min values from a column

[Scatz]

Hi,

I am working on a project at the moment where we read a lot of data(3751rows, 4 columns) at a time from some equipment. What I want to do is search through the data and find the max/min values. The problem is the data is cycular so I don't simply want 1 value. When I do find a relative max/min value I want to highlight the cell and copy/paste it to a different sheet.

I have tried the following, and I get a runtime 9 error. VBA obviously doesn't like my use of k so I commented it out and then it works (don't know why it didn't). However, the result gives me around 30 max values (I haven't adjusted the code yet for th min values) and there should only be 7!!!

I have attached a sample of data with a graph. Essentailly all I want are the 7 or so max and min values. I don't know if there is a much better way of trying to get them? Perhaps from the graph directly? I have tried to smooth the data but I still get the same problem. I know excel/VBA is giving me exactly what I ask it to, I just don't know how to ask it to give me what I want!

This is the very last bit of a long stream of VBA to try and sort this data and it's my first time using VBA so it's taken ages to get this far and I just can't seemt o get around the problem

Any help would be great. Thanks in advance
Scatz


[vba]
Sub GetPeaks()
Dim i As Integer
Dim j As Integer
Dim k As Integer


i = 1
k = 3

For i = 4 To 3751
If Worksheets("Summary").Cells(i, 13).Value > _
Worksheets("Summary").Cells(i - 1, 13).Value And _
Worksheets("Summary").Cells(i, 4).Value < _
Worksheets("Summary").Cells(i + 1, 4).Value Then

With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

Worksheets("Summary").Cells(i, 13).Copy
Worksheets("Data Table").Cells(k, 1).Paste

k = k + 1
i = i + 1
Else
i = i + 1
End If
Next i
End Sub

[/vba]

Edited 18-May-07 by geekgirlau. Reason: insert line breaks

[Bob Phillips]

Your code doesn't run, a procedure doesn't exist.

[Scatz]

It runs via a user fom whcih calls a number of subroutines see below. The userform appears on opening the workbook

[VBA]Private Sub WOWSUP_Click()

Call CombineWorkbooks

Call CopySheets

Call Headers

Call Force

Call AvgForce

Call Graphs

Call GetPeaks

Call delEC

End Sub
[/VBA]

[Scatz]

Meant ot say thanks for helping though

[Bob Phillips]

It was CombineWorkbooks that it failed on.

[Scatz]

Hi xld,

I have only posted the code for GetPeaks. The other subroutines all work just fine.

Scatz

[Bob Phillips]

You posted the workbook, and it is that that doesn't run.

[Scatz]

Ah,

I get you now. No I haven't addedd it in there I was just using F5/F8 in the VBA editor as i was developing it. Sorry if I confused you.

I was thinking last night of the following plan:
1) Copying all maximums into a new worksheet withe the time stamp column
2) Displaying each of the timestamps in a userform that the user could "check" the ones that were right and delete those that weren't


Just wondering if you (or anyone else) thought this mght be doable?

Cheers
Scatz

[johnske]

You only want the 7 max/min values? Not sure of your intent here - something like this maybe?
[vba]
Option Explicit
'
Sub TryThis()
'
Dim N As Long
'
'many dupes, so filter uniques to a helper col (change to suit).
With Worksheets("Summary")
.Range("C2:C2528").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("IV2:IV2528"), _
Unique:=True
'
'put maxs/mins in descending order
For N = 3 To 10
.Range("M" & N) = WorksheetFunction.Large(.Range("IV2:IV2528"), N)
.Range("N" & N) = .Range("IV2:IV2528").Find(.Range("M" & N)).Address(0, 0)
.Range("O" & N) = WorksheetFunction.Small(.Range("IV2:IV2528"), N)
.Range("P" & N) = .Range("IV2:IV2528").Find(.Range("O" & N)).Address(0, 0)
Next
'
'sort to arrange in order that maxs/mins occur (optional)
.Range("M3:N10").Sort Key1:=Range("N3"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

.Range("O3:P10").Sort Key1:=Range("O3"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
'
.[M2] = "Maxs": .[O2] = "Mins"
.Range("N3:N10, P3:P10, IV:IV").ClearContents '< optional
'
End With
'
End Sub

[/vba]

[johnske]

Hey Scatz,

From what I see in your attachment you only want absolute maxs and not relative maxs.

I think the only way you can do this properly is if we can assume the chart has some sort of periodicy such that you can examine the Y-axis for absolute maxs in a number of cells that fall within given intervals along the X-axis i.e. for each period.

If we can't assume some sort of periodicy then there are great difficulties. For instance, a value might rise slowly to a large absolute maxima and remain at that maximum value for a number of cells and then slowly descend in value. Further absolute maxs may not rise to anywhere near the value of the first mentioned, so the seven largest values may all be obtained from that single large maxima.

We really need to know if the function generating your data's periodic or not, and if it is periodic, what is the number of cells containing values that fall within each period?

Also, it's a bit difficult to see what you wnat to do. From what you say you only expect to see seven maxs. For instance, are these maxs the first seven maxs or the largest seven? (As mentioned above, it's quite possible that the seven largest values may all belong to one maxima).

i.e. More info is needed...