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

JimmyBambo · Dec 8, 2018

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.

MickG · Dec 8, 2018

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

oldbrewer · Dec 8, 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 ?

JimmyBambo · Dec 8, 2018

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!

oldbrewer · Dec 8, 2018

it keeps my brain cells from atrophying.........

JimmyBambo · Dec 8, 2018

oldbrewer said:
it keeps my brain cells from atrophying.........

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

JimmyBambo · Dec 8, 2018

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

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.

MickG · Dec 9, 2018

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"

JimmyBambo · Dec 9, 2018

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"

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

oldbrewer · Dec 9, 2018

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.............

Taking the right amount out of more than one condition

JimmyBambo

New Member

JimmyBambo

New Member

Excel Facts

MickG

MrExcel MVP

oldbrewer

Well-known Member

JimmyBambo

New Member

oldbrewer

Well-known Member

JimmyBambo

New Member

JimmyBambo

New 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

New Member

Excel Facts

MrExcel MVP

Well-known Member

New Member

Well-known Member

New Member

New 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