Hello, I’m trying to set up auto sort with formula, I have a list of names with numbers, and the goal is to have that list sorted without duplicated names and with the largest number for a specific name. Find file in attachment for easier understanding, and what I managed to get by checking on forums and different tutorials.
Regarding removing duplicates from name I found solution to read the name from another sheet (but if there is formula for this also it would be great), but the main issue is with these largest numbers.
Hello
I personally wouldn’t deal with formulas, lookup etc, but use a simple pivot table which is done in 10 seconds. Just do the following
- Select range
A3:B32 - Go to
Data -> Pivot Table -> Create...(stick with “Current selection”) and click OK - Drag & Drop “Name” from Availabe Fields to Row Fields
- Drag & Drop “Number” from Availabe Fields into Data Fields
- Double click to “Number” and select “Max” as Function and click OK
- Finally click OK
Your layout should look like this:
PS: “Source and Destinations” allow to define a range/cell, where to store the result and modifications can be done, if you right click on pivot table an use Edit Layout....
PPS: And if you don’t like the formatting of a pivot table, just reference cells from a better looking table to pivot cells like you would do with ordinary cells.
Thank you for your answer, but I need result in the same sheet, so I can set another formula to read that result data somewhere else (another sheet) automatically, this way is good if this is the final result, what is not in mine case. That is why I’m searching for a solution with formula, there is no need to be only one formula, it can be several combined formulas in more cells.
Two ways.
Edit the pivot table to define its output position.
Or.
Draw the contents to wherever you want them by referring to them.
Sorting by formulas based on standard functions is complicated and inefficient.
Please use the “add comment” if you want to comment on a post. Only use the answer option for an actual answer.
You might also find the GETPIVOTDATA() function useful