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.

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

 

[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]

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]highdisc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]supplier1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]supplier2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]supplier3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]supplier4[/TD]
[TD="align: right"]1.01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]supplier5[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]supplier6[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]supplier7[/TD]
[TD="align: right"]1.01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]supplier8[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]supplier9[/TD]
[TD="align: right"]1.01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]supplier10[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]let us say just 3 suppliers, 4 7 and 9 get higher discounts[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]let us say they get an extra 1%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]now in your main table you calculate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]discount according to product sold[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]just look up in this table to see if a particular[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]supplier gets higher discount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]so you calculate discount as before [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]and multiply it by[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]in the case of supplier 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]=VLOOKUP(supplier4,highdisc,2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JimmyBambo]

[TABLE="width: 640"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]now in your main table you calculate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]discount according to product sold[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]just look up in this table to see if a particular[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]supplier gets higher discount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]so you calculate discount as before[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]and multiply it by[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]in the case of supplier 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]=VLOOKUP(supplier4,highdisc,2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="width: 1209"]
<colgroup><col><col><col><col><col><col><col span="3"><col span="3"><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]customer[/TD]
[TD]discountADJUST[/TD]
[TD]product[/TD]
[TD]DISCOUNT[/TD]
[TD]TODAY =[/TD]
[TD="align: right"]18/12/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]alan[/TD]
[TD]0[/TD]
[TD]pepsi[/TD]
[TD="align: right"]2.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bill[/TD]
[TD]0[/TD]
[TD]cola[/TD]
[TD="align: right"]3.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]colin[/TD]
[TD]1.5[/TD]
[TD]nescafe[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/10/2018[/TD]
[TD="align: right"]01/01/2019[/TD]
[TD="align: right"]01/04/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dave[/TD]
[TD]0[/TD]
[TD]pepsi[/TD]
[TD="align: right"]2.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31/12/2018[/TD]
[TD="align: right"]31/03/2019[/TD]
[TD="align: right"]30/06/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ed[/TD]
[TD]0.8[/TD]
[TD]cola[/TD]
[TD="align: right"]4.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]pepsi[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]2.7[/TD]
[TD="align: right"]2.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD]0.6[/TD]
[TD]nescafe[/TD]
[TD="align: right"]5.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cola[/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]3.8[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]MYTABLE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]george[/TD]
[TD]0[/TD]
[TD]pepsi[/TD]
[TD="align: right"]2.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]nescafe[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]4.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]harry[/TD]
[TD]0[/TD]
[TD]cola[/TD]
[TD="align: right"]3.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ian[/TD]
[TD]0[/TD]
[TD]nescafe[/TD]
[TD="align: right"]4.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31/12/2018[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31/03/2019[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30/06/2019[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]with this approach you can change both the amounts in the regular discount table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]and the discount adjust amounts[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]eg you could make fred a 0 and make bill a 0.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]