Calc: How do I sort one range of cells according to the values in a second range of cells, and place the results in a third range of cells?

[H.G]

Windows 7 SP1. LibreOffice 4.2.6.3. Calc.

I would like to sort a list of values in one range of cells into an order determined by a co-related list of values in a second range of cells, and place the resulting sorted list into a third range of cells (I would like to leave the original list of values, unsorted, where they are).

A simplified example:

The range B11:B20 contains a list of “Labels”/“Names” (of items): “A”,“B”,“C”,“D”,“E”,“F”,“G”,“H”,“I”,“J”.

The range C11:C20 contains a co-related list of attribute values: 5, 17, 49, -3, 15,145, 49,8.5, 17, 9.

(Note: The attribute values may not all be unique).

The resulting sorted list (of items) placed in C31:C40 would be: “D”,“A”,“H”,“J”,“E”,“B”,“I”,“C”,“G”,“F”.

I cannot see an easy/straightforward way to do this.

I can only imagine something like having to write formula statements with a lot of (nested) “IF” Functions. (And, as the actual lists contain 50 items, and there are multiple co-related lists of attributes, that will be a LOT of "IF"s!) A somewhat daunting prospect! Please help! (Do I need to learn to write Macros? I’m not averse to that, where do I start?)

---

UPDATE: In lieu of an attachment here’s the Less Simplified Example in text form:

                Daily      Cumulative    Items arranged
             A B C D E F   A B C D E F   by cumulative score
2014/09/25     1       1   0 1 0 0 0 1   B F A C D E
2014/09/26   1       2     1 1 0 0 2 1   E A B F C D
2014/09/27         3       1 1 0 3 2 1   D E A B F C
2014/09/28       4         1 1 4 3 2 1   C D E A B F
2014/09/29     2   6       1 3 4 9 2 1   D C B E A F
2014/09/30   6       3     7 3 4 9 5 1   D A E C B F

I’m looking for a formula for the “Items arranged by cumulative score” rows so that when I add new data I can just copy the last row, put the new details in the “Daily” columns and the formulas will work out the “Cumulative” results (done that bit) and then arrange the Items in order.

Thanks in advance for any suggestions!

---

UPDATE: Added the Less Simplified Example as an attachment Less_Simplified_Example.ods

I’m looking for a formula to work out the results in the shaded area.

The thought occurs that formulae seem to give the result for a single cell, whereas I want a result for a range of cells. Does this mean that I’m going to have to write a macro?

[mariosv]

1- Define a data range for B11:C20 in Menu/Data/Define ranges.
2- Set up sort options in Menu/Data/Sort,

• select columns in the order you like in Sort Criteria tab.
• mark properly if Range contains column labels.
• mark “Copy sort to” an enter destination range, all columns are copied.

[H.G]

Hi mariosv, thanks for your answer, it gives me something. I’d missed/overlooked “Copy sort results to:” and didn’t think that you could do that.

Unfortunately it doesn’t fully solve my problem, I guess maybe I oversimplified my example. When I said that there are multiple co-related lists of attributes I meant that, in your example, there would be “data3” in column C and “data4” in column D.

Continued in following comment …

[H.G]

Then I want to do sorts on “data1” & “data2” (as you have), then on “data1” & “data3” and then on “data1” & “data4”. As far as I can make out Menu/Data/Define ranges won’t let me select non-adjacent columns; and, indeed, if I do so on the sheet then Menu/Data/Sort is greyed out and unavailable.

Continued in following comment …

[H.G]

I guess I could duplicate “data1” next to each of “data3” & “data4” and then use your solution, but I’m looking for a formula to do the Menu/Data/Sort so that I don’t have to do it manually everytime I update the data (`cos that’s what computers are for right? ).

I was going to update the question with an attachment of a less simplified example to show my problem, but it says “>3 points required to upload files” (Is that Karma points?).

[mariosv]

I upvote your question, try to upload now.

[H.G]

Hi mariosv, thanks again. I’ve updated the question adding the attachment Less_Simplified_Example.ods