Replacing field values to match top value based on different column value

[Lili7891]

Hi, 

This one might be more simple than I think, I'm trying to replace values in a cell based on the data in other columns.

My data set up is as follows:

Name	Location	Value
A	UK	100,000
A	US	20
B	India	30,000
B	Germany	3000

I've already sorted the data so the name is in ascending order and the values are descending and I want to replace the location field to match the field that has the highest value in as I essentially want to summarise the values at the end and assign them to the location (so duplication removal won't work), basically I want the table to look like below after running

Name	Location	Value
A	UK	100,000
A	UK	20
B	India	30,000
B	India	3000

I think maybe if I use a multi row formula I can achieve this but not sure on the condition to make it work out it's the field with the highest value - hence why I did the sort beforehand. 

Appreciate any suggestions.

Thank you.

[Qiu · Accepted solution]

@Lili7891 
I hope this is what you need.

[mceleavey]

Hi @Lili7891 ,

you almost had it in your description.

Applying max in the sum tool allows you to achieve this.

Hope this helps,

M.

[Lili7891]

Amazing, worked perfectly, thank you so much!

[Qiu]

@Lili7891 
Glad to help and thank you for the accept mark. 😁