How to rearrange or reorder the contents of a Sheet (LibreOffice Calc)

Question

How can I rearrange or reorder the contents of a sheet in LibreOffice Calc?

To be more specific, here is my precise situation:

I have a spread sheet of 1 column and 130 rows. Each row contains a quote (a string of characters?).

For example:

The dog ate the biscuits!
Set theory demolishes clouds.
Hello world?

I would like to rearrange/reorder the quotes according to this:

58 89 40 36 39 41 117 99 ...

After this rearranging/reordering, the quote that was originally inside row 58, will then be in row 1; the quote that was originally in row 89, will then be in row 2; what was in row 40 will be in row 3; row 36 to row 4; etc...

I have a total sequence of a set of 130 numbers (1-130) (58 89 40 36 39 41 117 99...) to which I would like the reordering/rearranging to adhere.

How can I use LibreOffice Calc to rearrange/reorder the contents of the sheet according to this new order, this sequence of numbers?

Solution 1

Put your quotes in column A, put your sequence in column B.

In column C, use

=INDEX($A1:$A130,B1)

Copy and paste this down column C.

The index command extracts the contents of a range based on an index you provide. As your sequence is the order you want the column A items in, column C will be populated in the correct order.

Once you have column C, you can copy and paste special to convert it into Text, by checking only text in the paste options.

Solution 2

Here's a possible solution:

• first, create a sort column

• second, append the sort column to your text column and sort it according to the sort column.

Here is an example with a shorter sequence, in single steps, with strings A B C D E F G H I rearranged to I A H B G C F D E (this is rearranging according to the sequence 9 1 8 2 7 3 6 4 5 - String from 9th to first position, 1st to second, 8th to third and so on):

Create a sort row based on sort sequence

• Paste the sort sequence into a text file, save it as order.csv (the .csv is important!);
• open order.csv with OpenOffice.org Calc; you will get a sheet with the cells A1 to I1 holding the sequence.

• add a second row with numbers starting from 1 (just enter 1 and 2, mark the cells and expand the range, so the remaining numbers are auto-filled). Your Sheet will look like this:

  

• transform the table rows into columns:

  • select A1 to I2,
  • cut (CTRL+X)
  • Select A1
  • Paste Special (CTRL+SHIFT+V), in the Paste Special dialogue, select Transpose from Options. The result will look like this:

  

  • Sort the order sequence (Column A), resulting in:

  

  Now, you have the "target rows" in Column B.

Apply sort column to strings column

• Copy Column B from first part to your Calc sheet holding the strings. Result will look like:

  

• Now, you have the target rows next to the strings. Just sort the table based on Column B. Result will look like:

  

Comments

[cyberha4]

i knew there must be a way that's much more easy to implement than my one :-) Only one point to add: i think the formula should be =INDEX(A$1:A$130;B1) so only the second argument changes when copying the formula down the C column.

[Paul]

@tohuwawohu Oh! Yes, rookie mistake. Thanks, I have fixed my answer.