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



0
Utf
12/23/2009 8:04:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
737 Views

Similar Articles

[PageSpeed] 10

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" <vilfood@discussions.microsoft.com> wrote in message 
news:25F207D6-E399-4BC5-B395-2A8287ABF01C@microsoft.com...
> 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
>
>
> 

0
Harald
12/23/2009 8:21:40 AM
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


"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
> 
> 
> 
0
Utf
12/23/2009 8:23:01 AM
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 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
> 
> 
> "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
> > 
> > 
> > 
0
Utf
12/23/2009 8:26:01 AM
Reply:

Similar Artilces:

excel viewer #2
Won't microsoft provide a simple, small and fresh Excel Viewer? Version 97/2k is obsolete, using the 100% of processor time and simply: it's not good. I think not everybody buys Excel and Office so there should be always fresh viewers (like Adobe Acrobat Reader, etc) best regards, on "on" <on@vnet.hu> wrote in message news:O1DNPQveEHA.3988@tk2msftngp13.phx.gbl... > Won't microsoft provide a simple, small and fresh Excel Viewer? > Version 97/2k is obsolete, using the 100% of processor time > and simply: it's not good. > I think not everybody buys ...

Quick print - error
I understand that the settings are stored in Dex.INI. All users have read/write access to this file - however we can only use Quick Print if the Adminstrator is logged into Windows. It must be a security issue however don't know where else to look. Any clues anyone? C Catherine, Can you give full access to the Great Plains directory to your users. Gerald "Catherine the Brit" wrote: > I understand that the settings are stored in Dex.INI. All users have > read/write access to this file - however we can only use Quick Print if the > Adminstrator is logged into...

Unable to read file #5
Dear Officer In Charge, I am using Office 2007 Professional. I have one file (I named this as target file) that contains some data that is linked to another file with protected hidden sheets (I named this file as source file). When I open the target file without opening the source file and update the links, the system will prompt me "Unable to read file.". Then, the system will help me to update the links in the file and the value in the target file becomes "#REF". However, if I open the source file, the links in the target file is refreshed with correct value. Wo...

why does excel ignore page formating after selecting rows to repe.
I have a Pivot table to which I wish to print excluding the "Page" items but include the Table headings. I have also set the document to print 1 page wide by up to 999 pages tall. It previews fine except for when I select the table headings (row $3$3) to be repeated at the top. After I make that selection, the preview shows all the data being printed on only 5 pages instead of 31. I don't know what I am missing to cause this unexpected result. ...

Optimum file formats for import of xcel data to PDA?
I want to import an excel based file to a PDA. What is the best format to save the parent file in, so as to optimise the file size (or is there an optimal format?). Presently as an excel based file the size is 4M! I can remove some content which will reduce it - but not more than 1M to 1.5M ... Any idea? Don- Hi It really depends how much information you want. If you only need to get the actual data in (rather than the format too) then use CSV. This will probably be as small as you can get. -- Andy. "Don Niall" <donniall@aol.com> wrote in message news:2bcde01c468ec...

Are there any excel template for creating POIs for Garmin units
I'm looking for a template in which I can create points of interest for a Garmin 340c. Not that I know of but have you tried the free POI software from Garmin? http://www8.garmin.com/products/poiloader/ Gord Dibben MS Excel MVP On Sat, 21 Jul 2007 11:24:00 -0700, Fremen <Fremen@discussions.microsoft.com> wrote: >I'm looking for a template in which I can create points of interest for a >Garmin 340c. ...

How do you create dual drop down menus in excel 2000?
I can create a single drop down menu, but need to add a qualifier next to lists on menu. HELP!!!!!! If drop-down created with Data Validation>List, check out Debra Dalgleish's site for dependent lists. http://www.contextures.on.ca/xlDataVal02.html Gord Dibben Excel MVP On Tue, 11 Jan 2005 10:51:03 -0800, "Phlashh" <Phlashh@discussions.microsoft.com> wrote: >I can create a single drop down menu, but need to add a qualifier next to >lists on menu. HELP!!!!!! ...

multiple 3D equations in one excel surface chart
hi all i'm trying to draw in excel multiple 3d equtions(x,y,z) inside of one excel surface chart does anyone have simple way of doing it i have the link for one equation chart but from there i'm little bit lost http://www.tushar-mehta.com/excel/charts/3d_surface/ What's the problem? If your data is of 3D type, just plot it as such. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "thread" wrote: > hi all > i'm trying to draw in excel multiple 3d equtions(x,y,z) inside of one > excel...

VB code to import Excel sheet
I'm looking for the VB code to copy an Excel Sheet to an Access 2007 table. The sheet in is a know location. I also want to clear the Access table prior to importing the Excel Sheet. Thanks Take a look at what Ken Snell has at http://www.accessmvp.com/KDSnell/EXCEL_Import.htm -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "BobC" <Bob.CallenNoSpamm@cox.net> wrote in message news:TBgZm.57278$ZF3.39461@newsfe13.iad... > I'm looking for the VB code to copy an Excel Sheet to an Access 2007 > table. The she...

Excel
When using MS Works I can enter letters and numbers in a cell and the total of the cells in a row will be calculated correctly(for my particular requirement) by ignoring anyone of the cells in the row which has letters and numbers together. All the cells are formated as 'General'. When I try to do this task in Excel it gives an error in the result cell. Note that any cell may have a mixture of letters and cells. Any help would be appreciated. Thank you James Submitted via EggHeadCafe - Software Developer Portal of Choice Bing Search RSS with Silverlight 3 RIA Domain Ser...

Pocket Excel Graphic Lost
I developed a spreadsheet that included my company's logo in Excel 2002. I saved it to my PPC file for conversion by ActiveSync for my WinCE 2002. I want to use the spreadsheet from my PPC. I tried both the .png and .jpg image file types. The graphic is not on the Pocket Excel version of the document. Is there a problem with Pocket Excel keeping graphics? If not, what image format should I use for graphics in Pocket Excel? Or, is there a switch I need to use? Sorry, the correct e-mail address is bschmidt@westechnology.net >-----Original Message----- >I developed a spreads...

Planning training in Excel
I have been tasked to organise training for my company and they have asked me to use MS Project, which has totally blown my mind. Having done some investigating Project will not give me the answers that I need surprisingly, but I am hoping Excel will I am really trying hard to figure a way of schedulign training for 200 individuals, with 20 different modules (fixed dates) and take into account holidays. So for instance Joe Bloggs needs to attend Module A, B & D over the next 3 months, but he also has 2 weeks holiday booked. There are four other people in his department but I can only all...

File size is larger in XP than in Excel 97
Has anyone noticed that excel files that contain imported data are larger in XP than in 97? Why, any ideas? Thanks Kathy .. ...

copy paste problems for chart from excel to power point, solution
I am really having troubles copying excel charts to power point, the charts are getting messed up after copying and pasting to power point. I tried everyting, turned off auto scale and selected the don't move or resize with cells option and still having the same problem, any idea how to fix that? Thanks! On Thu, 1 Mar 2007, in microsoft.public.excel.charting, Jean <Jean@discussions.microsoft.com> said: >I am really having troubles copying excel charts to power point, the charts >are getting messed up after copying and pasting to power point. I tried >everyting, turned ...

Excel interfering with dates
How can I stop Excel 2007 interfering with dates? I have a variety of dates to enter. Some are before 1900, some after. Some are partial (with no year given); some are just a year. All are entered in the form 1 Sep 1854 (with some parts missing). I have defined the format of the cells to be Text/String but Excel ignores that and corrupts the dates by assuming, if no year is present, that they are 2011 and if they are after 1900 it converts them to 01/01/2011 format. How can I force Excel to do what it is told and treat them all as Text? -- Chris Watts chris@watts-bros.co.uk (synonymous...

How to keep the print output colour setting for the same file?
I have two Excel files saved in a network drive. And I access the files in two different servers in a separate location. I have no problem printing both the files with the correct setting (especially the print output colour) that I saved with the files using one of the servers. However, when I try to print the same files in the other server; one of the files print setting has changed to black and white. These files are the same files that I opened in a same network drive. Wondering why one file print setting works and the other doesn't. Can anyone can help? ...

Access 2000 /Jet/ BLOB/mdb file structure
Could someone direct me to a reference which gives a byte-level description of mdb files written by MS Access 2000, including whatever native internal variables or pointers there are and their typical locations? Thank you. Richard Bonomo rbonomoXXX@madisonwaterYYY.orgZZZ_drop_caps Richard Bonomo <Richard Bonomo@discussions.microsoft.com> wrote: >Could someone direct me to a reference which gives a byte-level description >of mdb files written by MS Access 2000, including whatever native internal >variables or pointers there are and their typical locations? Thank you. Mi...

Excel 97 #3
I have a list of timecodes, examples, ESD1.2 ESD12.3.2 ESD12.4.12 ESD4.4.2 ESD13.1.1 ESD2.2.6 ESD13.1.2 ESD4.3.3 ESD13.2 ESD14.2.1 ESD2.1.3 ESD4.3.2 ESD2.2 ESD4.4.4 they are formatted as General Any ideas how to list them in order of number -- Message posted from http://www.ExcelForum.com You can convert the text to time with the following formula :- =TIMEVALUE(SUBSTITUTE(RIGHT(A1,LEN(A1)-3),".",":")) Format as HH:MM:S -- Message posted from http://www.ExcelForum.com ...

Creating a PST file at the server level
Tech guys, How do I create this PST (Archive) file from the server level. Am on office 2003 - exchange 5.5 ? A pst file can only be created by Outlook or by using the Exmerge tool against a mailbox/message store. You need to explain with a bit more detail what you are trying to do. "Amwendwa" <alphonse.mwanzia@hotmail.com> wrote in message news:B672CBBD-A73C-4B84-9A1F-F5FA91E43259@microsoft.com... > Tech guys, How do I create this PST (Archive) file from the server level. > Am > on office 2003 - exchange 5.5 ? Hi Noel, When we talk of Exmerge tool, what are ...

Preflighting Excel files
Is there a way to preflight (check for problems before printing) Excel files (I already know that Flightcheck doesn't work). Any suggestions? Martin Martin There is a Before_Print event macro that you can use for such checking. Of course, that macro is blank. You would have to write in the necessary code to do the checks you want. Can you list the checks and the criteria for each? Writing the code is straight forward once you have listed the checks and criteria. HTH Otto "martindaylor" <anonymous@discussions.microsoft.com> wrote in message news:21d201c49...

How do I change the thickness of a bottom boarder in Excel 2002?
In Word you can change the thickness of the line used in boarders - but I can not change the thickness in Excel. Is there any way to do this? Thanks. try Sub bottomborder() ActiveCell.Borders(xlEdgeBottom).Weight = xlThick 'xlmedium or xlthin End Sub -- Don Guillett SalesAid Software donaldb@281.com "Dawn" <Dawn@discussions.microsoft.com> wrote in message news:B24E39F0-58BF-47AD-AE42-716CBA2C9C54@microsoft.com... > In Word you can change the thickness of the line used in boarders - but I can > not change the thickness in Excel. Is there any way to do this? ...

How to Copy Charts and Tables from Excel to Publisher
Hi, Everyone: I'm a new user of Publisher 2003, and I'm working on a 100-page or so document with text, photos, charts, and tables. I would appreciate advice about copying charts and tables from an Excel workbook and reducing file size. Please also recommend which is the correct file format to use when saving charts, tables, and photos as "pictures". My Excel workbook has numerous worksheets. (1) When I try to "copy and paste" a single Excel chart from one worksheet into Publisher, I get an "object" which shows ALL the worksheets if I double...

break links crashes Excel 2002 #2
It appears that it may just be one workbook file. I tried it wit another file and it did not crash. We primarily use one Excel workboo as a template. Copies of this file are probably 95% of all the Exce files we use so it is a big problem for us. Assuming that it is just this one important file, any ideas how to fi this workbook so that we can break the links with the toolbar command -- ED ----------------------------------------------------------------------- EDS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=879 View this thread: http://www.excelforum.com...

File Open Initial Directory
Hi, I have a simple question: How does an application choose the initial directory to open when the user selects File->Open? I mean, when first run, the application usually opens the File Open dialog in the "My Documents" folder, but the next time, it will open in the last folder used by that application. How does the application know which folder was last used? I thought it could be from the MRU files, but I deleted them from the registry and my application still knows which was the last folder used. Any ideas? Thanks Mikel It uses the current directory, whatever that is. N...

MS Money Old Files
I have been runing MS Money 97 the file format is .mmy, I have a new computer and upgraded to Microsoft Money Essentials. But now I can't seem to get my old files to transfer over. How can I save my old files so that they can be used by the new program or I am out of luck and have to stick with MS Money 97?? MEss does not deal with existing Money files. It's strictly for new Money users who don't really want to go to the trouble to manage their finances but want to think they are doing something along those lines. Upgrade to Deluxe. You'll be happier. And you'll be ...