# Microsoft Excel Data Transformation

I have an Excel document that looks like this:

``````Country       Property   Value
United States Capital    Washington
United States Population 280
United States Language1  English
United States Language2  NA
``````

I want to re-arrange this so that it looks like this:

``````Country         Capital     Population Language1 Language2
United States   Washington  280        English   NA
``````

Is there any way to do this transformation ? Thanks.

-

I think the formula may actually be...

``````=OFFSET(Sheet1!\$A\$1,MIN(IF((Sheet1!\$A\$2:\$A\$1000=\$A2)*(Sheet1!\$B\$2:\$B\$1000=B\$1),ROW(Sheet1!\$A\$2:\$A\$1000),FALSE))-1,2,-1)
``````
-

Note: Assuming the first table with columns Country, Property and Value is stored in Sheet1.

1. Create a new sheet with columns Country, Capital, Population, Language1, Language2
2. Populate the first column with distinct countries
1. Copy all countries from Sheet1
2. Use the delete duplicate value function
3. Enter the array formula below into cell B2 in the new sheet. Remember to use Ctrl + Shift + Enter when entering the formula to make it an array formula (noticable by the curly brackets)
4. Copy/paste the formula from B2 to the remaining cells

=OFFSET(Sheet1!\$A\$1;MIN(IF((Sheet1!\$A\$2:\$A\$1000=\$A2)*(Sheet1!\$B\$2:\$B\$1000=B\$1);ROW(Sheet1!\$A\$2:\$A\$1000);FALSE))-1;2)

(The formula retrieves the first row number of the source data matching both country and property name, and then use the offset function to retrieve the property value from that row)

-