Merge information to one line

Question

I have a big spreadsheet with a lot of values such as 'phone, mail, mobile and so on but the application where I get this from gives me only one of those values per line. For example: if one one contact has three ways to communicate it produces three lines. Please see the files:

before

after

The result should merge the information to one line.

Answer 1

This is a good task for LibreOffice Base.

1. File -> New -> Database. Press "Finish" to use the defaults, and choose a place to save the database file.
2. In the Calc window, click on the "Sheet1" tab (or whichever tab contains the data) and drag to Tables in the Base window. Check "Use first line as column names" and "Create primary key". Then press the "Create" button to create Table1.

3. Go to Queries and click "Create Query in SQL view." Paste the following:

   SELECT DISTINCT T1.Name, T1.Lastname, T2.phone, T3.mail, T4.mobile
   FROM Table1 T1
   LEFT JOIN (SELECT Name, Lastname, phone FROM Table1 WHERE phone IS NOT NULL) T2 ON (
       T2.Name = T1.Name AND T2.Lastname = T1.Lastname)
   LEFT JOIN (SELECT Name, Lastname, mail FROM Table1 WHERE mail IS NOT NULL) T3 ON (
       T3.Name = T1.Name AND T3.Lastname = T1.Lastname)
   LEFT JOIN (SELECT Name, Lastname, mobile FROM Table1 WHERE mobile IS NOT NULL) T4 ON (
       T4.Name = T1.Name AND T4.Lastname = T1.Lastname)
   

4. Save the query as "Query1" and close the query.

5. Right-click on "Query1" and Copy. Go back into Calc and paste the results.

Name  Lastname  phone           mail             mobile
John  Doe       +49304856421    [email protected]     +491704
Lisa  Miller    +1327464361785  [email protected]