Delete rows where column A has duplicates based on values in Column B

Hi,

I am new to this forum and I am trying to acheive something in excel vba with no luck. I have explained the issue below. Appreciate your inputs.

when there is a duplicate in Column A , the column B values of those rows should be compared the row with high priority value should be retained and the low priority one has to be deleted. For Example,

Column A Column B

1 -------> Critical
2 ----------> High
3 -----------> Medium
4 ----------> Low
5 -----------> Low
1 ---------> High
1 --------> Medium
1--------->Low
2 --------> Low
2--------->Medium
4 ---------> High


in the above example, Column A has the value "1" with many values in corresponding column B. When executed, it should retain

1 -------> Critical
2 ----------> High
3 -----------> Medium
4 ---------> High
5 -----------> Low
The priority Order of column B values are

1) Crictical - Priority 1
2) High - Priority 2
3) Medium - Priority 3
4) Low - - Priority 4

Hence, the duplicates in Column A will be checked for the priority and the high priority rows are retained and the other duplicate rows should be deleted.

FYI, The sheet is sorted by Column A

Thanks for the help.

Cheers,
Raj

Hi,

I am posting both before execution and after execution sheets along

Hi,

Could you explain me how this syntax works. How will it apply the priority on the Column "B" values

Hi,

The problem is the values in Column B would necessarily not be the same values. Lets say if a new value called "Pending" is there for the same ID with another status "Low", the pending row should stay and the "Low" should go. In other words, they values in Column B are maximum 4 or values with prority 1 to Priority 5. Based on the available values in the sheet, the row with the highest priority should stay