mickeystanford_alumni
Board Regular
- Joined
- May 11, 2022
- Messages
- 129
- Office Version
- 2021
- Platform
- Windows
- MacOS
Dear guys,
Hope you are good.
I want to convert some data with a few conditions. My tables are below. Table 1 is how I have the data. Table 2 is how I want the data to look like (adding a few conditions).
Basically, I want to first split the row and duplicate if the Origin has two countries. I created this code, however, I would like the origin to also be splitted. If Row1, USA+Mexico; Row2, USA; ROW3, Mexico.
Count = Len(WS6.Range("E" & R).Value) - Len(Replace(WS6.Range("E" & R).Value, "+", ""))
If Count = 1 Then
Rows(R + 1).Insert Shift:=xlDown
Rows(R + 1).FillDown
spl = True
MyLastRow2 = MyLastRow2 + 1
Then, once I have the rows splitted, I want to tell excel to apply some conditions.
1. If origin has 2 countries (no matter what country), then give to USA, 2,500 of the sales and the rest of sales to Mexico. I always have in the origin USA+Mexico, so that might be easier for the code. Although the quantity changes.
2. For this quantity change then I want to apply:
2.1. If the rest of sales is lower than 800, then 60% of the times put 0, and 40% of the times put the rest of sales.
2.2. If the rest of sales is between 800 and 1200, 30% of the times put 0, and 70% of the times put rest of sales.
2.3. If the rest of sales is higher than 1,200, then 100% of the times put rest of sales.
TABLES
DESIRED OUTCOME
THANK YOU.
Hope you are good.
I want to convert some data with a few conditions. My tables are below. Table 1 is how I have the data. Table 2 is how I want the data to look like (adding a few conditions).
Basically, I want to first split the row and duplicate if the Origin has two countries. I created this code, however, I would like the origin to also be splitted. If Row1, USA+Mexico; Row2, USA; ROW3, Mexico.
Count = Len(WS6.Range("E" & R).Value) - Len(Replace(WS6.Range("E" & R).Value, "+", ""))
If Count = 1 Then
Rows(R + 1).Insert Shift:=xlDown
Rows(R + 1).FillDown
spl = True
MyLastRow2 = MyLastRow2 + 1
Then, once I have the rows splitted, I want to tell excel to apply some conditions.
1. If origin has 2 countries (no matter what country), then give to USA, 2,500 of the sales and the rest of sales to Mexico. I always have in the origin USA+Mexico, so that might be easier for the code. Although the quantity changes.
2. For this quantity change then I want to apply:
2.1. If the rest of sales is lower than 800, then 60% of the times put 0, and 40% of the times put the rest of sales.
2.2. If the rest of sales is between 800 and 1200, 30% of the times put 0, and 70% of the times put rest of sales.
2.3. If the rest of sales is higher than 1,200, then 100% of the times put rest of sales.
TABLES
COMPANY | SALES QUANTITY | ORIGIN | DESTINATION |
APPLE | 4,000 | USA+MEXICO | EUOROPE |
DESIRED OUTCOME
COMPANY | SALES QUANTITY | ORIGIN | DESTINATION |
APPLE | 2,500 | USA | EUROPE |
APPLE | 1,500 | MEXICO | EUROPE |
THANK YOU.