How to find the last value with specific conditions?

Question

Sheet 1 column A has the following values (it has around 3000 records. I’ve given the below sample values). I need to find the last value of a specific text.

RVT-01
RVT-02
RVT-03
RVT-04
RVT-05
RVT-06
RHT-01
RHT-02
RHT-03
RHT-04
RHT-05
ROI-01
ROI-02
ROI-03
SWO-01
SWO-02
SWO-03
SOR-01
SOR-02
SOR-03
SOR-04
SOR-05
SOR-06
SOR-07

Using VBA code

If enter short tex in sheet1.cells(2,2) = SWO , I need the last value in sheet1.cells(2,4)=SWO-03
If I enter sheet1.cells(2,2) = RHT , I need the last value in sheet1.cells(2,4)=RHT-05
If I enter sheet1.cells(2,2) = RVT , I need the last value in sheet1.cells(2,4)=RVT-06
If I enter sheet1.cells(2,2) = SOR , I need the last value in sheet1.cells(2,4)=SOR-07

What would be the VBA code for the above process?

read through a loop and have a variable in it.. – Methew Mar 25 '13 at 13:03 — Methew, Mar 25 '13 at 13:03
If you add a helper column, you don't need VBA. – Skip Intro Mar 25 '13 at 13:43 — Skip Intro, Mar 25 '13 at 13:43
@SkipIntro, something like the below? – Our Man in Bananas Mar 25 '13 at 14:33 — Our Man in Bananas, Mar 25 '13 at 14:33

score 1 · Answer 1 · edited May 23 '17 at 11:57

As Skip Intro suggested, there is no need for VBA: in Column B, put a formula like this:

=IF(IF(LEFT(A1,3)=LEFT(A2,3),1,0)=0,RIGHT(TRIM(A:A),2),"") (to get the just the max number):

or

=IF(IF(LEFT(A1,3)=LEFT(A2,3),1,0)=0,A:A,"") (to get the complete contents of the cell)

Both will show you the highest values. Then you could AutoFilter that column, hiding the blanks and voila :)

Or

=IF(IF(LEFT($A1,3)=LEFT($A2,3),1,0)=0,NA(),"")

will enable you to use SpecialCells in VBA to get a range that you can interrogate for the maximum values in each group, as below:

Sub test()

    Dim rng As Range
    Dim cell

    Range("B1:B" & Range("A65536").End(xlUp).Row).Formula = "=IF(IF(LEFT($A1,3)=LEFT($A2,3),1,0)=0,NA(),"""")"
    Set rng = Range(Range("B1:B" & Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).Offset(0, -1).Address)

    For Each cell In rng
        Debug.Print cell.Address & " =" & cell.Value
        MsgBox cell.Address & " =" & cell.Value
    Next
End Sub

For more information on the SpecialCells magic tricks, see How to delete multiple rows without a loop in Excel VBA.

I also recommend you to take a look at [Charley Kyd ExcelUser - Use SUMPRODUCT to Find The Last Item in an Excel List](http://exceluser.com/explore/last-item-in-list.htm) — Our Man in Bananas, Mar 25 '13 at 16:33
hi Philip, thanks for your help, i've try to execute the code , i am getting the result as below , Run-time error '1004'; applicatio-defined or object-defined error,, what is the issue?, i did not modify your code , (i've tried ms-2010 excel vba) — user2075655, Mar 26 '13 at 07:38

How to find the last value with specific conditions?

Using VBA code

1 Answers1