LibreOffice Calc sort rows by column comparison

Question

I'm trying to sort rows based on two columns matching.

For example, in the following table, two users rate the same books. In sorting the example table below, Book 2 should come first, and Book 4 second, because the user's ratings both match.

  BOOK     USER A     USER B  
 Book 1      4.5        3.5
 Book 2      2.0        2.0
 Book 3      5.0        3.5
 Book 4      3.0        3.0

The remaining which did not match, would be in ascending order based on USER A ratings (although this isn't the important bit really).

I can use the basic Sort - sorting Book and USER A by USER A ascending, then sort USER B ascending separately, and will all match up again with the correct ratings for the correct books, and as I want it. But I need a more functional way of doing this.

Mainly so I can copy the sorted data to a new sheet.

Answer 1

I am not certain if this is "a more functional way of doing this" but assuming something like :

  |   A    |   B    |    C         
------------------------------
1 | BOOK     USER A   USER B    
2 | Book 1      4.5      3.5    
3 | Book 2      2.0      2.0    
4 | Book 3      5.0      3.5    
5 | Book 4      3.0      3.0    

If the maximum rating is 5 it can be solved easily with a very simple formula in the column D :

=IF(B2-C2=0;-5+B2;B2)  

Basically it checks the difference between the columns B and C. If they are equals it will return a negative value based on the difference with the maximum. If not we use the rating from User A.

You can then sort the whole range (ascending) based on column D. You should get the result you want.