Find Max Valu every 10 rows &mark row as keep

Jman - Mar 23, 2010 at 07:04 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 27, 2010 at 06:45 AM

I need help creating a formula in excel to Identify the Highest and Lowest values Every 10 rows THEN Identify those two rows by placing some text or number in a blank column in the rows with the highest and lowest values. For example I want to return the word keep in Column A3 for ONLY the Highest and Lowest trade price for each 10 row incement. This way I can sort the data by Keep and delete the other 8 rows of data. This way I can keep most of my data integrity and greatly reduce the size of the file.

I am using Excel 2007 and I have a 160,000 rows of data. The purpose of the formula is to cut this data down to by 80%

The spreadsheet below gives a simple example.

                Time                 Price                 C is a blank column for formula out put
Row 1     19:30:25              121.81

Row 2     19:30:26              121.80              Keep

Row 3    19:30:29               121.83

Row 4    19:30:31              121.86                  Keep

Row 5    19:39:35              121.84

Any help would be greatly appreciated.

Thank you,

2 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Mar 24, 2010 at 01:07 AM
I am not able to get at a formula immediately. however try these macros result is in sheet 2

there are 3 macros "test","test1" and "undo"
test1 is embedded in "test". so it is enough if you run only the macro "test"
all the three macros should be in the module
macro "undo" undoes the macro results.
take a small extract of your data about 31 rows and check the macro . If the results are ok you can use the macros on your original file


The macros are

Dim r As Range, r1 As Range, x As Double, y As Double
Dim j As Integer, k As Integer
Dim r2 As Range, m As Integer

Sub test()
Range("c1") = "signal"
Set r2 = Range(Range("B1"), Range("B1").End(xlDown))
j = 1
m = 1
Set r = Cells(j * m + 1, "B")
MsgBox r.Address
Set r1 = Range(r, r.Offset(9, 0))
MsgBox r1.Address
If r.Offset(9, 0) = "" Then Exit Do
x = WorksheetFunction.Min(r1)
y = WorksheetFunction.Max(r1)
MsgBox x
MsgBox y
k = WorksheetFunction.Match(x, r2, 0)
Cells(k, "c") = "keep"
k = WorksheetFunction.Match(y, r2, 0)
Cells(k, "c") = "keep"
m = m + 10
MsgBox m
End Sub

Sub test1()
Set r = Range(Range("A1"), Range("A1").End(xlDown).Offset(0, 3))
r.AutoFilter field:=3, Criteria1:="keep"
r.Cells.SpecialCells(xlCellTypeVisible).Copy Worksheets("sheet2").Range("A2")

ActiveSheet.AutoFilterMode = False
End Sub

Sub undo()
End Sub
Thank you Venkat.

I appreciate your help and I will try your solution. I want to see how the Macro with a loop will work on 160,000 rows.

I was working with the following formula to Identify the High and Low values and the formula works quickly but I'm not able to sort the ouput easily. Basically the formula re-calculates after the sort so the sort is useless.

Take a look at the formula:


I could use the =text formula to copy the output of the main formlua to another cell as text. Then save as a CSV file and reopen. That should work. Once the file is reopened it will only have the text field and no formula since formulas are not saved in CSV format. Then I could sort the column with Keep in it and delete the rest.

It looks like your Macro will take care of everything all at once.

Thank you very much.
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Mar 27, 2010 at 06:45 AM
your formula seems to be incomplete
will you please send the complete formula. Looks like a good formula.