formula to derive district with maximum count

[vinod9111]

Hi All,

Need a formula to derive the final district by looking at the maximum count. There are duplicate accounts against which there are number of counts, the district with maximum count of these duplicate to be derived in final district column. In event of tie any one of the district can be reported. Final district is the expected results.

[TABLE="width: 474"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Acct[/TD]
[TD]District[/TD]
[TD]Count[/TD]
[TD]Final district[/TD]
[/TR]
[TR]
[TD]02211800004661[/TD]
[TD]Akola[/TD]
[TD]4[/TD]
[TD]Akola[/TD]
[/TR]
[TR]
[TD]02211800004661[/TD]
[TD]Washim[/TD]
[TD]4[/TD]
[TD]Akola[/TD]
[/TR]
[TR]
[TD]03601800006491[/TD]
[TD]Latur[/TD]
[TD]7[/TD]
[TD]Latur[/TD]
[/TR]
[TR]
[TD]03601800006491[/TD]
[TD]Osmanabad[/TD]
[TD]1[/TD]
[TD]Latur[/TD]
[/TR]
[TR]
[TD]18101800000058[/TD]
[TD]Nagpur[/TD]
[TD]1[/TD]
[TD]Wardha[/TD]
[/TR]
[TR]
[TD]18101800000058[/TD]
[TD]Wardha[/TD]
[TD]4[/TD]
[TD]Wardha[/TD]
[/TR]
[TR]
[TD]50100001276940[/TD]
[TD]Cuddalore[/TD]
[TD]1[/TD]
[TD]Villupuram[/TD]
[/TR]
[TR]
[TD]50100001276940[/TD]
[TD]Villupuram[/TD]
[TD]9[/TD]
[TD]Villupuram[/TD]
[/TR]
</tbody>[/TABLE]

regards,

Vinod

 

[Peter_SSs]

This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D
1	Acct	District	Count	Final district
2	02211800004661	Akola	4	Akola
3	02211800004661	Washim	4	Akola
4	03601800006491	Latur	7	Latur
5	03601800006491	Osmanabad	1	Latur
6	18101800000058	Nagpur	1	Wardha
7	18101800000058	Wardha	4	Wardha
8	50100001276940	Cuddalore	1	Villupuram
9	50100001276940	Villupuram	9	Villupuram
Final District

 

[vinod9111]

Thanks Peter, it worked.

you are always a great help...

regards,

Vinod

 

[Peter_SSs]

Cheers. Glad to help.