Excel Convert Rows to Columns

Asked

Viewed 472 times

2

I have a spreadsheet with several lines this way

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

That is, there 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 ? in this way

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

  • Do you want this to be done automatically? with VBA (I don’t know if it’s the right language)? more details so that other members can help you

  • Question: The camos are always the same and all contacts have all the fields or risk a certain contact not having PHONE, for example. I ask this because there is a very simple solution if everything is standardized.

1 answer

0


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

My explanation will be based on the image below.

Excel

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 TELEPHONE.

These columns store the row where each NAME, SURNAME and TELEPHONE are 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. So D2 = 1, D3 = D2 + 3, D4 = D3+3 and so on.

  • Do the same for columns E and F, only E should start from 2 and F from 3.

  • Once done, write in columns G,H and I the name of the columns.

  • To finish, write the following formula in cell G2: INDEX($B$1:$B$9;D2)

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

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

  • 1

    Boy, I asked in the stack in English and nobody knew how to answer, I thought it was an unsolved problem, as you said yourself, it’s pretty simple but I wouldn’t have the idea myself. You’ve helped me so much, thank you.

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.