OpenOffice: Merge and Sort Data

Posted on May 14, 2013  •  3 minutes  • 453 words

You need to construct an array using a list of pre-existing values but firstly have to sort the order of that list.

There are plenty of alternatives but I enjoy using OpenOffice for these simple string inclusions.

OpenOffice is open source and available for use on all major operating systems.

Collect Data

Copy your list of values.

Select the top cell in the column you want your list to reside.

When you paste the values in OpenOffice, as long as they’re separated by a line break, OpenOffice will automatically separate each value into a new row of whichever column you’ve designated.

Sort

Sort the list by hilighting the data and perform the following:

Data > Sort > Options

Unselect the checkbox labeled “Range contains column labels.”

Click OK

This option prevents column headers from being sorted with the rest of the data. Since we don’t have any column headers, like in a CSV file, we need to deactivate this feature.

Prepare

Create the prepending or appending content at the top of the column to the left or right of the working list.

Point your cursor to the bottom-right corner of the cell you just filled, hold down your left mouse button, and move downwards to the bottom of the working column.

This action will copy the active cell to all of the other cells touched by your hover action. Do this over again for further columns.

Merge

Use the following Calc function in the column that you want the new data to be returned.

Concatenate: Combines several text strings into one string.

=CONCATENATE( A1; ":"; B1; ":"; C1 )

or you can use the simple version:

= A1 & ":" & B1 & ":" & C1

Where cell A1 contains ", cell B1 contains actionmailer, and cell C1 contains ", returns "actionamailer",.

Hit return

Just like when you copied the prep data, grab the bottom-right corner of the working cell and drag it directly down to the target end point.

This will copy the concatenation function across all of the modified cells. So if you change a value in another column, from its row, OpenOffice will generate new content for the concatenated cell.

Your column is now ready for inclusion in production-ready code. Just highlight the readied cells, copy, and paste into your program editor of choice.

Further Reading

When you paste the new column into your text editor you might notice special quotes that OpenOffice magicly replaces upon creation. You can disable the automatic conversion of ASCII “straight” quotes into typographic “curly” quotes by going to:

Tools > AutoCorrect Options > Localized Options

Scan down to the container marked “Double Quotes” and make sure the checkbox labeled “Replace” is unchecked.

Click OK