Need a alternate solution for Maxifs

[Divakarkumar]

Hi Team,

I have used Maxifs (=MAXIFS($B$2:$B$13,$A$2:$A$13,A2)=B2) to find latest version of quote number(False/ True) but my bad luck the same Maxifs forumal is not available in my Virtual Machine due to excel version issue.

Examples : I have 10 number repeated in A columns 5 times and B column Version like 12345 in the same row and I have to get 4 false and 1 true which is latest one (10 and 5) , I have attached image for better understate.

False : indicate old version
True : Indicate new version

Great if some one provide alternate option to built same logic.

 

[GraH]

Have a look at MAX IF in Excel to get highest value with conditions
Or a Countif($A$2:$A$13,A2)=B2.

 

[Dave Patton]

You did not post an example that we can use; see XL2BB.
Adapt the following to your data.

T202002a.xlsm
	B	C	D	E
1			Max based on criteria
2	A	200	FALSE	A
3	B	220	FALSE	1,320
4	C	240	FALSE	1,320
5	A	260	FALSE
6	B	280	FALSE
7	C	300	FALSE
8	A	1320	TRUE
9	B	340	TRUE
10	C	360	TRUE
Installations

Cell Formulas
Range	Formula
E3	E3	=MAX((Installations!B2:B455=E2)*Installations!C2:C455)
E4	E4	=AGGREGATE(14,6,Installations!C$2:C$455/(Installations!B$2:B$455=E2),1)
D2:D10	D2	=AGGREGATE(14,6,Installations!C$2:C$455/(Installations!B$2:B$455=B2),1)=C2
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Divakarkumar]

You did not post an example that we can use; see XL2BB.
Adapt the following to your data.
========================

Hi Sir,

Your result is coming as i expected but i'm not able to understand the formula which you have used, could you please use the same formula in below data set in C column, need false or true result based on latest quote.

and I really appreciate your help

Quote Numbers	PVR Version	Latest PVR Version
10	1
11	2
10	3
11	4
14	5
15	6
14	7
15	8
18	9
10	5
18	11
19	12

 

[Peter_SSs]

Welcome to the MrExcel board!

Adapting the last formula suggested in post 3 to your data layout does exactly as the MAXIFS formula.

20 07 31.xlsm
	A	B	C	D
1	Quote Numbers	PVR Version	Latest PVR Version
2	10	1	FALSE	FALSE
3	11	2	FALSE	FALSE
4	10	3	FALSE	FALSE
5	11	4	TRUE	TRUE
6	14	5	FALSE	FALSE
7	15	6	FALSE	FALSE
8	14	7	TRUE	TRUE
9	15	8	TRUE	TRUE
10	18	9	FALSE	FALSE
11	10	5	TRUE	TRUE
12	18	11	TRUE	TRUE
13	19	12	TRUE	TRUE
MAXIFS Replacement

Cell Formulas
Range	Formula
C2:C13	C2	=MAXIFS($B$2:$B$13,$A$2:$A$13,A2)=B2
D2:D13	D2	=AGGREGATE(14,6,B$2:B$13/(A$2:A$13=A2),1)=B2

 

[Divakarkumar]

Thanks you so much for your help Sir, really I appreciate your help

 

[Peter_SSs]

You're welcome. Dave did the work though - I just tweaked the ranges.