Need code or formula ( next large value replace with word " last")

[furqan_q]

Hi, i need help in formula or VBA code any thing, i am using =COUNTIF($A$2:$A2,A2) formula, it is giving me count of my duplicate lease no which are in column A, i need to replace highest number with word "Last".

eg:

LEASE NO IS IN "A" COLOUMN = 10005 ( total lease for 10005 duplicates are 5 )

TOTAL LEASE VERSION = 5 (=COUNTIF($A$2:$A$13,A2)

LAST AND HIGHEST LEASE VERSION = 5 (Need to Replace 5 with "LAST" ) =COUNTIF($A$2:$A2,A2)

see the attached file pic and excle .Thanks

 

[Saba Sabaratnam]

I assume that you have 365 or 2019 Excel.

Enter the following formula in C1 and copy it down.

=IF(MAXIFS($B$1:$B$9,$A$1:$A$9,A1)=COUNTIFS($A$1:A1,A1),"Last",COUNTIFS($A$1:A1,A1))

Kind regards

Saba

 

[furqan_q]

Hi thanks you for ur reply, i am using 2010 ver, with me its giving error of "#NAME?"

 

[Saba Sabaratnam]

Hi

Please enter the following array formula in C1 by pressing CONTORL + SHIFT + ENTER keys and copy it down.

=IF(MAX(IF($A$1:$A$9=A1,$B$1:$B$9,0))=COUNTIFS($A$1:A1,A1),"Last",COUNTIFS($A$1:A2,A2))

Name error occurred as 2010 does not have MAXIFS formula.

Please update your version of Excel so that we can provide solutions that will work in your Excel version.

Kind regards

Saba

 

[furqan_q]

Thank you so much for ur help, it works now ... i almost spend 10 hours finding this formula and was not able to do so ... Thanks you once again for ur help and support .

 

[DanteAmor]

Try this regular formula:

Dante Amor
	A	B	C
1	cc	last v	l ver no
2	10001		LAST
3	10005		1
4	10005		2
5	10005		3
6	10005		4
7	10005		LAST
8	10089		LAST
9	10201		1
10	10012		1
11	10012		2
12	10012		LAST
13	10201		2
14	10201		3
15	10201		4
16	10201		5
17	10201		LAST
Hoja8

Cell Formulas
Range	Formula
C2:C17	C2	=IF(COUNTIF($A$2:$A$17,A2)=COUNTIF(A$2:A2,A2),"LAST",COUNTIF(A$2:A2,A2))

 

[Saba Sabaratnam]

No problem

 

[furqan_q]

Try this regular formula:

Dante Amor
	A	B	C
1	cc	last v	l ver no
2	10001		LAST
3	10005		1
4	10005		2
5	10005		3
6	10005		4
7	10005		LAST
8	10089		LAST
9	10201		1
10	10012		1
11	10012		2
12	10012		LAST
13	10201		2
14	10201		3
15	10201		4
16	10201		5
17	10201		LAST
Hoja8

Cell Formulas
Range	Formula
C2:C17	C2	=IF(COUNTIF($A$2:$A$17,A2)=COUNTIF(A$2:A2,A2),"LAST",COUNTIF(A$2:A2,A2))

Thank you so much its works fine with me ..........

 

[furqan_q]

You guys make my day very happy ............. Thanks a lot .....

 

[DanteAmor]

Glad we could help & thanks for the feedback