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

[[email protected]]

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

[:) Sixthsense :)]

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

[[email protected]]

Hi,

I am posting both before execution and after execution sheets along

[:) Sixthsense :)]

Try the below code...

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[[email protected]]

Hi,

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

[:) Sixthsense :)]

It will sort the data based on column-B A-Z and Column-A A-Z and removes the duplicates from it.

[[email protected]]

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

[:) Sixthsense :)]

Sorry I can't help you should have shown the sample data with different permutations while posting the sample data.

I hope someone will take care of your new requirement.

[kalak]

try this code
note that for your sample data it gives a different result for one value (medium instead of your low for ID INC000000563322), but the error in that case seems to be yours rather than mine
to add any more priority criteria, put them in the correct order in the array p

Please Login or Register  to view this content.