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]

Are the yellow cells supposed to be telling us something?

There are 3 rows with Customer code MC07. Why is there nothing in column I for them?

In column G, wouldn't =F2-D2 do the same us using TRUNC()?

See if this helps anyway

24 08 21.xlsm
	A	B					G		I
1	Customer code	Complete code					VAT diff.
2	MC01	MC010101					0.00
3	MC02	MC020101					0.70		MC020101
4	MC02	MC020201					0.50
5	MC03	MC030101					0.00
6	MC05	MC050101					0.50
7	MC06	MC060101					0.00
8	MC07	MC070101					0.00
9	MC07	MC070201					0.00
10	MC07	MC070401					0.00
11	SIS	SIS02001					0.00
12	SIS	SIS04001					0.00
13	SIS	SIS05001					0.00
14	SIS	SIS06001					0.00
15	SIS	SIS07001					0.00
16	SIS	SIS08001					0.00
17	SIS	SIS09001					0.00
18	SIS	SIS10001					0.00
19	SIS	SIS11001					0.00
20	SIS	SIS12001					0.00
21	SIS	SIS13001					0.00
22	SIS	SIS14001					0.00
23	SIS	SIS15001					0.00
24	SIS	SIS16001					0.50		SIS16001
feni1388

Cell Formulas
Range	Formula
I2:I24	I2	=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),"",B2)))

 

[feni1388]

Are the yellow cells supposed to be telling us something?

There are 3 rows with Customer code MC07. Why is there nothing in column I for them?

In column G, wouldn't =F2-D2 do the same us using TRUNC()?

See if this helps anyway

24 08 21.xlsm
	A	B					G		I
1	Customer code	Complete code					VAT diff.
2	MC01	MC010101					0.00
3	MC02	MC020101					0.70		MC020101
4	MC02	MC020201					0.50
5	MC03	MC030101					0.00
6	MC05	MC050101					0.50
7	MC06	MC060101					0.00
8	MC07	MC070101					0.00
9	MC07	MC070201					0.00
10	MC07	MC070401					0.00
11	SIS	SIS02001					0.00
12	SIS	SIS04001					0.00
13	SIS	SIS05001					0.00
14	SIS	SIS06001					0.00
15	SIS	SIS07001					0.00
16	SIS	SIS08001					0.00
17	SIS	SIS09001					0.00
18	SIS	SIS10001					0.00
19	SIS	SIS11001					0.00
20	SIS	SIS12001					0.00
21	SIS	SIS13001					0.00
22	SIS	SIS14001					0.00
23	SIS	SIS15001					0.00
24	SIS	SIS16001					0.50		SIS16001
feni1388

Cell Formulas
Range	Formula
I2:I24	I2	=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),"",B2)))

Are the yellow cells supposed to be telling us something?
--> No, I just want to show where the difference is

There are 3 rows with Customer code MC07. Why is there nothing in column I for them?
--> Because the VAT difference in column G is 0

In column G, wouldn't =F2-D2 do the same us using TRUNC()?
--> Yes, you're right. I don't know why I used that. Thank you.

Thank you so much.... your formula works great.
I'll try it again on different cases.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up & clarifications.

 

[feni1388]

You're welcome. Thanks for the follow-up & clarifications.

Dear Peter,

Please help again.
In the first table, the VAT difference is obvious, which is 0.7 and 0.5.
But in case it has the same 0.5 and 0.5, I want to show the code of the one that has the highest total amount (column C), in this case MC020101.

I tried to modify it myself, but it didn't work.

Book1
	A	B	C	D	E	F	G	H	I
1	Customer code	Complete code	Total	VAT	Sum	VAT with decimal	VAT diff.	The current result	The result that I hope to have
2	MC01	MC010101	138,080	13,808.00	151,888	13,808.00	0.00
3	MC02	MC020101	212,175	21,217.00	233,392	21,217.50	0.50	MC020101	MC020101
4	MC02	MC020201	30,875	3,087.00	33,962	3,087.50	0.50	MC020201
5	MC03	MC030101	215,220	21,522.00	236,742	21,522.00	0.00
6	MC05	MC050101	361,365	36,136.00	397,501	36,136.50	0.50
7	MC06	MC060101	89,600	8,960.00	98,560	8,960.00	0.00
8	MC07	MC070101	37,750	3,775.00	41,525	3,775.00	0.00
9	MC07	MC070201	81,740	8,174.00	89,914	8,174.00	0.00
10	MC07	MC070401	23,600	2,360.00	25,960	2,360.00	0.00
Sheet1

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),"",B2)))
A2:A10	A2	=LEFT(B2,4)

 

[Phuoc]

Try change to:

=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)))

 

[Peter_SSs]

This is my reading of it.

24 08 21.xlsm
	A	B	C				G	H
1	Customer code	Complete code	Total				VAT diff.	Result
2	MC01	MC010101	138,080				0
3	MC02	MC020101	212,175				0.5	MC020101
4	MC02	MC020201	30,875				0.5
5	MC03	MC030101	215,220				0
6	MC05	MC050101	361,365				0.5
7	MC06	MC060101	89,600				0
8	MC07	MC070101	37,750				0
9	MC07	MC070201	81,740				0
10	MC07	MC070401	23,600				0
feni1388 (2)

Cell Formulas
Range	Formula
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)))
A2:A10	A2	=LEFT(B2,4)

 

[feni1388]

This is my reading of it.

24 08 21.xlsm
	A	B	C				G	H
1	Customer code	Complete code	Total				VAT diff.	Result
2	MC01	MC010101	138,080				0
3	MC02	MC020101	212,175				0.5	MC020101
4	MC02	MC020201	30,875				0.5
5	MC03	MC030101	215,220				0
6	MC05	MC050101	361,365				0.5
7	MC06	MC060101	89,600				0
8	MC07	MC070101	37,750				0
9	MC07	MC070201	81,740				0
10	MC07	MC070401	23,600				0
feni1388 (2)

Cell Formulas
Range	Formula
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)))
A2:A10	A2	=LEFT(B2,4)

Thank you so much.... it works perfectly.

 

[Peter_SSs]

Cheers

 

[feni1388]

Try change to:

=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)))

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