Excel VBA question

[hammermaff]

Hello all,
This is my first post. I usually just search the forum, if i have a problem, but i am stumped with this one. Any help would be very much appreciated.

Here is my problem

I have a set of data comprising of engine test results and i need to delete some rows.
Basically, i have an engine No. in column A, an "operation count" (the number of the test performed on the engine, this can range from 1 to 8) in column E, and a fault description in column i.
I need to find any duplicate engine numbers
Then check within these results to find any duplicate "operation count" numbers.
Finally, if there are duplicate "operation count" numbers. check the fault details, keep the highest priority fault and delete the other rows
So i can be left with duplicate engine numbers, but they must have unique "operation count" numbers.

The data is sorted in descending order, first by engine Number., then "operation count" number.

Does anybody know if it possible either using, preferably VBA or a combination of VBA and excel functions to achieve this?

Thank you in advance for Any help anybody can give me.


Maff

 

[patel45]

please upload a sample file (with current data and desired result) on a sharing site, then paste the link here

 

[hammermaff]

Sorry, will this work?

engine data | Flickr - Photo Sharing!

Basically the data highlighted in yellow i need to delete rows based on the fault priority,
The data highlighted green is OK.

Many thanks

Maff

 

[patel45]

I need xlsx file for testing, not image

 

[hammermaff]

What sharing site do you recommend for uploading an excel file?

Maff

 

[hammermaff]

I think this might work:

Wikisend: free file sharing service data.xlsx

 

[patel45]

Finally, if there are duplicate "operation count" numbers. check the fault details, keep the highest priority fault and delete the other rows

Where can I see the highest priority ?

 

[hammermaff]

Sorry mate, they're not on the sheet at the moment because the sorting is done manually.
but the list is:
highest priority first
RunT
RunT_DCRemoved
GalleryPressure
BRKAWAY_TORQ
PistProbe (any)
Derivative

 

[patel45]

I think it's possible if you add a column with priority level number, do you want the result in sheet2 ?

 

[samar123]

First make a column for rank of fault column(i) as per the priority and suppose that this column in column(j). Suppose runT has higest priority and is ranked 1. First we will do autofiler which is not equal to 1. Then we will delete all the duplicate values which are not filtered. The program is as follows: Sub test() Dim rng As Range Set rng = Sheets("sheet1").Range("a2:i" & range("a1").end(xldown).row) 'Replace sheet1 with your sheet name Sheet1.Rows(1).AutoFilter field:=10, Criteria1:="<>1" 'Replace field number 10 from the column number where fault rank is listed rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete Sheet1.AutoFilterMode = False End Sub This will leave all the enteries with rank 1 and delete all other duplicate entries