I have like the following set of data in Excel:

Name    a
Age 22
Address add1

Name    b
Age 23
Address add2

Name    c
Age 24
Address add3
City        XX

I need to convert this data into columns like this:

Name    Age Address City
a   22  add1    
b   23  add2    
c   24  add3    XX

The transpose does not help in this case, right? Thanks a lot!


You should include an identificator for each case as a new first column (for instacce can the name if you know it´s unique) putted together with the field involved as follows:

1Name   Name     a
1Age     Age     22
1Address Address add1

2Name    Name    b
2Age     Age     23
2Address Address add2

3Name    Name    c
3Age     Age     24
3Address Address add3
3City   City     xx

The you can buils the desidered matrix with fields in columns and identificators in the rows and use vlookup to fild the values with the following formula:


enter image description here

Look that the first argument put together the identificator (row of the new matrix) with the fild (column of the new matrix), the second argument indicates the range, the third the column we are looking for, and it´s very important to put FALSE in the last parameter.

Hope it helps!

  • I have over a thousand rows and it is difficult to add numbers as you mention above. Thanks anyway – Nazaf Apr 6 '11 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.