CODEWITHSUNDEEP
sortinglibreoffice-calc
James
James

Reputation: 4783

LibreOffice Calc sort rows by column comparison

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.

Upvotes: 3

Views: 2939

Answers (1)

ForguesR
ForguesR

Reputation: 3618

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.

Upvotes: 3

Related Questions