Is it possible in Open/LibreOffice Calc to take the values from a set of two or more rows, and display them in one column? In order words, make a separate column which derives its values from the original two rows without destroying or altering the original rows and their data.

Also, at the same time, assuming the values in the original two rows are essentially random, can they be placed in order?

Just to be extra clear: I don't mean a one time, manual copy, paste, and sort operation. I mean for the resulting column to contain formulas that will display what is in the original two rows and be updated when the data in the original rows changes.

So, for example, I have the following two rows of dates:

enter image description here

I'd like to reorder them into a column like so:

enter image description here

Is this possible?

Bonus: Can the resulting column have duplicate dates removed? Like so:

enter image description here


In your example you can do as follows: Select A1 through D2, or wherever the lower right corner of your data range is. Use Data > Define Range to give it a name such as DATES. Then select the cell that will be first in your column and enter the formula =SMALL(DATES, ROW() - 3). Finally, select that cell and a sufficient number below it to contain your data. Choose Edit > Fill > Down.

(This replaces my earlier answer. Thanks for your clarification.)

| improve this answer | |
  • Thank you for responding, but this isn't quite what I meant. I'm sorry I was unclear. What I mean is that I am looking for a way to make the column stay in sync with the original rows, so that if any changes happen in the rows, that will be reflected in the column. Copying and pasting is a one time and manual action. I'm trying to set up something automatic that will always reflect the latest data when there are changes in the source rows. – Questioner Mar 15 '12 at 3:20
  • Sweet! Thank you for updating and providing a clear solution. That works really well. The only thing left is the removal of duplicate dates. Is there an easy fix for that or should I open a new question? – Questioner Mar 15 '12 at 4:43
  • Right, I couldn't think of a slick fix for that. I did notice that the Data > Filter > Standard Filter command with the No duplication option only has the effect of hiding (collapsing) rows that contain duplicates. So when you update the data, you could perhaps select those rows and hit Show, then repeat the filter operation to hide the duplicates from the current data. – minopret Mar 15 '12 at 5:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.