I'm working with a table that has the Name, ID and location of different entities that I need to consolidate. In many instances the entity is repeated multiple times based on different locations. Locations are scored, but there can be multiple locations for a single entity that have the same score. I would like to summarize this data based on the unique ID so each only appers one time, and in the instance where there are locations with different scores it uses the highest score, but if there are ties for the highest score then it doesn't matter which row it uses, but it does need to consolidate to a single row. I can't share the actual data, but I created a dummy version of the before and after that I'm looking for. This table is a sample of what the data looks like:
| Name | ID | Street Address | City | Postal Code | State | Country | Score |
| Acme | 1 | 1234 Anywhere | Denver | 80222 | CO | United States | 15 |
| Acme | 1 | 456 Main | Parker | 80134 | CO | United States | 15 |
| Sample | 12 | PO Box 1235 | Dallas | | TX | United States | 14 |
| Sample | 12 | | Los Angeles | | | United States | 12 |
| Contoso | 5 | | El Paso | | TX | United States | 7 |
And below is what I'd like the final output to look like. For ACME I don't care if it selects the Parker or Denver row since the scores were the same, I just need to ensure it consolidates to a single row/entry. For Sample the PO Box row will be used because it was scored higher than the row with Los Angeles as the city, and Contoso has a single entry so that will remain as-is.
| Name | ID | Street Address | City | Postal Code | State | Country | Score |
| Acme | 1 | 456 Main | Parker | 80134 | CO | United States | 15 |
| Sample | 12 | PO Box 1235 | Dallas | | TX | United States | 14 |
| Contoso | 5 | | El Paso | | TX | United States | 7 |
