Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel TRANSPOSE | Excel Worksheet Functions | |||
Transpose Crosstab Data in Excel 2000 to flat file for use in MS A | Excel Discussion (Misc queries) | |||
Hi all i need some help on excel transpose | Excel Worksheet Functions | |||
Hi all need some help in excel transpose | Excel Discussion (Misc queries) | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions |