How to transpose in excel file
 

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

How to transpose in excel file
 

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

"vilfood" wrote in message
...
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

How to transpose in excel file
 

Suppose you have the information in ColA,B and C

1. Select the range in Col A including the header.
2. From menu DataFilterAdvanced FilterCopy 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 DataFilterAdvanced FilterCopy 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


"vilfood" wrote:

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

How to transpose in excel file
 

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)

--
Jacob


"Jacob Skaria" wrote:

Suppose you have the information in ColA,B and C

1. Select the range in Col A including the header.
2. From menu DataFilterAdvanced FilterCopy 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 DataFilterAdvanced FilterCopy 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


"vilfood" wrote:

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