Find Largest Decimal Value and then display result from a different cell

[feni1388]

Hello everyone....

I need to find the highest decimal (column G) among those with the same customer code (if there're more than 1 that have the same customer code in column A. If there's only one like MC05, then no need to return anything) then return it as complete code (column B).
I found a similar problem in another forum and tried using index function but it didn't work out for me.
Does anyone have any suggestion how to fix it?

Thank you in advance.

Book1
	A	B	C	D	E	F	G	H	I
1	Customer code	Complete code	Total	VAT	Sum	VAT with decimal	VAT diff.	which code has the highest diff. (didn't work out)	The result that I hope to have
2	MC01	MC010101	138,080	13,808.00	151,888	13,808.00	0.00	MC01
3	MC02	MC020101	212,177	21,217.00	233,394	21,217.70	0.70	MC02	MC020101
4	MC02	MC020201	30,875	3,087.00	33,962	3,087.50	0.50	MC02
5	MC03	MC030101	215,220	21,522.00	236,742	21,522.00	0.00	MC01
6	MC05	MC050101	361,365	36,136.00	397,501	36,136.50	0.50	MC02
7	MC06	MC060101	89,600	8,960.00	98,560	8,960.00	0.00	MC01
8	MC07	MC070101	37,750	3,775.00	41,525	3,775.00	0.00	MC01
9	MC07	MC070201	81,740	8,174.00	89,914	8,174.00	0.00	MC01
10	MC07	MC070401	23,600	2,360.00	25,960	2,360.00	0.00	MC01
11	SIS	SIS02001	339,760	33,976.00	373,736	33,976.00	0.00	MC02
12	SIS	SIS04001	512,050	51,205.00	563,255	51,205.00	0.00	MC02
13	SIS	SIS05001	156,730	15,673.00	172,403	15,673.00	0.00	MC02
14	SIS	SIS06001	13,500	1,350.00	14,850	1,350.00	0.00	MC02
15	SIS	SIS07001	57,360	5,736.00	63,096	5,736.00	0.00	MC02
16	SIS	SIS08001	15,910	1,591.00	17,501	1,591.00	0.00	MC02
17	SIS	SIS09001	14,200	1,420.00	15,620	1,420.00	0.00	MC02
18	SIS	SIS10001	36,040	3,604.00	39,644	3,604.00	0.00	MC02
19	SIS	SIS11001	24,200	2,420.00	26,620	2,420.00	0.00	MC02
20	SIS	SIS12001	24,860	2,486.00	27,346	2,486.00	0.00	MC02
21	SIS	SIS13001	18,900	1,890.00	20,790	1,890.00	0.00	MC02
22	SIS	SIS14001	20,320	2,032.00	22,352	2,032.00	0.00	MC02
23	SIS	SIS15001	13,500	1,350.00	14,850	1,350.00	0.00	MC02
24	SIS	SIS16001	19,185	1,918.00	21,103	1,918.50	0.50	MC02	SIS16001
Sheet1

Cell Formulas
Range	Formula
D2:D24	D2	=ROUNDDOWN(C2*10%,0)
E2:E24	E2	=D2+C2
F2:F24	F2	=C2*10%
G2:G24	G2	=F2-TRUNC(D2)
H2:H24	H2	=INDEX($A$2:$A$24,MATCH(MAXIFS(G:G,A:A,A2),$G$2:$G$24,0))
A2:A10	A2	=LEFT(B2,4)
A11:A24	A11	=LEFT(B11,3)

 

[Peter_SSs]

Thank you ..... your formula works great too.

Perhaps I have misinterpreted your statement ..

I want to show the code of the one that has the highest total amount (column C)

With the sample data, try changing cell C3 to 12,175. Shouldn't the formula now show the result in row 4 since C4 is now higher than C3?

 

[Phuoc]

The results of the two formulas are the same.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Customer code	Complete code	Total	VAT	Sum	VAT with decimal	VAT diff.	F1	F2	F3
2	MC01	MC010101	138080	13808	151888	13808	0
3	MC02	MC020101	12.175	1	13.175	1.2175	0.2175
4	MC02	MC020201	30875	3087	33962	3087.5	0.5	MC020201	MC020201	MC020201
5	MC03	MC030101	215220	21522	236742	21522	0
6	MC05	MC050101	361365	36136	397501	36136.5	0.5
7	MC06	MC060101	89600	8960	98560	8960	0
8	MC07	MC070101	37751	3775	41526	3775.1	0.1
9	MC07	MC070201	81742	8174	89916	8174.2	0.2	MC070201	MC070201	MC070201
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=ROUNDDOWN(C2*10%,0)
E2:E9	E2	=D2+C2
F2:F9	F2	=C2*10%
G2:G9	G2	=F2-D2
H2:H9	H2	=IF(COUNTIF(A$2:A$24,A2)=1,"",LET(m,MAXIFS(G$2:G$24,A$2:A$24,A2),IF(OR(m=0,m<>G2,C2<>MAXIFS(C$2:C$24,A$2:A$24,A2,G$2:G$24,m)),"",B2)))
I2:I9	I2	=IF(OR(COUNTIF(A$2:A$24,A2)=1,COUNTIFS($A$2:A2,A2,$G$2:G2,G2)>1),"",LET(m,MAXIFS(G$2:G$24,A$2:A$24,A2),IF(OR(m=0,m<>G2),"",B2)))
J2:J9	J2	=IFS(G2=0,"",COUNTIF(A$2:A$24,A2)=1,"",COUNTIFS($A$2:A2,A2,$G$2:G2,G2)>1,"",G2=MAXIFS(G$2:G$24,A$2:A$24,A2),B2,TRUE,"")
A2:A9	A2	=LEFT(B2,4)

 

[Peter_SSs]

The results of the two formulas are the same.

Not if you make the change I mentioned.

24 08 21.xlsm
	A	B	C	D	E	F	G	H	I
1	Customer code	Complete code	Total	VAT	Sum	VAT with decimal	VAT diff.	Result	Result
2	MC01	MC010101	138,080	13808	151888	13808	0
3	MC02	MC020101	12,175	1217	13392	1217.5	0.5		MC020101
4	MC02	MC020201	30,875	3087	33962	3087.5	0.5	MC020201
5	MC03	MC030101	215,220	21522	236742	21522	0
6	MC05	MC050101	361,365	36136	397501	36136.5	0.5
7	MC06	MC060101	89,600	8960	98560	8960	0
8	MC07	MC070101	37,750	3775	41525	3775	0
9	MC07	MC070201	81,740	8174	89914	8174	0
10	MC07	MC070401	23,600	2360	25960	2360	0
feni1388 (2)

Cell Formulas
Range	Formula
D2:D10	D2	=ROUNDDOWN(C2*10%,0)
E2:E10	E2	=D2+C2
F2:F10	F2	=C2*10%
G2:G10	G2	=F2-D2
H2:H10	H2	=IF(COUNTIF(A$2:A$24,A2)=1,"",LET(m,MAXIFS(G$2:G$24,A$2:A$24,A2),IF(OR(m=0,m<>G2,C2<>MAXIFS(C$2:C$24,A$2:A$24,A2,G$2:G$24,m)),"",B2)))
I2:I10	I2	=IF(OR(COUNTIF(A$2:A$24,A2)=1,COUNTIFS($A$2:A2,A2,$G$2:G2,G2)>1),"",LET(m,MAXIFS(G$2:G$24,A$2:A$24,A2),IF(OR(m=0,m<>G2),"",B2)))
A2:A10	A2	=LEFT(B2,4)

 

[feni1388]

Perhaps I have misinterpreted your statement ..

With the sample data, try changing cell C3 to 12,175. Shouldn't the formula now show the result in row 4 since C4 is now higher than C3?

Yes, you're right.
It didn't change when I used Phuoc's formula. It should have changed to C4 instead of C3.
I didn't realise that. Thank you for letting me know.

 

[Peter_SSs]

Thank you for letting me know.

No problem.