How to transpose in excel file

  • Thread starter Thread starter vilfood
  • Start date Start date
V

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
 
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
 
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)
 
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)
 
Back
Top