Taking the right amount out of more than one condition

[JimmyBambo]

Hi friends,
I took picture of my problem as it is easier for me to explain on it, as I cant fully solve this problem.
So I have to determine percentage of discounts (F row) according to conditions in table on right side.
1) For different suppliers I have different discount rates on different dates,
2) For some products of suppliers I have also different discount rates.
So for example, if producer is Coca Cola, product is Coca Cola, date of invoice is 28.01.2018, discount is 5%. If it was product Fanta or Sprite, discount would be 7%.
I hope so that this is solvable, thank you in advance

 

[JimmyBambo]

have you named the lower table as mytable2
if you think you have use insert name , mytable2, ok then delete it then rename it

Thank you, I forgot to change table name, now it works.
One more (and I hope so last question regarding this problem), I need to resolve problem with "buyer discount", where I will have 20 biggest buyers (in this example I have only 3 big, and one which I will put in "others"), and "other buyers", where will be every other buyer who is not named on list (in this case, that will be "R" buyer, but in real case it will be more than 100 buyers).
Buyer discounts are also linked to different dates.

buyer	supplier	prod	date	dis		helper1	Buyer discount
CS	COC	coca	1/28/2018	5		COC	4
CS	COC	fan	7/23/2018	8		COCfan				column K					column P				column T								column AB
CS	PEP	pepsi	8/16/2018	9		PEP
CS	NES	nesc	11/21/2018	3		NES						mytable
FF	PEP	7up	2/5/2018	10		PEP
FF	COC	ccz	5/1/2018	6		COC										other buyers	CS	FF	GS
FF	NES	nesg	8/30/2018	6		NESnesg							1	2	3	1	2	3	1	2	3	1	2	3	1	2	3
R	PEP	mir	3/15/2018	8		PEPmir				helper	supplier	product	4/30/2018	8/25/2018	9/28/2018	3/31/2018	9/30/2018	11/30/2018	2/28/2018	5/31/2018	8/12/2018	1/31/2018	5/31/2018	9/25/2018	1/31/2018	5/31/2018	9/25/2018
R	COC	spr	6/30/2018	6		COCspr		row 10		COC	COC		5	6	5	4	5	6	4	4	5	3	5	5	5	3	5
R	COC	coca	7/15/2018	6		COC				COCfan	COC	fan	7	8	8	6	7	7	6	6	6	5	7	6	7	5	6
R	NES	nese	9/29/2018	3		NES				COCspr	COC	spr	6	6	7	5	4	6	5	4	5	4	5	5	6	3	5
R	NES	nesa	10/31/2018	3		NES				NES	NES		4	2	3	3	1	3	3	0	2	2	1	2	4	-1	2
GS	COC	spr	3/13/2018	6		COCspr				NESnes	NES	nes	3	2	3	1	1	2	2	0	1	0	1	1	3	-1	1
GS	PEP	mir	8/23/2018	8		PEPmir				NESnesg	NES	nesg	5	6	6	3	4	5	4	4	4	2	5	4	5	3	4
GS	NES	neso	11/15/2018	12		NESneso				NESneso	NES	neso	10	10	12	8	7	9	9	8	8	7	9	8	10	7	8
										PEP	PEP		10	9	11	8	8	8	9	7	7	7	8	7	10	6	7
										PEPmir	PEP	mir	8	8	9	7	6	6	7	6	5	6	7	5	8	5	5	row 18
													mytable2	Column1
													1/1/2018	1
													5/1/2018	2
													9/29/2018	3
													12/31/2018	3

<tbody>
</tbody>

 

[oldbrewer]

are biggest buyers defined by you or is it the top 20 by sales value (ie the top 20 is dynamic) . You already calculate discount by product and by date, so you could give the top 20 an extra 1% discount ????

 

[JimmyBambo]

are biggest buyers defined by you or is it the top 20 by sales value (ie the top 20 is dynamic) . You already calculate discount by product and by date, so you could give the top 20 an extra 1% discount ????

I will define 20 biggest buyers manually
I will also put manually discount per supplier, product and by date. It will be variable from buyer to buyer, and different for different dates. These numbers for discounts are put randomly.
So I will have 20 biggest buyers, and 1 column for other buyers.
Every big buyer will have their discount.
Discounts could be changed three times in year, so they should be linked with date of invoice.

 

[oldbrewer]

How about a list of all buyers in first column and their discount status in column 2 - this will act as a lookup table so discounts will be applied correctly ? (in your main table)

 

[JimmyBambo]

How about a list of all buyers in first column and their discount status in column 2 - this will act as a lookup table so discounts will be applied correctly ? (in your main table)

Can you show me in short line how did you imagine that?
As I already said, this is just example table, real table have more than 200 suppliers, will it make problem for your idea?

 

[oldbrewer]

					highdisc
					supplier1	1
					supplier2	1
					supplier3	1
					supplier4	1.01
					supplier5	1
					supplier6	1
					supplier7	1.01
					supplier8	1
					supplier9	1.01
					supplier10	1
		let us say just 3 suppliers, 4 7 and 9 get higher discounts
		let us say they get an extra 1%
		now in your main table you calculate
		discount according to product sold
		just look up in this table to see if a particular
		supplier gets higher discount
		so you calculate discount as before
		and multiply it by
		in the case of supplier 4
		=VLOOKUP(supplier4,highdisc,2)

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[JimmyBambo]

		now in your main table you calculate
		discount according to product sold
		just look up in this table to see if a particular
		supplier gets higher discount
		so you calculate discount as before
		and multiply it by
		in the case of supplier 4
		=VLOOKUP(supplier4,highdisc,2)

<tbody>
</tbody>

Good idea, but I can not use it in my case, because discount is not proportional.
For example, one buyer will have bigger discount on cola, but not on pepsi, and second one will have bigger discount on nescafe, but lower on coca cola. Discount for buyers are not related with discount which I get from suppliers.
So basically I had to put in separate rows discount for 20 buyers, and other buyers will have same discount in one row. The reason why I am putting discount in rows is because supplier and products are in columns, so I just expand current table.

 

[oldbrewer]

before I retired I was a business improvement leader for a major company and saw many examples of bolt on tweaks to systems - we recommended a complete redesign. In your case say you have just 2 customers bill and fred and they each buy only cola, pepsi and nescafe. You need a look up table as below

fredcola.......4
fredpepsi.....3.7
frednescafe.....7.2
billpeps1.......6.5
billcola........4.75
bill nescafe 7.77

the beauty of this is you can tweak the percentages in this table any time you like

if you have hundreds of customers you can assign each one a discount rating of D1 D2 D3 which can also be changed whenever you want

 

[JimmyBambo]

You're welcome

Hi MickG,
any code for this problem?
Discount from buyer:
- 20 manually defined buyers will have their discounts
- All other buyers which are not on listed will have same discount (under "other buyers")
other problem are similar to previous problem.
If it is possible to solve with code, maybe it will be easier than with formula.
I really appreciate your hard work

https://www.dropbox.com/s/tvoxybq81pepmo6/Report (1).xlsx?dl=0

 

[oldbrewer]

customer	discountADJUST	product	DISCOUNT	TODAY =	18/12/2018
alan	0	pepsi	2.5
bill	0	cola	3.5						1	2	3
colin	1.5	nescafe	6						01/10/2018	01/01/2019	01/04/2019
dave	0	pepsi	2.5						31/12/2018	31/03/2019	30/06/2019
ed	0.8	cola	4.3					pepsi	2.5	2.7	2.9
fred	0.6	nescafe	5.1					cola	3.5	3.8	4			MYTABLE
george	0	pepsi	2.5					nescafe	4.5	4.6	4.7
harry	0	cola	3.5											01/01/2018	1
ian	0	nescafe	4.5											31/12/2018	1
														31/03/2019	2
														30/06/2019	3
					with this approach you can change both the amounts in the regular discount table
					and the discount adjust amounts
					eg you could make fred a 0 and make bill a 0.7

<colgroup><col><col><col><col><col><col><col span="3"><col span="3"><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>