VBA to swap values

S

sonic2000

New Member
Joined
Apr 7, 2018
Messages
5
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Like this ?
Excel Workbook
ABC
1Order IDProduct TypeDollar Amount
211Low grade10
311Low grade20
411Premium35
512Low grade10
612Low grade20
712Low grade30
812Premium40
912Premium45
1012Premium50
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
 
Upvote 0
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
ABC
1Order IDProduct TypeDollar Amount
211Low grade10
311Premium35
411Low grade20
Swap Values (eg)



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

Excel Workbook
ABC
1Order IDProduct TypeDollar Amount
211Low grade10
311Low grade20
411Premium35
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
ABCD
1Order IDProduct TypeDollar Amount
211Low grade2010
311Premium3535
411Low grade1020
512Premium5050
612Premium2045
712Low grade1010
812Low grade4020
912Low grade4530
1012Premium3040
Swap Values (eg)
 
Upvote 0
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.
 
Upvote 0
sonic2000 said:
Peter,

Thanks a lot. The formula works perfect.
Click to expand...
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
Would this result be acceptable?
We'll try to get the result correct before worrying about the vba.

Excel Workbook
ABCD
1IDTypeAmountAdjusted
2111Premium Phones5050
3111Premium Phones2550
4111Premium Phones1025
5111Commissionable SIM5010
6222Premium Phones2250
7222Lower Grade Device5022
8222Lower Grade Device5050
9333Premium Phones2250
10333Commissionable SIM5010
11333Commissionable SIM5022
12333Commissionable SIM1050
13444Premium Phones2550
14444Premium Phones2550
15444Lower Grade Device2510
16444Lower Grade Device5010
17444Lower Grade Device5010
18444Commissionable SIM1025
19444Commissionable SIM1025
20444Commissionable SIM1025
Swap 2
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Locked
  • Question Question
Sampling of data using macros
Replies
1
Views
344
Peter_SSs
Peter_SSs
S
  • Question Question
Sampling of data using formula or macros
2
Replies
18
Views
978
Fluff
Fluff
BigPaws
  • Question Question
VBA to create new worksheets from a template, populate the new worksheet by copying data from another sheet to specific locations.
Replies
0
Views
196
BigPaws
BigPaws
D
  • Solved
highs and lows
2
Replies
15
Views
384
felixstraube
F
S
  • Question Question
VBA for Userform to add data values
Replies
0
Views
601
Strycure12
S

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top