repeat a merge of cells through multiple rows

Discuss the spreadsheet application
Post Reply
highway
Posts: 3
Joined: Tue Mar 25, 2008 3:44 pm

repeat a merge of cells through multiple rows

Post by highway »

Hi,
I have a spreadsheet with multiple columns and rows of data.
I want to merge the contents of two of the columns into one column, without losing the contents of either.

I can do this easily on a row my row basis manually, by highlighting the two cells (e.g. A1 and A2), clicking on the merge cells button in the toolbar, and answering yes to the "Should the contents of the hidden cells be moved into the first cell" dialogue box.

The problem is that i need to do this for approx 12000 rows (i.e. B1/B2, C1/C2, D1/D2, etc, etc)

Does anyone know of a method of doing this automatically. Also, i obviously would need to automate the clicking of "yes" at the dialogue box mentioned above. I tried recording a macro, which works for the few cells that i manually did while recording, but i cannot figure out how to get it to transcend through all the rows merging the two cells in each row as it goes.

Any help/suggestions appreciated.
Thank you very much.
Highway.
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: repeat a merge of cells through multiple rows

Post by Villeroy »

Do the merge by formula:
=$A1&" "&$B1
in C1 concatenates the contents of this row's A and B with intermediate space. Copy down.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
highway
Posts: 3
Joined: Tue Mar 25, 2008 3:44 pm

Re: repeat a merge of cells through multiple rows

Post by highway »

Thank you for the prompt response, that formula worked brilliantly.

I'm very grateful.
Highway.
Post Reply