Excel VBA question

hammermaff

New Member
Joined
Feb 22, 2013
Messages
6
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

patel45

Well-known Member
Joined
Jul 15, 2012
Messages
1,953
please upload a sample file (with current data and desired result) on a sharing site, then paste the link here
 

hammermaff

New Member
Joined
Feb 22, 2013
Messages
6

ADVERTISEMENT

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

Maff
 

patel45

Well-known Member
Joined
Jul 15, 2012
Messages
1,953

ADVERTISEMENT

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

New Member
Joined
Feb 22, 2013
Messages
6
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

Well-known Member
Joined
Jul 15, 2012
Messages
1,953
I think it's possible if you add a column with priority level number, do you want the result in sheet2 ?
 

samar123

New Member
Joined
Jul 14, 2013
Messages
1
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,900
Messages
5,598,759
Members
414,257
Latest member
Vlookup_Er_No

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top