How to find last 3 data from maximum value

Solved/Closed
Report
Posts
3
Registration date
Wednesday November 17, 2010
Status
Member
Last seen
November 18, 2010
-
 apspeed -
Hello,
i'm trying to write a vba in excel to find out the last 3 data from the maximum value in a colunm. Anybody can help?



14 replies

Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
Hi,

For example if your data are in Cells A2 to A9, use the following formulas :

For the first max data : = LARGE(A2:A9,1)
For the second max data : =LARGE(A2:A9,2)
For the last and third max data : =LARGE(A2:A9,3)

Best regards
Highlight three cells then Ctrl-Shift and Enter this:
=LARGE(C1:C14,ROW(INDIRECT("1:3")))

Where C1:C14 is the range of values you are checking.
1:3 will return the top 3 values

This is a formula though not VBA code.
Is the column sorted in value order? Would the 'last 3 data' be together at the top or bottom of the list?
Posts
3
Registration date
Wednesday November 17, 2010
Status
Member
Last seen
November 18, 2010

Actually i need to plot a chart to show the last 3 cut of my machine.And the first thing i need is to write a marco to help me auto generate out the chart .First i copy and then paste on the worksheet where the data can be located , then sorter according to the machine id follow by number of cut .I have manage to record the macro until the filter part but i do not know how to write an vba code to determine where is the maximum value of the cell in the colunm and then from there offset 3 value up .Once i got this part , i will delete away the rest of the data so this mean the value i have will be the fina one.Then from there, i will again record the marco of ploting out the chart .
The formula I've shown will give you the top three values in the range. Uness the value you are looking for are not the top three but just the last three in the list. Can you paste an example it would help enormously?
Posts
3
Registration date
Wednesday November 17, 2010
Status
Member
Last seen
November 18, 2010

i'm looking for the last three in the list..example

Cells(1,4)= 1
Cells(2,4)=2
Cells(3,4)=3
Cells(4,4)=4
Cells(5,4)=5

When i run the marco , i would expect it would stop at "Cells(2,4)" then follow by deleting away the rest of the data except Cell (3,4),(4,4) and Cells(5,4)
Dim lastrow As Long

Let lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & lastrow - 3 & ":A2").Delete


This deletes the contents of all but the last three cells.
This should be:
Dim lastrow As Long

Let lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & lastrow - 3 & ":A2").ClearContents
What if the cell is having repeat number as below ?
Cells(1,4) 1
Cells (2,4) 1
Cells(3,4) 2
Cells(4,4) 2
Cells(5,4) 3
Cells(5,4)3
Cells(6,4) 3
Cells(7,4) 4
Cells(8,4) 4
Cells(9,4) 5
Cells(10,4) 6
You said "i'm looking for the last three in the list."
The last VBA i gave does that. Its not looking at the values just that they are the last three.
What would you expect as the result from your latest example?

The last three in the list? - 4,5,6
The three highest values in the list? - 4,4,5,6
Are the values in order or are they in random positions: e.g. 1,3,1,5,3,6,3,4,2,4
The three highest value in the list.-4,4,5,6 All the value is sorter in order as the latest example.
this has not been optimized but should give you what you need.

Sub findhighestvalues()
Dim myarray()
Dim listrange As Range
Dim LR As Long
Dim lastvalue As Long
Dim n As Long

Let n = 0
Let lastvalue = 99999
LR = Range("A" & Rows.Count).End(xlUp).Row
ReDim myarray(LR)

Set listrange = Range("A1:A" & LR)

For Each c In listrange
    If lastvalue <> c Then
        Let myarray(n) = c
        Let lastvalue = c
        Let n = n + 1
    End If
Next c

Let n = n - 3
Let lastvalue = myarray(n)

Range("A1").Select

Cells.Find(What:=lastvalue, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

Let newrownum = Selection.Row
Range("A1:" & "A" & newrownum - 1).ClearContents

End Sub
slightly improved version

Sub findhighestvalues()
Dim myarray()
Dim LR As Long
Dim lastvalue As Long
Dim n As Long
Dim c As Range

Let n = 0

Let lastvalue = 99999
LR = Range("A" & Rows.Count).End(xlUp).Row
ReDim myarray(LR)

For Each c In Range("A1:A" & LR)
    If lastvalue <> c Then
        Let myarray(n) = c
        Let lastvalue = c
        Let n = n + 1
    End If
Next c

Let lastvalue = myarray(n - 3)

Let n = 1
While Cells(n, 1) <> lastvalue
    Let n = n + 1
Wend

Range("A1:" & "A" & n - 1).ClearContents

End Sub

Thanks for the solution, i will try it out :-)
There was run time error 13 type mismatch.