Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel document that looks like this:

Country       Property   Value 
Canada        Capital    Ottawa 
Canada        Population 38 
Canada        Language1  French 
Canada        Language2  English 
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
Canada          Ottawa      38         French    English
United States   Washington  280        English   NA

Is there any way to do this transformation ? Thanks.

share|improve this question
add comment

2 Answers

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)
share|improve this answer
add comment

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)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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