VBA to swap values

[sonic2000]

Hi, I have a large dataset, about 4000 rows, which I need to correct some values in case of a criteria. Below is the example of my data:

Order ID	Product Type	Dollar Amount
11	Low grade	20
11	Premium	35
11	Low grade	10
12	Premium	50
12	Premium	20
12	Low grade	10
12	Low grade	40
12	Low grade	45
12	Premium	30

<tbody>
</tbody>

In each of distinct order IDs, the dollar amount of premium products must be higher than low grade ones. As seen in my data, for order ID "11", there is no issue since premium one is larger than low grade ones: 35>20>10.

But in order ID "12", as it is seen, there are two low grade orders which have higher dollar values than two of the premium ones. To fix this issue, I must swap the value of the low grade ones (40,45) with the premium ones (20,30). So, after correction, the data should look like the following:

Order ID	Product Type	Dollar Amount
11	Low grade	20
11	Premium	35
11	Low grade	10
12	Premium	50
12	Premium	40
12	Low grade	10
12	Low grade	20
12	Low grade	30
12	Premium	45

<tbody>
</tbody>

I have similar issues with many of the order IDs in my dataset which I need a VBA code to go through all the order IDs and fix this issue if exists.

 

[mikerickson]

You could put this in row 2 and drag down to get the changed product types:
=IF(COUNTIFS($A:$A,$A2,$C:$C,">="&$C2)<=COUNTIFS($A:$A,$A2,$B:$B,"Premium*"),"Premium ","Low Grade ")&SUBSTITUTE(SUBSTITUTE(B2,"Low grade",""),"Premium","")