Reputation: 11

Excel condense data

I have data in an excel spreadsheet in the form of

John | Age      | 24  
John | Location | Australia  
John | Salary   | $5000  
Sue  | Age      | 28   
Sue  | Location | England  
Sue  | Salary   | $6000

Is there an easy function to sort into a table along the lines of:

Name | Age   |  Location  |  Salary  
John | 24    | Australia  | $5000  
Sue  | 28    | England    | $6000

Thanks in advance!

Upvotes: 1

Answers (3)

WeAreOne

Reputation: 1343

=ROWS($A$6:A6)*3
=INDEX($A$6:$C$11;E6;1)
=INDEX($A$6:$C$11;(E6)-2;3)
=INDEX($A$6:$C$11;(E6)-1;3)
=INDEX($A$6:$C$11;E6;3)

Upvotes: 0

Harun24hr

Reputation: 37155

Formula in F2 cell I used-

=INDEX($C$1:$C$6,MAX(($A$1:$A$6=$E2)*($B$1:$B$6=F$1)*(ROW($C$1:$C$6))))

If you have Excel-365 then you can use UNIQUE() function to E2 cell.

=UNIQUE(A1:A6)

Upvotes: 0

Skin

Reputation: 11317

Use PowerQuery ...! Go to Data -> Get Data -> From File -> From Workbook and then follow these steps.

Rename first column to Name ...

Select Column2 and Pivot the data ...

Use these options in the pivot ...

Voila!

Select Close and Load to populate a new sheet ...

Result ...