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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Order ID[/TD]
[TD]Product Type[/TD]
[TD]Dollar Amount[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Low grade[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Premium[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Low grade[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Premium[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Premium[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Low grade[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Low grade[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Low grade[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Premium[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]


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:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Order ID[/TD]
[TD]Product Type[/TD]
[TD]Dollar Amount[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Low grade[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Premium[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Low grade[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Premium[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Premium[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Low grade[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Low grade[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Low grade[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Premium[/TD]
[TD]45[/TD]
[/TR]
</tbody>[/TABLE]


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","")