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.

 

[mole999]

Like this ?

Excel Workbook
	A	B	C
1	Order ID	Product Type	Dollar Amount
2	11	Low grade	10
3	11	Low grade	20
4	11	Premium	35
5	12	Low grade	10
6	12	Low grade	20
7	12	Low grade	30
8	12	Premium	40
9	12	Premium	45
10	12	Premium	50
Sheet1

That was done using SORT three seqential filters
Order ID > Values > Smallest to Largest
Dollar Amount > Values > Smallest to Largest
Product Type . Values > Z to A

 

[Peter_SSs]

Welcome to the MrExcel board!

1. Would it matter if, for example, the first 3 rows became as below? That is, cells C2 and c4 have swapped values?

Excel Workbook
	A	B	C
1	Order ID	Product Type	Dollar Amount
2	11	Low grade	10
3	11	Premium	35
4	11	Low grade	20
Swap Values (eg)

2. Would it matter if the product Type values were rearranged? For example, like below?

Excel Workbook
	A	B	C
1	Order ID	Product Type	Dollar Amount
2	11	Low grade	10
3	11	Low grade	20
4	11	Premium	35
Swap Values (eg)

3. Do you really need a macro? Depending on your answer to 1 above, would a formula in the next column to show the new values be okay?

Excel Workbook
	A	B	C	D
1	Order ID	Product Type	Dollar Amount
2	11	Low grade	20	10
3	11	Premium	35	35
4	11	Low grade	10	20
5	12	Premium	50	50
6	12	Premium	20	45
7	12	Low grade	10	10
8	12	Low grade	40	20
9	12	Low grade	45	30
10	12	Premium	30	40
Swap Values (eg)

 

[sonic2000]

Peter,

Thanks a lot. The formula works perfect. However, it would be great if I can have it in VBA. Because I get such a data every week (~4000 rows) which I need to first fix this issue and then I have a VBA code to run and generate a report out of it.

So I want to add a code at the beginning of my macro to first fix the issue.

 

[Peter_SSs]

Peter,

Thanks a lot. The formula works perfect.

That indicates that the answer to my Q1 is that it doesn't matter about the order in the third column as the formula swaps the 10 and 20 in the first 3 rows of the sample data.

What is the answer to my Q2 above?

 

[sonic2000]

Peter,

The order is not important. It is also both possible to swap the values or swap the types.

However, I also noticed an issue with the formula you provided. Below is the sample of the real data I have. There are five different types: premium, lower grade, classic, EIP, and commissionable, which I want to fix that issue. (in case of existing other types than Premium within the same order number, the dollar amount of those shouldn't be higher than Premium. In case there are any, the value must be swapped with the premium)


[TABLE="width: 500"]
<tbody>[TR]
[TD]111[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Commissionable SIM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Lower Grade Device[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Lower Grade Device[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Commissionable SIM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Commissionable SIM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Commissionable SIM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Lower Grade Device[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Lower Grade Device[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Lower Grade Device[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]Commissionable SIM[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]Commissionable SIM[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Commissionable SIM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

*The formula doesn't work properly for the order number "444" since there are two other types, lower grade and commissionable than premium.

 

[Peter_SSs]

So, does it matter what values all the grades other than 'Premium' get, or is the only important thing that the 'Premium' get the values?

If it does matter what the others get, could you please provide the expected results for that sample data.

 

[sonic2000]

The point is that there shouldn't be any order other than premium that has higher dollar amount than the premium. So if there is any, the dollar amount should be swapped.
And the arrangement is not important.

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Type[/TD]
[TD]Amount[/TD]
[TD]Adjusted[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Commissionable SIM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]22[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Lower Grade Device[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Lower Grade Device[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]22[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Commissionable SIM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Commissionable SIM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Commissionable SIM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Premium Phones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Lower Grade Device[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Lower Grade Device[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Lower Grade Device[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Commissionable SIM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Commissionable SIM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Commissionable SIM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Would this result be acceptable?
We'll try to get the result correct before worrying about the vba.

Excel Workbook
	A	B	C	D
1	ID	Type	Amount	Adjusted
2	111	Premium Phones	50	50
3	111	Premium Phones	25	50
4	111	Premium Phones	10	25
5	111	Commissionable SIM	50	10
6	222	Premium Phones	22	50
7	222	Lower Grade Device	50	22
8	222	Lower Grade Device	50	50
9	333	Premium Phones	22	50
10	333	Commissionable SIM	50	10
11	333	Commissionable SIM	50	22
12	333	Commissionable SIM	10	50
13	444	Premium Phones	25	50
14	444	Premium Phones	25	50
15	444	Lower Grade Device	25	10
16	444	Lower Grade Device	50	10
17	444	Lower Grade Device	50	10
18	444	Commissionable SIM	10	25
19	444	Commissionable SIM	10	25
20	444	Commissionable SIM	10	25
Swap 2

 

[sonic2000]

Thanks Peter. It’s fine if there is no better way. Because it fixes the premium values issue but it also changes the values of commissionable sim as well (order ID 444) which I prefer it not to.