Convert Rows to Columns in Excel 2010

Question

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!

Answer 1

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:

=VLOOKUP($F2&G$1;$B$1:$D$12;3;FALSE)

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!