Ask Your Question

[Calc] Auto sort with formula

asked 2019-05-15 14:22:57 +0200

Nkru gravatar image

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.

C:\fakepath\autosort v.1.ods

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-05-15 14:46:21 +0200

Opaque gravatar image

updated 2019-05-15 14:59:05 +0200


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: image description

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.

edit flag offensive delete link more

answered 2019-05-16 07:24:02 +0200

Nkru gravatar image

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.

edit flag offensive delete link more


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.

Lupp gravatar imageLupp ( 2019-05-16 09:05:08 +0200 )edit

Please use the "add comment" if you want to comment on a post. Only use the answer option for an actual answer.

Lupp gravatar imageLupp ( 2019-05-16 09:08:14 +0200 )edit

You might also find the GETPIVOTDATA() function useful

robleyd gravatar imagerobleyd ( 2019-05-16 11:28:06 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-05-15 14:22:57 +0200

Seen: 30 times

Last updated: May 16