VBA code to get highest hours value for all duplicate numbers, pls check below table

[ashish_RC]

[TABLE="width: 311"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]NUMBER[/TD]
[TD]NAME[/TD]
[TD]HOURS[/TD]
[TD]HIGHEST HOURS[/TD]
[/TR]
[TR]
[TD]9999[/TD]
[TD]XXXX[/TD]
[TD]23[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]6577[/TD]
[TD]XXXX[/TD]
[TD]43[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]6577[/TD]
[TD]XXXX[/TD]
[TD]66[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]9999[/TD]
[TD]XXXX[/TD]
[TD]22[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]6577[/TD]
[TD]XXXX[/TD]
[TD]53[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]6577[/TD]
[TD]XXXX[/TD]
[TD]23[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]9999[/TD]
[TD]XXXX[/TD]
[TD]54[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]6577[/TD]
[TD]XXXX[/TD]
[TD]65[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]9999[/TD]
[TD]XXXX[/TD]
[TD]22[/TD]
[TD]54[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

Do you really need a macro? Could you use one of these formulas, copied down?

Excel Workbook
	A	B	C	D	E
1	NUMBER	NAME	HOURS	HIGHEST HOURS
2	9999	XXXX	23	54	54
3	6577	XXXX	43	66	66
4	6577	XXXX	66	66	66
5	9999	XXXX	22	54	54
6	6577	XXXX	53	66	66
7	6577	XXXX	23	66	66
8	9999	XXXX	54	54	54
9	6577	XXXX	65	66	66
10	9999	XXXX	22	54	54
Highest Hours

 

[ashish_RC]

Welcome to the MrExcel board!

Do you really need a macro? Could you use one of these formulas, copied down?

Highest Hours

	A	B	C	D	E
NAME
XXXX
XXXX
XXXX
XXXX
XXXX
XXXX
XXXX
XXXX
XXXX

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:75px;"><col style="width:57px;"><col style="width:65px;"><col style="width:127px;"><col style="width:43px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]NUMBER[/TD]

[TD="align: right"]HOURS[/TD]
[TD="align: right"]HIGHEST HOURS[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]9999[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]54[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]6577[/TD]

[TD="align: right"]43[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]66[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]6577[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]66[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]9999[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]54[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]6577[/TD]

[TD="align: right"]53[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]66[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]6577[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]66[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]9999[/TD]

[TD="align: right"]54[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]54[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]6577[/TD]

[TD="align: right"]65[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]66[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]9999[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]54[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =MAXIFS(C$2:C$10,A$2:A$10,A2) E2 =AGGREGATE(14,6,C$2:C$10/(A$2:A$10=A2),1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you Peter. This solution also helpful for me.

I need to calculate sum of hours where numbers are duplicate and paste the total result in TOTAL HOURS column.

Can I get below result using excel formula?

[TABLE="width: 311"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]NUMBER[/TD]
[TD]NAME[/TD]
[TD]HOURS[/TD]
[TD]TOTAL HOURS[/TD]
[/TR]
[TR]
[TD]9999[/TD]
[TD]XXXX[/TD]
[TD]5[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]6577[/TD]
[TD]XXXX[/TD]
[TD]10[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]6577[/TD]
[TD]XXXX[/TD]
[TD]10[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]9999[/TD]
[TD]XXXX[/TD]
[TD]5[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]6577[/TD]
[TD]XXXX[/TD]
[TD]10[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]6577[/TD]
[TD]XXXX[/TD]
[TD]10[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]9999[/TD]
[TD]XXXX[/TD]
[TD]5[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]6577[/TD]
[TD]XXXX[/TD]
[TD]10[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]9999[/TD]
[TD]XXXX[/TD]
[TD]5[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]


Regards
Ashish

 

[Peter_SSs]

Can I get below result using excel formula?

Sure

=SUMIF(A$2:A$10,A2,C$2:C$10)

 

[ashish_RC]

this is really helpful for me, Thank you so much Peter.

 

[Peter_SSs]

this is really helpful for me, Thank you so much Peter.

You're welcome.