If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|