Taking the right amount out of more than one condition

J

JimmyBambo

New Member
Joined
Dec 8, 2018
Messages
30
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 :beerchug:

jTyGvDN
V57Xzxg.png
 
Sort by date Sort by votes
I am watching this what you made, and it looks like it was exactly what I was searching for.
Can you give me explanation how did you get helper1 values?
And what formula did you use for discount column?


I would really appreciate if you can share whole file with me so I can see it and try to implement it in my format.


Thank you. :beerchug::beerchug:
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
If your happy to use some code, try this for results in column "F".
Code: 
[COLOR="Navy"]Sub[/COLOR] MG08Dec53
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("H3"), Range("H" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False

[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Txt = Dn.Value & Dn.Offset(, 1).Value
    [COLOR="Navy"]Set[/COLOR] .Item(Txt) = Dn
[COLOR="Navy"]Next[/COLOR]

[COLOR="Navy"]Set[/COLOR] Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
Txt = ""
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
  [COLOR="Navy"]For[/COLOR] n = 0 To 1
   Txt = IIf(n = 0, Dn.Value & Dn.Offset(, 1).Value, Dn.Value)
        [COLOR="Navy"]If[/COLOR] .exists(Txt) [COLOR="Navy"]Then[/COLOR]
           [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] True
                [COLOR="Navy"]Case[/COLOR] Dn.Offset(, 2).Value > Range("L2").Value
                Dn.Offset(, 4).Value = Format(Cells(.Item(Txt).Row, "L"), "0.00%")
                
                [COLOR="Navy"]Case[/COLOR] Dn.Offset(, 2).Value <= Range("L2") _
                And Dn.Offset(, 2).Value > Range("K2")
                Dn.Offset(, 4).Value = Format(Cells(.Item(Txt).Row, "L"), "0.00%")
            
                [COLOR="Navy"]Case[/COLOR] Dn.Offset(, 2).Value <= Range("K2") _
                And Dn.Offset(, 2).Value > Range("J2")
                Dn.Offset(, 4).Value = Format(Cells(.Item(Txt).Row, "K"), "0.00%")

                [COLOR="Navy"]Case[/COLOR] Dn.Offset(, 2).Value <= Range("J2").Value
                Dn.Offset(, 4).Value = Format(Cells(.Item(Txt).Row, "J"), "0.00%")
           [COLOR="Navy"]End[/COLOR] Select
           [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn

[COLOR="Navy"]End[/COLOR] With
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
[TABLE="width: 1244"]
<colgroup><col span="3"><col><col span="9"><col span="3"><col span="3"></colgroup><tbody>[TR]
[TD]buyer[/TD]
[TD]supplier[/TD]
[TD]prod[/TD]
[TD]date[/TD]
[TD]dis[/TD]
[TD][/TD]
[TD]helper1[/TD]
[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="align: right"]28/01/2018[/TD]
[TD="align: right"]5[/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][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CS[/TD]
[TD]COC[/TD]
[TD]fan[/TD]
[TD="align: right"]23/07/2018[/TD]
[TD="align: right"]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]
[/TR]
[TR]
[TD]CS[/TD]
[TD]PEP[/TD]
[TD]pepsi[/TD]
[TD="align: right"]16/08/2018[/TD]
[TD="align: right"]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]
[/TR]
[TR]
[TD]CS[/TD]
[TD]NES[/TD]
[TD]nesc[/TD]
[TD="align: right"]21/11/2018[/TD]
[TD="align: right"]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]
[/TR]
[TR]
[TD]FF[/TD]
[TD]PEP[/TD]
[TD]7up[/TD]
[TD="align: right"]05/02/2018[/TD]
[TD="align: right"]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]
[/TR]
[TR]
[TD]FF[/TD]
[TD]COC[/TD]
[TD]ccz[/TD]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]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][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]NES[/TD]
[TD]nesg[/TD]
[TD="align: right"]30/08/2018[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]NESnesg[/TD]
[TD][/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]
[/TR]
[TR]
[TD]R[/TD]
[TD]PEP[/TD]
[TD]mir[/TD]
[TD="align: right"]15/03/2018[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]PEPmir[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]helper[/TD]
[TD]supplier[/TD]
[TD]product[/TD]
[TD="align: right"]30/04/2018[/TD]
[TD="align: right"]25/08/2018[/TD]
[TD="align: right"]28/09/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]COC[/TD]
[TD]spr[/TD]
[TD="align: right"]30/06/2018[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]COCspr[/TD]
[TD][/TD]
[TD]row 10[/TD]
[TD][/TD]
[TD]COC[/TD]
[TD]COC[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]COC[/TD]
[TD]coca[/TD]
[TD="align: right"]15/07/2018[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]COC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]COCfan[/TD]
[TD]COC[/TD]
[TD]fan[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]NES[/TD]
[TD]nese[/TD]
[TD="align: right"]29/09/2018[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]NES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]COCspr[/TD]
[TD]COC[/TD]
[TD]spr[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]NES[/TD]
[TD]nesa[/TD]
[TD="align: right"]31/10/2018[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]NES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NES[/TD]
[TD]NES[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GS[/TD]
[TD]COC[/TD]
[TD]spr[/TD]
[TD="align: right"]13/03/2018[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]COCspr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NESnes[/TD]
[TD]NES[/TD]
[TD]nes[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GS[/TD]
[TD]PEP[/TD]
[TD]mir[/TD]
[TD="align: right"]23/08/2018[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]PEPmir[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NESnesg[/TD]
[TD]NES[/TD]
[TD]nesg[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GS[/TD]
[TD]NES[/TD]
[TD]neso[/TD]
[TD="align: right"]15/11/2018[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]NESneso[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NESneso[/TD]
[TD]NES[/TD]
[TD]neso[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/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]PEP[/TD]
[TD]PEP[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/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]PEPmir[/TD]
[TD]PEP[/TD]
[TD]mir[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/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][/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]
[/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="align: right"]01/01/2018[/TD]
[TD="align: right"]1[/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="align: right"]01/05/2018[/TD]
[TD="align: right"]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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29/09/2018[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]K10 (COC)[/TD]
[TD="colspan: 2"]=L10&M10[/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"]3[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]COC under helper1 header[/TD]
[TD="colspan: 6"]=IF(ISERROR(MATCH(C2,$M$10:$M$18,0)),B2&"",B2&C2)[/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]
[/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]
[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]can you see any inaccuracies ?[/TD]
[TD][/TD]
[TD][/TD]
[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]
[/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]
[/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]
[/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]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thank you guys,
I owe you a beer :).

I tried script and it works.
I will also try oldbrewer solution and choose which one is best for me.

Thank you for your time!:beerchug:
 
Upvote 0
oldbrewer said:
it keeps my brain cells from atrophying.........
Click to expand...
:rofl:
I still couldnt do job with these formulas, I struggle with helper1, I made somewhere mistake, I got only supplier result.
And also I dont know how to get discount number which should be linked with date.
Possible to get that excel file if you have it to explore formulas and see where I made mistake?

Thank you ;)
 
Upvote 0
MickG said:
If your happy to use some code, try this for results in column "F".
Code: 
[COLOR=Navy]Sub[/COLOR] MG08Dec53
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Txt [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("H3"), Range("H" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False

[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    Txt = Dn.Value & Dn.Offset(, 1).Value
    [COLOR=Navy]Set[/COLOR] .Item(Txt) = Dn
[COLOR=Navy]Next[/COLOR]

[COLOR=Navy]Set[/COLOR] Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
Txt = ""
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
  [COLOR=Navy]For[/COLOR] n = 0 To 1
   Txt = IIf(n = 0, Dn.Value & Dn.Offset(, 1).Value, Dn.Value)
        [COLOR=Navy]If[/COLOR] .exists(Txt) [COLOR=Navy]Then[/COLOR]
           [COLOR=Navy]Select[/COLOR] [COLOR=Navy]Case[/COLOR] True
                [COLOR=Navy]Case[/COLOR] Dn.Offset(, 2).Value > Range("L2").Value
                Dn.Offset(, 4).Value = Format(Cells(.Item(Txt).Row, "L"), "0.00%")
                
                [COLOR=Navy]Case[/COLOR] Dn.Offset(, 2).Value <= Range("L2") _
                And Dn.Offset(, 2).Value > Range("K2")
                Dn.Offset(, 4).Value = Format(Cells(.Item(Txt).Row, "L"), "0.00%")
            
                [COLOR=Navy]Case[/COLOR] Dn.Offset(, 2).Value <= Range("K2") _
                And Dn.Offset(, 2).Value > Range("J2")
                Dn.Offset(, 4).Value = Format(Cells(.Item(Txt).Row, "K"), "0.00%")

                [COLOR=Navy]Case[/COLOR] Dn.Offset(, 2).Value <= Range("J2").Value
                Dn.Offset(, 4).Value = Format(Cells(.Item(Txt).Row, "J"), "0.00%")
           [COLOR=Navy]End[/COLOR] Select
           [COLOR=Navy]Exit[/COLOR] For
        [COLOR=Navy]End[/COLOR] If
  [COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]Next[/COLOR] Dn

[COLOR=Navy]End[/COLOR] With
Application.ScreenUpdating = True
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...
Hi MickG,
when I change this VB and apply my real field numbers (as they are different, some of them are not on same sheet), I get Runtime error `13`: type mismatch on line: "Txt = Dn.Value & Dn.Offset(, 1).Value".
Since I dont know anything about VB codes, could you explain where I should search for mistake?
Thank you.
 
Upvote 0
I assume the format of the test data and actual data is the same
The code you have will only look at the active sheet, this can be changed ,but that is not your present problem .!!

Is it possible to show an example of the data that fails. If you can't post on forum you can use "Box.com" or "DropBox.com" to send an example file. "Please send excel file not a Picture"
 
Upvote 0
MickG said:
I assume the format of the test data and actual data is the same
The code you have will only look at the active sheet, this can be changed ,but that is not your present problem .!!

Is it possible to show an example of the data that fails. If you can't post on forum you can use "Box.com" or "DropBox.com" to send an example file. "Please send excel file not a Picture"
Click to expand...
Here is the whole file for which I am struggling. I tried to change fields to pick up values from another sheet, but I got stuck with VB script.
Ignore hidden rows, because I am getting this kind of report from software and they are not important for this part of job, but they will be always in this report.
And which value to change in VB script when I want to move "Discount" results (if I need them in another row), since I couldnt find it anywhere in script something which looks like defined row?
https://www.dropbox.com/s/682ysf0qglvoed6/Report.xlsx?dl=0

Ty
 
Upvote 0
i HAVE SUPPLIED THE FORMULA FOR HELPER 1 WHICH HAS TO BE DRAGGED DOWN. I am finding eg cocacola plus sprite or cocacola with no product specified. I used helper 2 table to identify the date offset.............
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Find the matching details from 2 workbooks compile it in a sheet and then summarize it and put it in different sheet.
Replies
0
Views
395
deba2020
D
L
  • Solved
Fixed minimum rate for prorating calculation
3 4 5
Replies
41
Views
3K
KRice
K
P
  • Question Question
Multiple a dynamic Range based on conditions
Replies
0
Views
878
Pepperoni
P
N
  • Question Question
First in First Out (Fifo) Stock balance Calaculation File.
Replies
2
Views
10K
TotallyConfused
T
I
  • Question Question
Conditional formatting matching more than one criteria
Replies
0
Views
745
Ira Hopkins
I

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top