Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 28 Sep 2014 19:43:28 +0200Calc: 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?https://ask.libreoffice.org/en/question/40398/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/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:
<PRE>
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
</PRE>
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](/upfiles/14121620899549537.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?
H.GSun, 28 Sep 2014 19:43:28 +0200https://ask.libreoffice.org/en/question/40398/