Convert Excel Tables to Pivot Table Lists

Excel Tables to Pivot Lists

Hello,

I'm trying to convert excel tables into pivot table lists and I am
looking for a method to do this besides cutting and pasting.  The table
has 6 columns (see below) with count of product for each year e.g. xxx1
prod1 100 in Yr1, 200 in Yr2, 300 in Yr3 etc.  I want to end up with a
4 column list like, (see "Get into pivot table list in this form)

Thanks


Home....Prod....Yr1....Yr2....Yr3....Yr4
xxx1.....prod1....100....200....300....400
xxx2.....prod2....110....210....310....410
xxx1.....prod3....120....220....320....420
xxx2.....prod4....130....230....330....430

Get into pivot table list in this form:

Home....Prod....Yr.......Cnt
xxx1.....prod1...Yr1   100
xxx2.....prod2...Yr1   110
xxx1.....prod1...Yr2   200
xxx2.....prod1...Yr2   210
xxx1.....prod3...Yr2   220
xxx2.....prod4...Yr2   230

Thanks
JB

0
jbesr1230 (21)
8/10/2005 10:09:08 PM
excel 39879 articles. 2 followers. Follow

2 Replies
568 Views

Similar Articles

[PageSpeed] 11

Real easy trick to that:-

http://j-walk.com/ss/excel/usertips/tip068.htm

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

------------------------------�------------------------------�----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------�------------------------------�----------------

<jbesr1230@hotmail.com> wrote in message 
news:1123711748.775229.113370@g44g2000cwa.googlegroups.com...
> Excel Tables to Pivot Lists
>
> Hello,
>
> I'm trying to convert excel tables into pivot table lists and I am
> looking for a method to do this besides cutting and pasting.  The table
> has 6 columns (see below) with count of product for each year e.g. xxx1
> prod1 100 in Yr1, 200 in Yr2, 300 in Yr3 etc.  I want to end up with a
> 4 column list like, (see "Get into pivot table list in this form)
>
> Thanks
>
>
> Home....Prod....Yr1....Yr2....Yr3....Yr4
> xxx1.....prod1....100....200....300....400
> xxx2.....prod2....110....210....310....410
> xxx1.....prod3....120....220....320....420
> xxx2.....prod4....130....230....330....430
>
> Get into pivot table list in this form:
>
> Home....Prod....Yr.......Cnt
> xxx1.....prod1...Yr1   100
> xxx2.....prod2...Yr1   110
> xxx1.....prod1...Yr2   200
> xxx2.....prod1...Yr2   210
> xxx1.....prod3...Yr2   220
> xxx2.....prod4...Yr2   230
>
> Thanks
> JB
> 


0
ken.wright (2489)
8/10/2005 10:16:38 PM
Just realised you had two columns at the start of your data, so can still be 
done but slightly more complicated:-

ON A COPY OF YOUR DATA:-

Insert a column at the start of your data that concatenates Col1 and Col2 
plus a delimiter, eg assuming you added Col A, use

=B2&"["&C2

and copy down as far as needed.  Now copy ColA and paste special as values, 
delete Cols B and C, and then follow the instructions on John's site.

When done, insert a column between A&B, select Col A, do data / text to 
Columns, Delimited, tick 'other' as delimiter and then put [ in the box and 
hit Next and Finish.

Regards
              Ken......................


"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message 
news:%23TTmukfnFHA.1948@TK2MSFTNGP12.phx.gbl...
> Real easy trick to that:-
>
> http://j-walk.com/ss/excel/usertips/tip068.htm
>
> -- 
> Regards
>           Ken.......................    Microsoft MVP - Excel
>              Sys Spec - Win XP Pro /  XL 97/00/02/03
>
> ------------------------------�------------------------------�----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------�------------------------------�----------------
>
> <jbesr1230@hotmail.com> wrote in message 
> news:1123711748.775229.113370@g44g2000cwa.googlegroups.com...
>> Excel Tables to Pivot Lists
>>
>> Hello,
>>
>> I'm trying to convert excel tables into pivot table lists and I am
>> looking for a method to do this besides cutting and pasting.  The table
>> has 6 columns (see below) with count of product for each year e.g. xxx1
>> prod1 100 in Yr1, 200 in Yr2, 300 in Yr3 etc.  I want to end up with a
>> 4 column list like, (see "Get into pivot table list in this form)
>>
>> Thanks
>>
>>
>> Home....Prod....Yr1....Yr2....Yr3....Yr4
>> xxx1.....prod1....100....200....300....400
>> xxx2.....prod2....110....210....310....410
>> xxx1.....prod3....120....220....320....420
>> xxx2.....prod4....130....230....330....430
>>
>> Get into pivot table list in this form:
>>
>> Home....Prod....Yr.......Cnt
>> xxx1.....prod1...Yr1   100
>> xxx2.....prod2...Yr1   110
>> xxx1.....prod1...Yr2   200
>> xxx2.....prod1...Yr2   210
>> xxx1.....prod3...Yr2   220
>> xxx2.....prod4...Yr2   230
>>
>> Thanks
>> JB
>>
>
> 


0
ken.wright (2489)
8/10/2005 10:28:00 PM
Reply:

Similar Artilces:

Excel VBA problem
I have 2 Excel tables, part of the first table looks as follows. I kee track of parts in production by entering completion dates for eac process they go through. S/N Kitting SMT 1st Assembly Inspection 10001 30-Jun 2-Jul 8-Jul 10002 24-Jun 27-Jun 10003 Note: This table needs to be flexible in terms of the number of S/N' as well the number of processes entered. For example, there could b 15 processes parts need to go through. In addition, there could be 1 identical parts in production. I need help writing a macro which will go through each line and take the latest comple...

Excel 2007 Pivot Table Question
Is there any programming way to change data sources within a Pivot Table, we need to switch from a UNC to a drive letter? Thanks in advance ...

Keep Font format when importing excel into Access
We have an excel spreadsheet that is updated by changing select text to red and highlighting in a text cell. So there are only certain strings where the format is changed and the rest stays as the default format. This select formatting does not carry over when it is imported into an Access database. Is there a way to keep the text format during this process? When working with Access, you would want to recreate the formatting criteria in the reports or forms. The tables in Access only show raw data and do not do any calculations or anything. If you wondering how to recreate th...

Is there an Excel template to reconcile business bank statments?
I am looking for a template that will simplify the bank reconciliation process? Acct Rep wrote: > I am looking for a template that will simplify the bank reconciliation process? How do you mean "simplify"? You will still need to manually tick off the bank statements against the cashbook entries. If you're looking for an AUTOMATED bank rec, then you're looking at comparisons of lists, and that gets complicated if (say) the bank has entered a cheque number correctly but with the wrong amount or vice-versa. It DOES happen - I've seen it. Also that assumes you can d...

How to put in alphabetic list automatically in Excel Spreadsheet?
I am cataloging my DVD's and would like to know how to put the list into alphabetical order once they are on the spreadsheet. I have the list created, but cannot for the life of me remember how to do it, if it is possible that is!! Yes, it has been a long time between uses of the application. Many thanks for any help anyone can offer. Helen :-) Boom wrote on Tue, 11 Jul 2006 03:16:02 -0700: BSL> I am cataloging my DVD's and would like to know how to put BSL> the list into alphabetical order once they are on the BSL> spreadsheet. I have the list created, but cannot for...

How to..use worksheet form and save data to an Excel list
I'm using Excel 2003. Is is possible to create a worksheet form where the user enters data, saves the data and the data is then saved to the last row in an Excel list? Once the data is saved by the end user, I would like the form to refresh so that the end user can continue entering additional data. Does anyone know of any documentation or examples I could review regarding the above as I have never done this before? TIA. Maybe just Data|Form would be sufficient. If that doesn't fit your needs, look at John Walkenbach's enhanced data form: http://j-walk.com/ss/dataform...

How can you attach an e-mail into an excel worksheet?
I want to attach an e-mail into an excel worksheet. The formula I have seen is =EMBED("Package","") But not sure how I can do it. Any ideas? Go to Insert - Object, From file. Navigate to the email you want to embed. Note the options who can choose to change icon or how it is links. You can then double-click on the object to access the email. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "humdul" wrote: > I want to attach an e-mail into an excel worksheet. The formula I have seen > is =EMBED(&qu...

excel-i want it to go to a certain cell when i 1st open the sheet
when i open a spreadsheet that i created, how do i get the cursor to go to the cell i want it to.. in this spreadsheet i will have to enter information in different columns and i want it to automatically go to the first cell under the first column. how can i do this? Hi Lindsey, I can think of two options off the top of my head: 1) Go to the cell, save and close. The next time you open the workbook, the active cell will be the same cell. 2) Use Excel Events. This requires programming Excel using VBA and when done properly can be extremely useful !!! The desired cell will always be t...

BUG?: Excel scatter chart: will not plot x-values
I am trying to make a simple scatter plot. There are two columns of data. Excel will not plot values on the x- axis, only categories. I have entered the ranges for x and y values correctly, and the chart wizard shows it; yet, the chart comes out with the x-axis showing only the points numbered in the order they are entered in the spreadsheet. Oddly enough, if only one y-value is included, it plots properly; the x-axis correctly shows the range of x-values entered, and the single data point shows up at its proper xy location. as soon as I expand the y-range to more points, the x-axis r...

Excel Programming Help.........
I have written an excel spreadsheet. There are six columns which signify the days of the week on one worksheet. I have an allotment to use per week. Sometimes my weeks are made up of 6 days, sometimes 5 or 4 or 3 or 2 or 1. I have successfully written the program to spread the allotment among the correct number of days. Sometimes the day itself varies. Example, Sat, Mon, Tue or Sat, Tue, Wed, Thur, Fri or Wed, Thur, Fri. I have included these calculations as well. And the allotment is equally split among the days. Now I wish to be able to assign variable percents to each day, so that...

Saving Excel with ActiveX controls
Hi, I have some custom activeX controls. I am able to add these controls in Excel (2000) using Control toolbar and they work fine. But when I try to save the Excel file along with these controls, I get the following message. "Microsoft Excel cannot save this workbook because the control named QIChartControl1 does not support saving. To save the workbook, delete this control and then try save again". I tried saving Excel file by deleting just the above control and it worked fine. Any ideas on what could be wrong with the above Activex control? Thanks. ...

Linking to an Excel Pivot Table
Does anyone know how to break the link between an Excel pivot table that uses Access as it's data source without recreating the pivot table? It links to a network drive at work, I need to relink to my local drive but it keeps looking for my "m" drive on my network at work. ...

How can I get excell to ignore line breaks?
I need to be able to have html source in a cell in Excel. My html source has line breaks and blank lines that I'd like to keep in tact. Is there a way to do this? Long explanation: I have to create a file with product information and then save this in a tab delimited file for import into a shopping cart system. There are quite a few fields and all of them are simple except the description field. For the descriptions I have written the html in such a way that you can actually read it ;-), and I'd like to keep it that way (with the line breaks and blank lines and spaces at b...

In Excel, how can I print a long list on 1 page in multiple colum.
I have a long (700 rows) list in an Excel worksheeet. I would like to print the whole list on one page by making the font smaller and by printing it on multiple columns on the page. How can I set this up? Thanks Check out this David McRitchie link for the columns question: http://www.mvps.org/dmcritchie/excel/snakecol.htm "Chuck" wrote: > I have a long (700 rows) list in an Excel worksheeet. I would like to print > the whole list on one page by making the font smaller and by printing it on > multiple columns on the page. How can I set this up? > > Thanks ...

Visible row index after applying a filter in Excel
Does anyone know an excel function that will give the index of the visible row after filtering or sorting a table? Say, I have a table like this: type time place c 12:20 1 f 12:40 2 c 13:30 3 f 14:20 4 Table is sorted with respect to time, and place refers to the index of each row in this order. Now, if I filter by type 'f', the table would still be sorted, but would have only two visible rows. In such a case, I'd like the place row to be like [#NA, 1, #NA, 2], reflecting that rows 1 and 3 are invisible, first place is ro...

Converting Lotus 1-2-3 charts to Excel
I am in the process of converting Lotus 1-2-3 spreadsheets which have charts in them to Excel. After starting Excel and opening the Lotus file in Excel. I do a 'save as' and give it the workbook format. The charts look almost identical. I had to change colors, change some ranges, etc. The one thing I can not figure out is the following: The original Lotus file had the years 1992 through 2003 on the Y-axis. The bar for 1992 was directly above the 1992 and indented about 1/2 inch from the vertcal x-axis. In Excel the chart has the years 1992 through 2003 on the Y-axis BUT the ...

Smartlist export to Excel
-- When exporting large volumes of data to Excel using GP 9.0 we encounter this error: Unhandled object exception: Error calling method 'Cells' EXCEPTION_CLASS_OBJECT_EXCEPTION Unhandled object exception: Unable to access field 'Value' EXCEPTION_CLASS_OBJECT_EXCEPTION I was told by GP support that this is a documented bug that is scheduled to be fixed. Does anyone know if this has in fact been fixed in version 10.0? I have a client that claims they will not upgrade until this is fixed Ladd Sustar Why not do a "test" upgrade for them and see if it works? Ther...

Office 97 to 2003: Excel: enter data w/fixed 2 decimal. 2000. be.
When I enter number "2000." I get 20.00 - yes, set decimals at 2 but earlier excel would accept change if you entered decimal with number. this is slowing down a budget prep and I'd sure like some help - online and tutorials say "enter decimal" but this is not working. what am I doing wrong? Hi Confirmed, until v ersion XP you could finish off with a decimal separator. This behavior changed with Excel 2003, I haven't seen any documentation on the reasons. "Enter decimal" is like 2000.0 which is just as many keystrokes as 200000 but slower. If you d...

Disappearing cell values in Excel 2007
Hello all, I've recently been experiencing an issue where as I scroll through a spreadsheet rows of cell values will appear to be blank. When I select the cell, the value is still present in the formula bar but only reappears when I double click on the cell or scroll either up or down so that the cell is not shown and then scroll back to the cell. I've experienced this is file ranging in size from 400-700 KB and there doesn't seem to be a pattern as to what cells or rows it will affect at any given time. Is this most likely a memory issue? Thank you, Andrea On Thu, 5 Mar 2009 ...

How do I create a tachograph in Excel?
How do I create a tachograph in Excel? I have been told that it is possible, but it is called soething different!! Hi, You can create a circular (polar or radar) plot using a scattergraph chart with polar coordinates. E.g. if a tachograph has 24 hours, each hour is 15 degrees. Initially it is easiest to have the clock start on the right (i.e. where x=1, y=0 on an x,y graph) , so this would be midnight (0 degrees by convention). The clock in this instance would go anti-clockwise from 0 degrees back to 0 (i.e. 360). E.g. 10 am would be 150 degrees (top-left quadrant), noon would be 180 ...

Importing exporting Distribution lists
Hi I have just gotten outlook 2003. I am trying to export a lengthy distribution list to a coworker. I can copy it into a text file as an attachment but I am trying to avoid entering the all the information over. The recepient has outlook also either 2002 or 2003. How do I import export this Sharon <anonymous@discussions.microsoft.com> wrote: > Hi I have just gotten outlook 2003. I am trying to export a lengthy > distribution list to a coworker. I can copy it into a text file as an > attachment but I am trying to avoid entering the all the information > over. The recepient ...

Install Excel XP and 2003 on same Computer
I have an Excel spreadsheet that was done in 2003 with data list, XP does not see the drop down arrows that was created in 2003. I have 2003 at my office and XP at home, how can I install 2003 on my home computer to finish the sheet with out uninstalling XP. You can install both versions on the same machine. There's information in the following MSKB article: How to run multiple versions of Excel on the same computer http://support.microsoft.com/kb/214388 Without installing Excel 2003 on your home computer, you can see the dropdown arrows if you apply an AutoFilter to the list. ...

Why is my Excel spreadsheet's sorting not being remembered?
I have a workbook with multiple spreadsheets. As I change/add data, I resort these sheets. One of my spreadsheets defaults the sorting criteria I used in the previous sort and remembers that I use headers. The other spreadsheet used to do the same, but now does not 'remember/default' any of my previous sort information or the fact that the spreadsheet contains headers. Is there a setting that I can change so that this sorting information is remembered? Thanks much, Lisa ...

PayPal not in Bank list
Hello, When I first installed MS Money 2006, I noticed that PayPal was in the bank list. I was able to download transactions directly from PayPal, but ever since I redid my data file, it is not there anymore. Am I looking in the wrong places? Thank you. "BJM" <BJM@discussions.microsoft.com> wrote in message news:1420061B-2CF1-4B8E-B8DC-0CABC897775E@microsoft.com... > > Am I looking in the wrong places? I don't think so. I don't find it anywhere in Money Plus (08) and Paypal makes no mention of support for Money. The only thing I find on the Paypal ...

List Server-Side rules
I need to get a list of Server-Side rules, from all mailboxes in the Exchange Organisation In Exchange 5.5 days, I used RulesMgr.Exe, what do I use with Exchange 2000/2003? On Mon, 28 Nov 2005 14:03:43 +0100, "Junk Mail" <a54857@hotmail.com> wrote: >I need to get a list of Server-Side rules, from all mailboxes in the >Exchange Organisation > > > >In Exchange 5.5 days, I used RulesMgr.Exe, what do I use with Exchange >2000/2003? > It's not very intuitive but mdbvu32.exe is your tool. Yes, mdbvu32.exe can show the rules, but not I need to f...