Hello
I need macro if it's possible because I have big data for each sheet .
I want to match sheet DECREASE for column D with IN sheet for column D ,if the ID is matched , then should search low price in column G in DECREASE sheet based on DATE(TODAY) ,then should create report as I did it in DIFFERENCES sheet.
should subtract column G in DECREASE sheet from column G in IN sheet and the column H=F*G as I did it by formulas without showing formulas .
should update DIFFERENCES sheet. when update DECREASE sheet .
just search for low price in column G in DECREASE sheet with comparison INV sheet .
before
expected result
I need macro if it's possible because I have big data for each sheet .
I want to match sheet DECREASE for column D with IN sheet for column D ,if the ID is matched , then should search low price in column G in DECREASE sheet based on DATE(TODAY) ,then should create report as I did it in DIFFERENCES sheet.
should subtract column G in DECREASE sheet from column G in IN sheet and the column H=F*G as I did it by formulas without showing formulas .
should update DIFFERENCES sheet. when update DECREASE sheet .
just search for low price in column G in DECREASE sheet with comparison INV sheet .
df | ||||||||
---|---|---|---|---|---|---|---|---|
C | D | E | F | G | H | |||
1 | ITEM | ID | ORDER NO | QTY | PRICE | AMOUNT | ||
2 | 1 | ATTR001 | OR00 | 10 | 100 | 1000 | ||
3 | 2 | ATTR002 | OR00 | 10 | 120 | 1200 | ||
4 | 3 | ATTR003 | OR00 | 10 | 130 | 1300 | ||
5 | 4 | ATTR004 | OR00 | 10 | 140 | 1400 | ||
IN |
df | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | DATE | NAME | INVOICE NO | ID | ORDER NO | QTY | PRICE | AMOUNT | ||
2 | 01/06/2023 | Mussaila1 | INA00 | ATTR001 | OR00 | 10 | 100 | 1000 | ||
3 | 01/06/2023 | Mussaila1 | INA00 | ATTR002 | OR00 | 10 | 120 | 1200 | ||
4 | 01/06/2023 | Mussaila1 | INA00 | ATTR003 | OR00 | 10 | 130 | 1300 | ||
5 | 01/06/2023 | Mussaila1 | INA00 | ATTR004 | OR00 | 10 | 140 | 1400 | ||
6 | 02/06/2023 | Mussaila2 | INA01 | ATTR001 | OR01 | 5 | 90 | 450 | ||
7 | 02/06/2023 | Mussaila2 | INA01 | ATTR002 | OR02 | 10 | 100 | 1000 | ||
8 | 02/06/2023 | Mussaila1 | INA02 | ATTR004 | OR00 | 10 | 140 | 1400 | ||
DECREASE |
before
df | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | DATE | NAME | INVOICE NO | ID | ORDER NO | QTY | PRICE | AMOUNT | ||
2 | ||||||||||
3 | ||||||||||
DIFFERENCES |
expected result
df | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
2 | 02/06/2023 | Mussaila2 | INA01 | ATTR001 | OR01 | 5 | -10 | -50 | ||
3 | 02/06/2023 | Mussaila2 | INA02 | ATTR002 | OR02 | 10 | -20 | -200 | ||
DIFFERENCES |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G3 | G2 | =DECREASE!G6-IN!G2 |
H2 | H2 | =F2*G2 |
H3 | H3 | =G3*F3 |