Vlookup with criteria

[irsmalik]

Hello Friends
I have some problems in Excel and need anybody's help. I have a file with many columns but especially I need
Drawing Number and thair Approval status.
Match Drawings Number and get its last status. Suppose

Drawing Number Rev Status
QV-00-VEN-C02-14400917-0001 0 Under Review
QV-00-VEN-C02-14400917-0001 1 Not Approve
QV-00-VEN-C02-14400917-0001 2 Approved with Comments
QV-00-VEN-C02-14400917-0001 3 Approved

I have to fill another Sheet by using Vlookup. I want to Vlookup based on Drawings and last cell of Status colums against that Drawings Number.

Excel File is attached. Thanks in advance

[c1377]

Howdy...
1) vlookup requires that you remove all empty rows from your data
2) vlookup only retrieves the 1st reponse,
...so if you want the highest revision level reported you will need to sort your data accordingly
3) even with vlookup you would be required to manually update your reference column with the exact Document name (which doesn't look like much fun)

4) a simpler solution would be to:
...copy your table to a new report tab
...sort your report
...detect & delete duplicates

5) you could even do this with a macro like so:
note: I used a button named "New_Report" to run the macro,
you will find removing this button has been commented out...

Sub NewReport()
 
' copy sheet to new tab, colour it yellow, and name it "new Report"
    Sheets("NEW EDDR").Copy Before:=Sheets(2)
    Sheets("NEW EDDR (2)").Name = "New Report"
        With ActiveWorkbook.Sheets("New Report").Tab
            .Color = 65535
            .TintAndShade = 0
        End With
 
'prepare sheet for sorting
    Cells.Select
    Cells.UnMerge
    ActiveWorkbook.Worksheets("New Report").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("New Report").Sort.SortFields.Add Key:=Range("B2:B9999"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("New Report").Sort.SortFields.Add Key:=Range("E2:E9999"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortTextAsNumbers
 
'sort sheet by document No. and Rev
        With ActiveWorkbook.Worksheets("New Report").Sort
            .SetRange Range("A1:L326")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
 
'Remove Document No. rows that have a Higher Revision Level
    Columns("B:B").Select
    ActiveSheet.Range("$A$1:$K$9999").RemoveDuplicates Columns:=2, Header:=xlYes
 
'Sort Data by "DSCP"
    Cells.Select
    ActiveWorkbook.Worksheets("New Report").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("New Report").Sort.SortFields.Add Key:=Range("D2:D9999"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("New Report").Sort.SortFields.Add Key:=Range("E2:E9999"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("New Report").Sort.SortFields.Add Key:=Range("B2:B9999"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("New Report").Sort
            .SetRange Range("A1:L326")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
 
'Hide Columns & Clean Up
    Columns("A:A").EntireColumn.Hidden = True
    Columns("F:J").EntireColumn.Hidden = True
    ActiveWindow.Zoom = 100
'    ActiveSheet.Shapes.Range(Array("New_Report")).Delete
    Columns("C:C").VerticalAlignment = xlTop
    Range("B1").Select
End Sub