formula to derive district with maximum count

vinod9111

Active Member
Joined
Jan 21, 2009
Messages
425
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.

AcctDistrictCountFinal district
02211800004661Akola4Akola
02211800004661Washim4Akola
03601800006491Latur7Latur
03601800006491Osmanabad1Latur
18101800000058Nagpur1Wardha
18101800000058Wardha4Wardha
50100001276940Cuddalore1Villupuram
50100001276940Villupuram9Villupuram

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

regards,

Vinod
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,401
Office Version
365
Platform
Windows
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
ABCD
1AcctDistrictCountFinal district
202211800004661Akola4Akola
302211800004661Washim4Akola
403601800006491Latur7Latur
503601800006491Osmanabad1Latur
618101800000058Nagpur1Wardha
718101800000058Wardha4Wardha
850100001276940Cuddalore1Villupuram
950100001276940Villupuram9Villupuram
Final District
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,401
Office Version
365
Platform
Windows
Cheers. Glad to help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,188
Messages
5,442,914
Members
405,206
Latest member
Neetish_93

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top