VBA code that locate and store "local" max. and min. values in a column and store the time associated corresponding to that value...........

[NCO_1969]

Hi Excel / VBA community

I have not been doing any VBA programming for more than ca. 10 to 15years, so I have forgot big deal
Now I have a problem of data processing large amount of data, that I get in *.xlsx - file format.

The file has the following content / layout:

Time step	A - response #1	B - response #2	C - response #3	D - response #4
0.001	Data ***. to response #1	Data ***. to response #2	Data ***. to response #3	Data ***. to response #4
0.002	Data ***. to response #1	Data ***. to response #2	Data ***. to response #3	Data ***. to response #4
0.003	Data ***. to response #1	Data ***. to response #2	Data ***. to response #3	Data ***. to response #4
continues down ca. 33.000 lines	continues down ca. 33.000 lines	continues down ca. 33.000 lines	continues down ca. 33.000 lines	continues down ca. 33.000 lines

I would like some help with some code how to identify local max. & min. in the different columns (A, B, C and C), each time a local min. or max. value is identified in a column the associated time should be stored, so the time between max. and min. values occur can be calculated, (for each response column).

Hope there are some in here with some good proposals.

KR - NCO

 

[6StringJazzer]

If the data is in column A:D, what column is the time step in?

each time a local min. or max. value is identified in a column the associated time should be stored, so the time between max. and min. values occur can be calculated, (for each response column).

Where do you want it to be stored?

Once stored the time between could be calculated with sheet formulas, but what do you want to calculate? The difference between from a min to the next max, then the max to the next min?

The VBA is pretty simple, something like this:

Private Sub MinMax()

   Dim Row As Long
   Dim Col As Long
   
   For Col = 1 To 4 ' A:D
      
      Row = 3
   
      Do Until Cells(Row, Col + 1) = ""
      
         If Cells(Row, Col) > Cells(Row - 1, Col) And Cells(Row, Col) > Cells(Row + 1, Col) Then
            ' Local maximum, store it
         ElseIf Cells(Row, Col) < Cells(Row - 1, Col) And Cells(Row, Col) < Cells(Row + 1, Col) Then
            ' Local minimum, store it
         End If
         
         Row = Row + 1
         
      Loop
      
   Next Col
   
End Sub

 

[NCO_1969]

that's great - Thanks String Jazzy - I owe you one. I will test it later today. And give you feed back.

KR - NCO

 

[6StringJazzer]

It's just the concept! The real code needs a few more details!

 

[NCO_1969]

I have send the code and a spread sheet home, will try and customize it tonight.
Will post the final code and status......

KR - NCO

 

[NCO_1969]

I have modified the proposal into a for / next loop, something I understand
But get the following error in line #3 in the If nesting, do anybody have an idea why this occur ?

KR - Niels O.

 

[6StringJazzer]

line #3 in the If nesting

which line is that? When I count I get

j = j + 1

which can't be right.

 

[NCO_1969]

Hi 6StringJazzer
I first saw your message now.
I now have some code that "work", but another problem occurred, that the numbers are oscillating while at the same time rising or falling / descending.
Which mean that for each local top or bottom value I get two numbers listed Which mean I have manually to remove all the doublets.
Do you 6StringJazzer or other here in the forum have a proposal for how I filter out these doublets ?


The code I have pt. and that "work" but generate doublets.

The above code generate the following output, where it can be seen that I get all these doublet numbers That I have to remove manually, if I don't manage to remove them via VBA code.

KR - NCO_1969

 

[6StringJazzer]

I can't help without seeing your input data. Do you have any way to share a file?

 

[NCO_1969]

Here comes the VBA

****************************

Private Sub MinMax()
'****************************************
Dim Row As Long
Dim Col As Long
Dim Local_max As Double
Dim Local_min As Double
Dim MaxMin_storage(0 To 100, 1 To 4) As Double
Dim var As Long
Dim i As Integer 'Row to start writing the identified values.
Dim j As Integer 'Column to start writing the identified values.
Dim k As Integer 'Counter variable for writing values into the array variable.
Dim m As Integer 'Counter variable for writing values into the array variable.
Dim lngMaxRows As Long
' ************* Find the last row in the first column ***************************
With Sheet1
lngMaxRows = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
' ********** Start searching for local max / Min values in the respective columns ************
i = 5 'Row to start write found max. / Min. values
j = 20 'Column to start write found max. / Min. values
k = 1 'Counter variable Initial value.
m = 1 'Counter variable Initial value.
' Start looping through all row and columns
For Col = 2 To 5 ' B to E Column #1 is the time step column
For Row = 7000 To lngMaxRows ' Start @ row 8000
If Cells(Row, Col) > Cells(Row - 2, Col) And Cells(Row, Col) > Cells(Row + 2, Col) Then ' Local Max.
'Local_max = Worksheets("Sheet1").Cells(Row, Col).Value 'Add cell value to variable.
Local_max = Cells(Row, Col).Value
MaxMin_storage(k, m) = Local_max 'Store the found local max. in the array.
Cells(i, j).Value = Local_max
Cells(i, j - 2) = Cells(Row, 1).Value 'Print the ***. Time from column #1
'Worksheets("Sheet1").Cells(i, j).Value = Local_max
i = i + 1
k = k + 1
ElseIf Cells(Row, Col) < Cells(Row - 2, Col) And Cells(Row, Col) < Cells(Row + 2, Col) Then ' Local Min.
'Local_min = Worksheets("Sheet1").Cells(Row, Col).Value 'Add cell value to variable.
Local_min = Cells(Row, Col).Value
MaxMin_storage(k, m) = Local_max 'Store the found local min. in the array.
'Worksheets("Sheet1").Cells(i, j).Value = Local_min
Cells(i, j).Value = Local_min
Cells(i, j - 2) = Cells(Row, 1).Value 'Print the ***. Time from column #1
i = i + 1
k = k + 1
End If
Next Row
i = 5
j = j + 3
k = 1
m = m + 1
Next Col
' Now we have to figure out which numbers in the Array variable to print to the cells in the spread sheet.
End Sub

Here comes the first 200 rows of the EXCEL - file. I could not upload an EXCEL - file MR Excel only take image files.....

Structure Position, Actual Response_Vippe_arm_#7_#8_#9_#10
Line A: Structure Position, Actual Response - Global Z (WP_ESTIMATE_VER4_VIPPE_ARM - CP_#21_Column_#4_Hydra_Attachment_VA_#7 relative to CP_#97_pivot_point_on_column_#4_2 (T3_#4_column)) [mm]
Line B: Structure Position, Actual Response - Global Z (WP_ESTIMATE_VER4_VIPPE_ARM - CP_#24_Column_#4_Hydra_Attachment_VA_#8 relative to CP_#96_pivot_point_on_column_#4_1 (T3_#4_column)) [mm]
Line C: Structure Position, Actual Response - Global Z (WP_ESTIMATE_VER4_VIPPE_ARM - CP_#27_Column_#4_Hydra_Attachment_VA_#9 relative to CP_#98_pivot_point_on_column_#4_3 (T3_#4_column)) [mm]
Line D: Structure Position, Actual Response - Global Z (WP_ESTIMATE_VER4_VIPPE_ARM - CP_#30_Column_#4_Hydra_Attachment_VA_#10 relative to CP_#98_pivot_point_on_column_#4_4 (T3_#4_column)) [mm]
*Time (s)	Line A (mm)	Line B (mm)	Line C (mm)	Line D (mm)
0.0	5.770811​	-1.87E-04​	-1.87E-04​	5.770819​
9.00E-03​	-6.11264​	-6.31187​	-6.31142​	-6.11202​
0.018​	-7.46225​	-6.7385​	-6.73466​	-7.45763​
0.027​	-6.09438​	-7.20649​	-7.20596​	-6.09277​
0.036​	-7.48859​	-6.26137​	-6.25645​	-7.48414​
0.045​	-6.09649​	-7.49158​	-7.48992​	-6.09371​
0.054​	-7.51845​	-6.1765​	-6.17172​	-7.51663​
0.063​	-6.11855​	-7.59013​	-7.5878​	-6.11846​
0.072​	-7.54959​	-6.20503​	-6.20465​	-7.54694​
0.081​	-6.15553​	-7.65634​	-7.65182​	-6.15307​
0.09​	-7.58841​	-6.27042​	-6.26771​	-7.58735​
0.099​	-6.19979​	-7.72594​	-7.72418​	-6.19653​
0.108​	-7.64146​	-6.35295​	-6.34962​	-7.64041​
0.117​	-6.25576​	-7.81503​	-7.8136​	-6.25327​
0.126​	-7.70216​	-6.45324​	-6.44948​	-7.69953​
0.135​	-6.32311​	-7.91992​	-7.91596​	-6.31963​
0.144​	-7.77175​	-6.56338​	-6.56353​	-7.76861​
0.153​	-6.39679​	-8.03589​	-8.0348​	-6.39342​
0.162​	-7.85002​	-6.68687​	-6.68595​	-7.84924​
0.171​	-6.47923​	-8.16964​	-8.16829​	-6.47931​
0.18​	-7.94091​	-6.83026​	-6.82849​	-7.93785​
0.189​	-6.57413​	-8.31416​	-8.31545​	-6.57412​
0.198​	-8.03485​	-6.98352​	-6.98118​	-8.03369​
0.207​	-6.67564​	-8.47367​	-8.47584​	-6.67647​
0.216​	-8.14223​	-7.14866​	-7.14922​	-8.14251​
0.225​	-6.78451​	-8.65006​	-8.64783​	-6.78714​
0.234​	-8.25753​	-7.32772​	-7.32931​	-8.25632​
0.243​	-6.90422​	-8.83775​	-8.83712​	-6.90543​
0.252​	-8.38217​	-7.51894​	-7.52234​	-8.38085​
0.261​	-7.03081​	-9.03559​	-9.04065​	-7.03662​
0.27​	-8.5117​	-7.72316​	-7.72717​	-8.5133​
0.279​	-7.16533​	-9.24636​	-9.25161​	-7.16931​
0.288​	-8.64948​	-7.9391​	-7.94619​	-8.65296​
0.297​	-7.30363​	-9.46503​	-9.47613​	-7.30981​
0.306​	-8.7922​	-8.16811​	-8.17483​	-8.80513​
0.315​	-7.45672​	-9.69924​	-9.71352​	-7.46289​
0.324​	-8.942​	-8.40654​	-8.41963​	-8.96027​
0.333​	-7.60934​	-9.94092​	-9.95781​	-7.62099​
0.342​	-9.10057​	-8.65206​	-8.67057​	-9.11739​
0.351​	-7.77117​	-10.1953​	-10.219​	-7.79036​
0.36​	-9.26629​	-8.91177​	-8.9372​	-9.28688​
0.369​	-7.93327​	-10.4562​	-10.4813​	-7.95696​
0.378​	-9.43438​	-9.17669​	-9.20899​	-9.46126​
0.387​	-8.10812​	-10.7279​	-10.7634​	-8.1358​
0.396​	-9.60664​	-9.45032​	-9.49002​	-9.64225​
0.405​	-8.28057​	-11.0064​	-11.0504​	-8.31953​
0.414​	-9.78445​	-9.73097​	-9.77987​	-9.82485​
0.423​	-8.46225​	-11.2882​	-11.3453​	-8.50665​
0.432​	-9.96379​	-10.0208​	-10.0819​	-10.0162​
0.441​	-8.64115​	-11.5819​	-11.6473​	-8.69969​
0.45​	-10.1481​	-10.3127​	-10.3868​	-10.2126​
0.459​	-8.82505​	-11.8787​	-11.9572​	-8.8957​
0.468​	-10.3357​	-10.6133​	-10.7037​	-10.4143​
0.477​	-9.01281​	-12.1802​	-12.2771​	-9.09797​
0.486​	-10.523​	-10.9191​	-11.0232​	-10.6142​
0.495​	-9.20344​	-12.4868​	-12.603​	-9.30472​
0.504​	-10.71​	-11.2264​	-11.3505​	-10.8226​
0.513​	-9.39272​	-12.7969​	-12.935​	-9.51052​
0.522​	-10.9021​	-11.5368​	-11.6846​	-11.0346​
0.531​	-9.57969​	-13.1109​	-13.2703​	-9.7213​
0.54​	-11.092​	-11.8488​	-12.0245​	-11.2468​
0.549​	-9.77132​	-13.4227​	-13.6114​	-9.93441​
0.558​	-11.2817​	-12.1662​	-12.3678​	-11.4633​
0.567​	-9.95729​	-13.74​	-13.9572​	-10.1507​
0.576​	-11.4674​	-12.4788​	-12.717​	-11.6787​
0.585​	-10.1457​	-14.0551​	-14.3031​	-10.37​
0.594​	-11.6531​	-12.7941​	-13.0653​	-11.8962​
0.603​	-10.3299​	-14.3699​	-14.6576​	-10.5915​
0.612​	-11.8395​	-13.1105​	-13.4183​	-12.1157​
0.621​	-10.5108​	-14.6849​	-15.0162​	-10.8123​
0.63​	-12.019​	-13.4244​	-13.776​	-12.338​
0.639​	-10.688​	-14.9959​	-15.3724​	-11.0325​
0.648​	-12.1945​	-13.7296​	-14.1342​	-12.5599​
0.657​	-10.8631​	-15.3054​	-15.7328​	-11.2517​
0.666​	-12.3674​	-14.0385​	-14.4939​	-12.7825​
0.675​	-11.0311​	-15.6071​	-16.0934​	-11.4737​
0.684​	-12.5332​	-14.3431​	-14.8562​	-13.003​
0.693​	-11.1934​	-15.9093​	-16.4558​	-11.6972​
0.702​	-12.6953​	-14.6394​	-15.2185​	-13.2233​
0.711​	-11.3508​	-16.2024​	-16.8177​	-11.9197​
0.72​	-12.8496​	-14.9297​	-15.5795​	-13.4434​
0.729​	-11.4989​	-16.4929​	-17.1819​	-12.1383​
0.738​	-12.9954​	-15.2171​	-15.941​	-13.6631​
0.747​	-11.6437​	-16.7736​	-17.5453​	-12.3557​
0.756​	-13.1303​	-15.492​	-16.3002​	-13.8849​
0.765​	-11.7823​	-17.0477​	-17.9081​	-12.5717​
0.774​	-13.2609​	-15.7594​	-16.6615​	-14.1005​
0.783​	-11.9071​	-17.3132​	-18.2668​	-12.7884​
0.792​	-13.3823​	-16.0203​	-17.0223​	-14.3156​
0.801​	-12.0235​	-17.5685​	-18.6236​	-13.0058​
0.81​	-13.4926​	-16.2702​	-17.3793​	-14.5253​
0.819​	-12.1307​	-17.8121​	-18.9818​	-13.2181​
0.828​	-13.5953​	-16.5104​	-17.7358​	-14.7375​
0.837​	-12.223​	-18.0471​	-19.3347​	-13.4258​
0.846​	-13.6849​	-16.738​	-18.0881​	-14.9456​
0.855​	-12.3088​	-18.2678​	-19.6844​	-13.6336​
0.864​	-13.7631​	-16.9568​	-18.4393​	-15.1537​
0.873​	-12.3803​	-18.4758​	-20.0346​	-13.8372​
0.882​	-13.8304​	-17.1608​	-18.7863​	-15.3566​
0.891​	-12.436​	-18.6709​	-20.3781​	-14.0378​
0.9​	-13.884​	-17.3467​	-19.1288​	-15.5579​
0.909​	-12.4877​	-18.8591​	-20.7249​	-14.2389​
0.918​	-13.9222​	-17.5228​	-19.4676​	-15.7576​
0.927​	-12.5194​	-19.0276​	-21.0615​	-14.4345​
0.936​	-13.9471​	-17.6796​	-19.8045​	-15.9524​
0.945​	-12.5377​	-19.1807​	-21.3936​	-14.6269​
0.954​	-13.9609​	-17.8276​	-20.137​	-16.1445​
0.963​	-12.5432​	-19.3169​	-21.7268​	-14.8195​
0.972​	-13.9563​	-17.9595​	-20.4627​	-16.3327​
0.981​	-12.5309​	-19.4364​	-22.0557​	-15.0065​
0.99​	-13.938​	-18.0691​	-20.7876​	-16.5172​
0.999​	-12.5019​	-19.5401​	-22.3741​	-15.1884​
1.008​	-13.9037​	-18.1641​	-21.1123​	-16.7011​
1.017​	-12.461​	-19.6257​	-22.6886​	-15.3702​
1.026​	-13.8517​	-18.2401​	-21.4244​	-16.8801​
1.035​	-12.4007​	-19.6938​	-23.0043​	-15.5475​
1.044​	-13.7828​	-18.2985​	-21.7332​	-17.0553​
1.053​	-12.3254​	-19.744​	-23.313​	-15.7247​
1.062​	-13.6994​	-18.3403​	-22.0414​	-17.2324​
1.071​	-12.2312​	-19.7713​	-23.6161​	-15.8944​
1.08​	-13.5971​	-18.3606​	-22.3418​	-17.4024​
1.089​	-12.1204​	-19.7827​	-23.9162​	-16.0649​
1.098​	-13.475​	-18.3599​	-22.639​	-17.5718​
1.107​	-11.9913​	-19.7735​	-24.2143​	-16.2326​
1.116​	-13.3385​	-18.339​	-22.9299​	-17.7404​
1.125​	-11.8434​	-19.7434​	-24.5038​	-16.3986​
1.134​	-13.1792​	-18.2984​	-23.2179​	-17.8993​
1.143​	-11.6784​	-19.6917​	-24.7883​	-16.5629​
1.152​	-13.0052​	-18.2369​	-23.5056​	-18.0642​
1.161​	-11.4922​	-19.6198​	-25.0689​	-16.723​
1.17​	-12.8087​	-18.154​	-23.7813​	-18.2234​
1.179​	-11.2896​	-19.5264​	-25.3475​	-16.8819​
1.188​	-12.5938​	-18.0493​	-24.0588​	-18.3804​
1.197​	-11.0653​	-19.4111​	-25.622​	-17.0402​
1.206​	-12.3635​	-17.9244​	-24.3304​	-18.537​
1.215​	-10.8215​	-19.2747​	-25.8899​	-17.1965​
1.224​	-12.111​	-17.7771​	-24.6​	-18.6955​
1.233​	-10.5602​	-19.1134​	-26.1558​	-17.3507​
1.242​	-11.8359​	-17.6043​	-24.8615​	-18.8504​
1.251​	-10.28​	-18.9321​	-26.4208​	-17.5082​
1.26​	-11.5431​	-17.4126​	-25.1232​	-19.0059​
1.269​	-9.97925​	-18.727​	-26.6808​	-17.6623​
1.278​	-11.2326​	-17.1989​	-25.3818​	-19.1611​
1.287​	-9.65456​	-18.4996​	-26.9354​	-17.8174​
1.296​	-10.9013​	-16.9609​	-25.6391​	-19.3155​
1.305​	-9.31432​	-18.2511​	-27.1929​	-17.9733​
1.314​	-10.5482​	-16.6981​	-25.8892​	-19.4703​
1.323​	-8.95242​	-17.9815​	-27.4441​	-18.1281​
1.332​	-10.181​	-16.4163​	-26.1446​	-19.6334​
1.341​	-8.5715​	-17.6865​	-27.6951​	-18.2836​
1.35​	-9.79032​	-16.1106​	-26.3948​	-19.7906​
1.359​	-8.17152​	-17.3694​	-27.9446​	-18.447​
1.368​	-9.37807​	-15.7837​	-26.6461​	-19.9481​
1.377​	-7.75305​	-17.0323​	-28.196​	-18.6107​
1.386​	-8.94951​	-15.4343​	-26.8917​	-20.113​
1.395​	-7.31535​	-16.6715​	-28.4453​	-18.7762​
1.404​	-8.49953​	-15.0621​	-27.1394​	-20.2802​
1.413​	-6.85616​	-16.2867​	-28.6944​	-18.9435​
1.422​	-8.03385​	-14.6698​	-27.3879​	-20.4526​
1.431​	-6.38076​	-15.8846​	-28.9436​	-19.117​
1.44​	-7.55021​	-14.2546​	-27.6388​	-20.626​
1.449​	-5.88459​	-15.4578​	-29.1943​	-19.2949​
1.458​	-7.04557​	-13.8183​	-27.8898​	-20.8064​
1.467​	-5.37274​	-15.0095​	-29.4439​	-19.4771​
1.476​	-6.52191​	-13.36​	-28.1452​	-20.9911​
1.485​	-4.84123​	-14.5401​	-29.6966​	-19.665​
1.494​	-5.98085​	-12.883​	-28.3971​	-21.1826​
1.503​	-4.29356​	-14.0524​	-29.9547​	-19.8556​
1.512​	-5.42401​	-12.3832​	-28.6542​	-21.3799​
1.521​	-3.72888​	-13.5452​	-30.215​	-20.058​
1.53​	-4.84829​	-11.8666​	-28.9164​	-21.5868​
1.539​	-3.14793​	-13.0165​	-30.4775​	-20.2652​
1.548​	-4.25739​	-11.3285​	-29.1832​	-21.7971​
1.557​	-2.55441​	-12.4689​	-30.7475​	-20.4843​
1.566​	-3.65011​	-10.7733​	-29.4497​	-22.0185​
1.575​	-1.93587​	-11.9023​	-31.0197​	-20.7052​
1.584​	-3.03405​	-10.2026​	-29.7283​	-22.248​
1.593​	-1.30776​	-11.3173​	-31.3​	-20.9405​
1.602​	-2.39821​	-9.61026​	-30.0095​	-22.4878​
1.611​	-0.66485​	-10.7194​	-31.5836​	-21.1833​
1.62​	-1.74954​	-9.00182​	-30.2988​	-22.7371​
1.629​	-8.23E-03​	-10.1035​	-31.8751​	-21.439​
1.638​	-1.08434​	-8.37575​	-30.5936​	-22.9962​
1.647​	0.659436​	-9.47366​	-32.1774​	-21.7065​
1.656​	-0.40939​	-7.73503​	-30.8959​	-23.2687​
1.665​	1.33883​	-8.828​	-32.4838​	-21.9861​
1.674​	0.276472​	-7.08151​	-31.2107​	-23.552​
1.683​	2.032478​	-8.16412​	-32.8001​	-22.2778​
1.692​	0.974602​	-6.41301​	-31.5338​	-23.8514​
1.701​	2.732724​	-7.49102​	-33.1306​	-22.5815​
1.71​	1.683185​	-5.73429​	-31.8647​	-24.1635​
1.719​	3.446644​	-6.80351​	-33.4634​	-22.8995​
1.728​	2.402617​	-5.04356​	-32.206​	-24.4867​