Taking the right amount out of more than one condition

JimmyBambo · Dec 8, 2018

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

oldbrewer · Dec 9, 2018

buyer

supplier

prod

date

dis

helper1

CS

COC

coca

28/01/2018

5

COC

CS

COC

fan

23/07/2018

8

COCfan

column K

column P

CS

PEP

pepsi

16/08/2018

9

PEP

CS

NES

nesc

21/11/2018

3

NES

mytable

FF

PEP

7up

05/02/2018

10

PEP

FF

COC

ccz

01/05/2018

6

COC

FF

NES

nesg

30/08/2018

6

NESnesg

1

2

3

R

PEP

mir

15/03/2018

8

PEPmir

helper

supplier

product

30/04/2018

25/08/2018

28/09/2018

R

COC

spr

30/06/2018

6

COCspr

row 10

COC

5

6

5

R

COC

coca

15/07/2018

6

COC

COCfan

COC

fan

7

8

R

NES

nese

29/09/2018

3

NES

COCspr

COC

spr

6

7

R

NES

nesa

31/10/2018

3

NES

4

2

3

GS

COC

spr

13/03/2018

6

COCspr

NESnes

NES

nes

3

2

3

GS

PEP

mir

23/08/2018

8

PEPmir

NESnesg

NES

nesg

5

6

GS

NES

neso

15/11/2018

12

NESneso

NES

neso

10

12

PEP

10

9

11

PEPmir

PEP

mir

8

9

mytable2

01/01/2018

1

01/05/2018

2

29/09/2018

3

K10 (COC)

=L10&M10

31/12/2018

3

COC under helper1 header

=IF(ISERROR(MATCH(C2,$M$10:$M$18,0)),B2&"",B2&C2)

can you see any inaccuracies ?

the 5 in E2 comes from

=OFFSET($K$9,MATCH(G2,$K$10:$K$18,0),VLOOKUP(D2,mytable2,2)+2)

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

MickG · Dec 10, 2018

Try this Based on your data sheet:-
Results column "U" sheet "Database"
NB:- The date in column "AG" of sheet "Rabat", I think should read "1/1/2019" ???
NB:- I note that you now have 4 columns in sheet "Rabat", of Dates/Percentages. The code should now cover any number of Extra columns of these dates/Percentages.

Code:

[COLOR="Navy"]Sub[/COLOR] MG10Dec53
[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] RngAc [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray() [COLOR="Navy"]As[/COLOR] Variant, Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]With[/COLOR] Sheets("Rabat")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A4", .Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]Set[/COLOR] RngAc = .Range("L3", .Cells(3, Columns.Count).End(xlToLeft)).SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]End[/COLOR] With
MsgBox RngAc.Address
ReDim Ray(1 To RngAc.Count, 1 To 2)
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] RngAc
    [COLOR="Navy"]If[/COLOR] IsDate(R.Value) [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        [COLOR="Navy"]Set[/COLOR] Ray(c, 1) = R
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] R
Application.ScreenUpdating = False

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

[COLOR="Navy"]With[/COLOR] Sheets("Database")
[COLOR="Navy"]Set[/COLOR] Rng = .Range("B6", .Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
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, Trim(Dn.Value) & Trim(Dn.Offset(, 10).Value), Trim(Dn.Value))
        [COLOR="Navy"]With[/COLOR] Sheets("Rabat")
        [COLOR="Navy"]If[/COLOR] Dic.exists(Txt) [COLOR="Navy"]Then[/COLOR]
           [COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray)
             [COLOR="Navy"]If[/COLOR] Ray(Ac, 1) = Ray(1, 1) [COLOR="Navy"]Then[/COLOR]
                 [COLOR="Navy"]If[/COLOR] Dn.Offset(, 13).Value <= CDate(Ray(Ac, 1)) [COLOR="Navy"]Then[/COLOR]
                   Dn.Offset(, 19).Value = Format(.Cells(Dic(Txt).Row, Ray(Ac, 1).Column), "0.00%")
                 [COLOR="Navy"]End[/COLOR] If
             [COLOR="Navy"]ElseIf[/COLOR] CDate(Ray(Ac, 1)) = CDate(Ray(UBound(Ray, 1), 1)) [COLOR="Navy"]Then[/COLOR]
                 [COLOR="Navy"]If[/COLOR] Dn.Offset(, 13).Value > CDate(Ray(Ac, 1)) [COLOR="Navy"]Then[/COLOR]
                    Dn.Offset(, 19).Value = Format(.Cells(Dic(Txt).Row, Ray(Ac, 1).Column), "0.00%")
                 [COLOR="Navy"]End[/COLOR] If
             [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]If[/COLOR] Dn.Offset(, 13).Value <= CDate(Ray(Ac, 1)) _
                  And Dn.Offset(, 13).Value > CDate(Ray(Ac - 1, 1)) [COLOR="Navy"]Then[/COLOR]
                  Dn.Offset(, 19).Value = Format(.Cells(Dic(Txt).Row, Ray(Ac, 1).Column), "0.00%")
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
         
        [COLOR="Navy"]Next[/COLOR] Ac
   [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

JimmyBambo · Dec 11, 2018

oldbrewer said:
29/09/2018 3
K10 (COC) =L10&M10 31/12/2018 3
COC under helper1 header =IF(ISERROR(MATCH(C2,$M$10:$M$18,0)),B2&"",B2&C2)
can you see any inaccuracies ?

<tbody>

</tbody>

I got this message when I try to put formula under helper1 header:

JimmyBambo · Dec 11, 2018

MickG said:
Try this Based on your data sheet:-

Regards Mick

Thank you MickG, this code works

JimmyBambo · Dec 11, 2018

JimmyBambo said:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
[/TABLE]
I got this message when I try to put formula under helper1 header:

Thank you oldbrewer, I fix it, just instead "," i put ";", since we have different versions.
Thank you, it works

JimmyBambo · Dec 11, 2018

oldbrewer, where I made mistake?

oldbrewer · Dec 11, 2018

there are always a few ways to achieve your aim, and different versions and date formats always complicate things..........

MickG · Dec 11, 2018

You're welcome

JimmyBambo · Dec 14, 2018

oldbrewer said:
there are always a few ways to achieve your aim, and different versions and date formats always complicate things..........

Can you see any mistake in formula, since I am getting "NAME" error?
Everything works OK except E row (discount percentages).
Can you see any mistake in this formula?

oldbrewer · Dec 14, 2018

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

Taking the right amount out of more than one condition

JimmyBambo

New Member

oldbrewer

Well-known Member

Excel Facts

MickG

MrExcel MVP

JimmyBambo

New Member

JimmyBambo

New Member

JimmyBambo

New Member

JimmyBambo

New Member

oldbrewer

Well-known Member

MickG

MrExcel MVP

JimmyBambo

New Member

oldbrewer

Well-known Member

Similar threads

Forum statistics

Share this page

Taking the right amount out of more than one condition

New Member

Well-known Member

Excel Facts

MrExcel MVP

New Member

New Member

New Member

New Member

Well-known Member

MrExcel MVP

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock