Find and mark the higher value in series of duplicate entries

Hi, I have been trying for ages to figure out a better way to do this. Any help would be greatly appreciated.

I have a approx. 70 excel sheets with thousands of entries. Within a single sheet there are some duplicate entries (based on an account number). I find the duplicate entries by using the excel 2007 conditional formating and then sort by color to only show the duplicate entries. There are often hundreds of accounts that are duplicates.

Sometimes there are more than 2 identical account duplicates (could be 3,4 or more).

In each entry there is a number representing the rating of said account. These numbers differ within the duplicates.

Ie.
A B C D
Acct. # Name Rating W/L
1 12a123 DOG 2
2 12a123 DOG 1
3 12a123 DOG 4
4 23b234 CAT
5 23b234 CAT 2
6 34c345 RAT 7
7 34c345 RAT 7
8 34c345 RAT 7

I am trying to find a formula that will choose a "winner" and enter a "W" based on the highest rating within the duplicate entries and then ideally assign an "L" to the loser duplicate account(s).
Sometimes there is no rating, other times the rating could be the same in which case any account (does not matter) can be assigned a "W" and the others an "L"

Thanks in advance. I am grateful for forums likes this and the experts who give their time to help "us" out.

I attached an example worksheet.

Hi,

Try this

Please Login or Register  to view this content.

which is confirmed with CTRL+SHIFT+ENTER not just ENTER

adjust range b2:b9 to suit and reconfirm with CSE keys, then copy down.

You are welcome

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save