Finding the Max & Min Values regarding to 2 Criterias

[uygaroz]

Good Day to all
I am trying to replace below formulas with a macro code.

=Min(if($C$7:$C$14940=$N7;if($D$7:$D$14940=M$7;$F$7:$F$11940)))
=Max(if($C$7:$C$14940=$N7;if($D$7:$D$14940=$M7;$F$7:$F$14940)))

I have 2 criterias
Criteria 1 : Plate Number like (PN AU 2095)
Criteria 2 : Period like (2018/01)

I try to find max or min values from Column (F7:to last Row) with multiple criteria.

Column (D7:to last row)= Plate Numbers
Column (C7:to last row)= Periods
Column (F7:to last row)= Kilometers (where the code must get the max or min "Km" regarding to these 2 criteria.)

Thanks for any help from now.

I am not allowed to load file to forum yet.
That is why I had load my file to link below.

Excel file link : [FONT=source_sans_proregular]https://mega.nz/#!BssiGRaa[/FONT][FONT=source_sans_proregular]!iNznlQslb_7u0WK0bAm20dHMnNmx9503AcP56YIV5YA[/FONT]


Note : By "recording macro" I can get this code. But I am not looking for something like this.

Range("Q9").Select
Selection.FormulaArray = _
"=MIN(IF(R7C3:R14940C3=R[-1]C14,IF(R7C4:R14940C4=R3C[-4],R7C6:R11940C6)))"

 

[SteveO59L]

have you considered a pivot table?

 

[uygaroz]

Yes I have already did it with pivot table.
But as I mentioned, I have solution with formula too.

My aim is not find a solution, it is to convert the formulas to macro codes.

 

[SteveO59L]

try posting a sample of your data, and what you would like to do with it

 

[uygaroz]

Dear Steve
I am not very familiar with this for and unable to load a file.
Instead I have uploaded my excel workbok to following link
Excel file link : [FONT=source_sans_proregular]https://mega.nz/#!BssiGRaa[/FONT][FONT=source_sans_proregular]!iNznlQslb_7u0WK0bAm20dHMnNmx9503AcP56YIV5YA[/FONT]

You can see al the data there.

 

[SteveO59L]

the internet police wont allow me to view external files.

just type a sample into a message body

 

[uygaroz]

Table No 1 :
[TABLE="width: 618"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No[/TD]
[TD]Date[/TD]
[TD]Period[/TD]
[TD]Plate No[/TD]
[TD]City[/TD]
[TD]Km[/TD]
[TD]Litre[/TD]
[TD]Litre Price[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[TD]26.01.2018[/TD]
[TD]2018/01[/TD]
[TD]PN AU 2095[/TD]
[TD]SARAY[/TD]
[TD]400.000[/TD]
[TD]50,62[/TD]
[TD]3,39[/TD]
[TD]171,58[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[TD]22.01.2018[/TD]
[TD]2018/01[/TD]
[TD]PN AU 2095[/TD]
[TD]SARAY[/TD]
[TD]399.500[/TD]
[TD]65,15[/TD]
[TD]3,39[/TD]
[TD]220,83[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3[/TD]
[TD]16.01.2018[/TD]
[TD]2018/01[/TD]
[TD]PN AU 2095[/TD]
[TD]SARAY[/TD]
[TD]399.250[/TD]
[TD]50,02[/TD]
[TD]3,33[/TD]
[TD]166,62[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4[/TD]
[TD]11.01.2018[/TD]
[TD]2018/01[/TD]
[TD]PN AU 2095[/TD]
[TD]SARAY[/TD]
[TD]399.000[/TD]
[TD]69,33[/TD]
[TD]3,33[/TD]
[TD]230,95[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]5[/TD]
[TD]20.12.2017[/TD]
[TD]2017/12[/TD]
[TD]PN HB 84PN[/TD]
[TD]ADANA[/TD]
[TD]532.460[/TD]
[TD]621,35[/TD]
[TD]3,39[/TD]
[TD]2.108,62[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]6[/TD]
[TD]14.12.2017[/TD]
[TD]2017/12[/TD]
[TD]PN HB 84PN[/TD]
[TD]SARAY[/TD]
[TD]527.400[/TD]
[TD]336,61[/TD]
[TD]3,39[/TD]
[TD]1.142,32
[/TD]
[/TR]
</tbody>[/TABLE]


Table No 2 :

[TABLE="width: 397"]
<tbody>[TR]
[TD][/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Plate Number[/TD]
[TD]Period[/TD]
[TD]Total Liter[/TD]
[TD]Total
Amount[/TD]
[TD]Min Km[/TD]
[TD]Max Km[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]PN AU 2095[/TD]
[TD]2018/01[/TD]
[TD]235,12 Lt[/TD]
[TD]789,99[/TD]
[TD]399.000[/TD]
[TD]400.000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]PN AU 2095[/TD]
[TD]2017/12[/TD]
[TD]217,20 Lt[/TD]
[TD]732,76[/TD]
[TD]370.569[/TD]
[TD]375.000[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]PN AU 2095[/TD]
[TD]2017/11[/TD]
[TD]274,44 Lt[/TD]
[TD]892,53[/TD]
[TD]368.000[/TD]
[TD]369.500[/TD]
[/TR]
[TR]
[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]
[/TR]
</tbody>[/TABLE]


Steve hi again.
I have found our how I can add table here.
As you see I have table 1 where the data is located.


In Table 2
Using Max if / Min if formulas with 2 criteria (Plate number and Period) I am finding the max or/and min km from table one.

So what I need is
instead of using formulas and pivot table (where I can get the correct results)
want to replace my formulas with macro code.

Cell Q7 Formula= {=Min(if($C$7:$C$14940=$N7;if($D$7:$D$14940=M$7;$F$7:$F$11940)))}
Cell R7 Formula = {=Max(if($C$7:$C$14940=$N7;if($D$7:$D$14940=$M7;$F$7:$F$14940)))}

Formulas has to activated with Ctrl+shift + Enter
Hope it is clear.
Thanks