Excel Converter Rows in Columns [closed]

2

I have a multi-line spreadsheet like this

NOME:      | EDSON
SOBRENOME: | CARLOS
TELEFONE:  | 1299232322
NOME:      | TESTE
SOBRENOME: | AMANDEU
TELEFONE:  | 1499252322
NOME:      | JOSE
SOBRENOME: | PASTOR
TELEFONE:  | 1299232322

That is, they are 2 columns, one with the name of the field and the other with the value, how do I put each one in its columns? this way

NOME    | SOBRENOME   | TELEFONE
EDSON   | CARLOS      | 1299232322
TESTE   | AMANDEU     | 1499252322
JOSE    | PASTOR      | 1299232322
    
asked by anonymous 15.08.2015 / 00:51

1 answer

0

Since everything is standardized, that is, all contacts have all the fields (NAME, SURNAME and PHONE), there is a very simple and fast solution.

My explanation will be based on the image below.

The solution is based on three auxiliary columns, they are D, E, F. Column D is for NAME, column E for SURNAME and column F for PHONE.

These columns hold the line where each NAME, NUMBER, and PHONE respectively.

    To fill column D, start with the fixed number 1 and then make a formula that increments the value of the previous cell with 3. Thus, D2 = 1, D3 = D2 + 3, D4 = D3 + 3 and so on.

  • Do the same for columns E and F, but E must start with 2 and F of 3.

  • Once you have done this, type columns in columns G, H, and I.

  • Finally, type the following formula in cell G2: INDEX ($ B $ 1: $ B $ 9; D2)

  • Copy and paste this formula into the rest of the columns.

  • To remove the INDEX formulas, copy the range and Paste Special (values only).

15.08.2015 / 14:14