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 :

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

<tbody>
</tbody>

Table No 2 :

	M	N	O	P	Q	R
	Plate Number	Period	Total Liter	Total Amount	Min Km	Max Km
7	PN AU 2095	2018/01	235,12 Lt	789,99	399.000	400.000
8	PN AU 2095	2017/12	217,20 Lt	732,76	370.569	375.000
9	PN AU 2095	2017/11	274,44 Lt	892,53	368.000	369.500

<tbody>
</tbody>

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