how to find last 3 data from maximum valueSolved/Closed

Question

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?



Configuration: Windows XP / Internet Explorer 8.0

aquarelle · Answer

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

RayH · Answer

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.

RayH · Answer

Is the column sorted in value order? Would the 'last 3 data' be together at the top or bottom of the list?

apspeed · Answer

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 .

RayH · Answer

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?

apspeed · Answer

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)

RayH · Answer

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.

Apspeed · Answer

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

RayH · Answer

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

apspeed · Answer

The three highest value in the list.-4,4,5,6 All the value is sorter in order as the latest example.

RayH · Answer

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

RayH · Answer

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

apspeed · Answer

Thanks for the solution, i will try it out :-)

apspeed · Answer

There was run time error 13 type mismatch.

How to find last 3 data from maximum value

14 responses

Subscribe To Our Newsletter!