Jyotirmaya
Board Regular
- Joined
- Dec 2, 2015
- Messages
- 205
- Office Version
- 2019
- Platform
- Windows
I have 3 sheets in excel, Sheet1, Shee2 and Base price
In sheet1 I have values in Column A and Column B, Column A is the Product Code and Column B is the values, In column A there are multiple product codes and multiple values are assigned to it.
In Sheet 2 also I have values in Column A and Column B, Column A is the product Code and B is the values. and in this sheet in column B the sum of negative is always more than the sum of positive.
In Base price I have values in Column A and Column B, Column A is the product code and Column B is the values against the product code.
3
The result should look like this
I want a macro that the sum of positive here its 40 is to be deducted from sheet1 as compare to the values in sheet3.
So in the above example I need to deduct 40 only. I can deduct the value 40 from the product codes of 2,3,6 only as in sheet2 these 3 product codes are having negative values.
Hence it will first check the maximum negative value in Sheet2 that's -20 in B2 hence it will check Base Price sheet whats the Base price of product code 2, its 10. then it will check the sum of values product code in Sheet1, its 24(B2+B3) now I want the maximum deduction it can made by keeping the base price. As base price is 10 it will keep that and the rest amount 14 it need to deduct from sheet1 under product code 2 category. hence the code should make B2 as 0 and B3 as 10.
then it will deduct the rest amount that is 40-14 = 26 it will select the next maximum value in sheet2 that is -15 under product code 3, base price of product code 3 under base price sheet is 10, then it will check the sum of values product code in Sheet1 its 25(B4+B5+B6). It will deduct 15 maximum so B4 should be 0 and B5 should be 0 and B6 should have value 10 as base price is 10.
till now it deducted 29 from sheet1 (14 from Product code 2 and 15 from product code 3) now the balance value is 40-29 = 11. base price of value 6 is 80 and sum of values product code 6 is 100 in sheet1 and I need to deduct 11 from that hence B10 should be 89.
What should be the macro code for that ?
In sheet1 I have values in Column A and Column B, Column A is the Product Code and Column B is the values, In column A there are multiple product codes and multiple values are assigned to it.
In Sheet 2 also I have values in Column A and Column B, Column A is the product Code and B is the values. and in this sheet in column B the sum of negative is always more than the sum of positive.
In Base price I have values in Column A and Column B, Column A is the product code and Column B is the values against the product code.
Example Sheet 1 Sheet 2 and Base price sheet
3
The result should look like this
I want a macro that the sum of positive here its 40 is to be deducted from sheet1 as compare to the values in sheet3.
So in the above example I need to deduct 40 only. I can deduct the value 40 from the product codes of 2,3,6 only as in sheet2 these 3 product codes are having negative values.
Hence it will first check the maximum negative value in Sheet2 that's -20 in B2 hence it will check Base Price sheet whats the Base price of product code 2, its 10. then it will check the sum of values product code in Sheet1, its 24(B2+B3) now I want the maximum deduction it can made by keeping the base price. As base price is 10 it will keep that and the rest amount 14 it need to deduct from sheet1 under product code 2 category. hence the code should make B2 as 0 and B3 as 10.
then it will deduct the rest amount that is 40-14 = 26 it will select the next maximum value in sheet2 that is -15 under product code 3, base price of product code 3 under base price sheet is 10, then it will check the sum of values product code in Sheet1 its 25(B4+B5+B6). It will deduct 15 maximum so B4 should be 0 and B5 should be 0 and B6 should have value 10 as base price is 10.
till now it deducted 29 from sheet1 (14 from Product code 2 and 15 from product code 3) now the balance value is 40-29 = 11. base price of value 6 is 80 and sum of values product code 6 is 100 in sheet1 and I need to deduct 11 from that hence B10 should be 89.
What should be the macro code for that ?
