Remove duplicates by condition

Hello, Can anyone help me (if this is even possible) to remove duplicates conditionally? Here is what I have:

A workbook that I imported data from my system at work. In column A there are order numbers, and column B are codes for things that happened within that order.

I only want 1 code per order, and there are many duplicates. However, I want certain codes to take precedence.

Example: Work order # 347090 has 3 entries. L06, L15, and W21. I want to remove the 2 entries that start with L, and keep the W21.

There are many example like this. On page 2 of the workbook I have a list of "exceptions" - meaning I want to see those codes and not necessarily remove them.

So I guess the rule would be:

Remove duplicate rows based on column A unless column B is equal to an exception code (which are defined on page 2 - column A)

There may be another part to this. If this first part is successful, you will see some order duplicates that remain. Some of them will have the same exception code listed twice. Example: Order 347674 has 12 entries - ALLL 12 of them are exception codes. But I only need 1, and it doesnt matter which 1 I keep. So if I had to express part 2 in terms of logic I would say:

Once the previous rule is complete - For remaining duplicate values in column A where ONLY exception codes remain - keep the most recent (Columns C and D)

As I type this I'm leaning towards this needing a macro, or VBA. If so, please let me know so it can move to the proper forum.

Thank you in advance for any and all help!

First of all, thank you for this. I don't know much about power query, a little intimidated by it. However, I don't think this worked.

example: Sales Order # 351866 - This order does NOT have a duplicate value. Yet it has been removed from the new "Merge1" list. Its reason code is "S17"
also 352162 is not showing either. It is NOT a duplicate, but its reason code is not on the exception list.

It looks like the power query removed all codes that weren't on the list. I apologize if I did a poor job explaining, I would like ONLY duplicates removed. If there is a solo order number, that can stay regardless of the reason code. Is there a way to adjust it so that happens?

Thanks again!

Hello!

I tried to do it with power query, hope it works

regards,
alan