Combine every two lines/rows contents into one

[lnrtbrn]

Hello everybody!

I am having trouble figuring this out, and it could help us tremendously.
Since one of our customers uses very specialized software to create a list of articles they send us (no excel or calc table, proprietary to the company/in-house solution), my only choice is to extract a csv file from it using software like Tabula.

Now when i open the file in calc, everything is neat, except the fact that one row has been split into two.

I am not great with excel or calc, and wanted to know if theres a way to script something that packs every two lines/rows into one.

I only find examples where you’d have to select the cells in question and then consolidate, which would be the opposite of saving time here.

Hope you can help me.

[JohnSUN]

Welcome @lnrtbrn !

@lnrtbrn

one row has been split into two

This phrase can mean at least three options: the string is split into two parts into two adjacent cells horizontally, the string is split into two parts into two consecutive cells vertically, the string is in one cell but contains a newline character. What option are you asking about?

[lnrtbrn]

Hi there, thanks for your reply and sorry for not being clear.

On the left you can clearly see how it should be distributed by the lines
On the right is Libre’s interpretation of the csv. So, I’d need something to tell calc “Take every two rows and combine/consolidate them”

Hope you are safe

[JohnSUN]

Ah, it’s not difficult. I hope that cells D2, D4 and so on are actually empty, and not just look empty.
Simply write in cell I1 the formula =IF($D1="";"";A1&CHAR(10)&A2) and in cell K1 the formula =IF($D1="";"";C1&CHAR(10)&C2).
Select again cell I1 and press Ctrl+Shift+End (select cells to the end of the data). Now press Ctrl+D (fill in the selected range with these formulas). Now Ctrl+X (cut these cells to the clipboard), Ctrl+Home (jump to cell A1) and Ctrl+Shift+V (Paste special).
Here carefully! Check “Skip empty cells” and click “Values ​​only”

Now it is enough to select and sort the table and all empty lines will move down, “disappear”.

Of course, if you want to get rid of “double lines” completely, you can replace CHAR(10) with a space in both formulas:

=IF($D1="";"";A1&" "&A2)
=IF($D1="";"";C1&" "&C2)