How to transpose in excel file

[vilfood]

Hi I would like to make a transpose like this?
From
House Defect How much
#1 Broken Sinks 1000
#1 Bad Paint 2000
#1 Bad Lighting 1000
#2 Broken Sinks 0
#2 Bad Paint 2000
#2 Bad Lighting 100

and Transpose into
House Broken Sinks Bad Paint Bad Lighting
#1 1000 2000 1000
#2 0 2000 100

 

[Harald Staff]

A Pivot Table will do that and more.
Set House as row field, Defect as column field and How much as data.

HTH. Best wishes Harald

 

[Jacob Skaria]

Suppose you have the information in ColA,B and C

1. Select the range in Col A including the header.
2. From menu Data>Filter>Advanced Filter>Copy to another location
3. In copy to I have selected G1 and check 'Unique records only'
4. Click OK will give you the unique list in Col G

'Repeat the same for ColB to generate a unique list of ColB values
1. Select the range in Col B including the header.
2. From menu Data>Filter>Advanced Filter>Copy to another location
3. In copy to I have selected H1 and check 'Unique records only'
4. Click OK will give you the unique list in Col H
5. Copy and pastespecial transpose so that you can have this table as below
with column headers in Row 1 and Column headers in ColG

House Broken Sinks Bad Paint Bad Lighting
#1
#2

Now in cell H2 apply the below formula and copy down and across as required

=SUMPRODUCT(($A$1:$A$1000=$F2)*($B$1:$B$1000=G$1),$C$1:$C$1000)

 

[Jacob Skaria]

Correction..from step5

5. Copy and pastespecial transpose so that you can have this table as below
with column headers in Row 1 and Row headers of the new table in ColG as below

House Broken Sinks Bad Paint Bad Lighting
#1
#2

Now in cell H2 apply the below formula and copy down and across as required

=SUMPRODUCT(($A$1:$A$1000=$G2)*($B$1:$B$1000=H$1),$C$1:$C$1000)