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    john@doe.com     +491704
Lisa  Miller    +1327464361785  mail@exmple.com

Answer 2

Assuming that the data is laid out in the way presented in the "before" image you can use formulas to fill in the missing information and then remove duplicates.

First insert a new column "ID" as column A that has a unique identifier, hopefully First/Last Name concatenated will suffice:

=CONCATENATE(A2,"|",B2)

Autofill that down for every row.

Next use a formula to fill in missing information:

=IF(LEFT(C2,1)="", VLOOKUP(A2, A:C, 3, FALSE),C2)

This formula checks if the first character from the left in the cell matches nothing, basically that the cell is blank, and if so does a vlookup on the "ID" column, finding the first match of "John|Doe" it returns the phone number, otherwise it just leaves the value in there that it currently has.

If you drag this across and modify the vlookup for the other columns, eg email would be

=IF(LEFT(D2,1)="", VLOOKUP(A2, A:D, 4, FALSE),D2)

then remove all duplicate records you will be left with the "after" image you want.