Conversion of Cross-Tab Formatted data to qualify for Pivot Table

It's amazing how one can take pivot table data -- double-click
in the data section and it "converts" the PT format to a data-table format
(fit for a database environment, say access)...
Well any way, I receive files that are in a sort of a crosstab format,
which I'd love to be able to get to respond to (the creation) of a Pivot
table.
Is there a way to make this "conversion"?
Thanks in advance for any direction offered..


0
jmay (696)
4/17/2005 5:11:49 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
657 Views

Similar Articles

[PageSpeed] 57

Do you mean something like this:
http://j-walk.com/ss/excel/usertips/tip068.htm
from John Walkenbach's site.

Jim May wrote:
> 
> It's amazing how one can take pivot table data -- double-click
> in the data section and it "converts" the PT format to a data-table format
> (fit for a database environment, say access)...
> Well any way, I receive files that are in a sort of a crosstab format,
> which I'd love to be able to get to respond to (the creation) of a Pivot
> table.
> Is there a way to make this "conversion"?
> Thanks in advance for any direction offered..

-- 

Dave Peterson
0
ec357201 (5290)
4/17/2005 5:36:24 PM
Dave, This is exactly what I needed.  I hesitated before I asked, but
certainly
am now glad I forged forward with the Q...
Thanks again for the reference to JW's Site and solution.
Have a good one,
Jim


"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:42629E98.68873C6A@netscapeXSPAM.com...
> Do you mean something like this:
> http://j-walk.com/ss/excel/usertips/tip068.htm
> from John Walkenbach's site.
>
> Jim May wrote:
> >
> > It's amazing how one can take pivot table data -- double-click
> > in the data section and it "converts" the PT format to a data-table
format
> > (fit for a database environment, say access)...
> > Well any way, I receive files that are in a sort of a crosstab format,
> > which I'd love to be able to get to respond to (the creation) of a Pivot
> > table.
> > Is there a way to make this "conversion"?
> > Thanks in advance for any direction offered..
>
> -- 
>
> Dave Peterson


0
jmay (696)
4/17/2005 6:13:26 PM
Reply:

Similar Artilces:

Validating Data on a Form with a subForm
I have a form with subforms with data validation in the main Form's Before_Update Event. The problem that I am encountering is when the user has not updated all required data but enters the subform the Form Before_Update Event fires and the validation will fail. Is there a way to know what the next object has been selected might be or how I would not perform the Form's validation when the subform has been selected? Any help will be appreciated!!! No. Access saves the main form's record at the point when you move from the main form into the subform. Any record that fails v...

Linking Formats Across Cells
anyone know if I can link formats across cells? For example, if I fill a cell with a color, can I link it to another cell that will do the same ? thanks. Try the FormatPainter, the little PaintBrush icon up in the tool bar...........click on your colored cell, then click on the icon, then click and drag on the cells you want to be the same........... Vaya con Dios, Chuck, CABGx3 "RB" <RB@discussions.microsoft.com> wrote in message news:FE8ABE47-2671-447C-8CC6-2D784EEECE00@microsoft.com... > anyone know if I can link formats across cells? For example, if I fill a cell ...

ClistCtrl and how to cross out a line
Hello I have CListCtrl object and style LVS_REPORT, but I can't make a horizontal line to cross out one line with subitems. So please help: How to do that line? m. >I have CListCtrl object and style LVS_REPORT, but I can't make a >horizontal line to cross out one line with subitems. >So please help: How to do that line? There's no build-in functionality to display strikeout text - but you can probably do it yourself by the custom draw facility - see NM_CUSTOMDRAW documentation in MSDN. Dave In addition to David's advice these articles may help: http://www.codeg...

Biztalk or Scribe for our data integration?
We recently started using CRM 3.0 and the time has come to decide on a data migration plan. So far I've looked at Scribe and it seems promising but my higher-ups are suggesting we take a look at BizTalk. Eventually we will need to pull (two way integration will be required with at least two legacy systems) data from several legacy systems including flat files, SQL Server and a Progress database to name a few. The possibility also exists that we'll want to accept Word, Excel and HTML documents from customers and have information they contain added to our database. Is BizTalk cap...

Data value display attributes linked to table attributes
Is there a way to cause data value points to take on attributes such as bold, italic or color set at the table from which they are derived? For example, suppose I was charting hits at several web sites as function of time, and the some characteristic of the site was being altered back and forth (say font "A" and font "B") as part of an experiment. I can easily produce a line graph x=hits y=time for the various sites, but these is no way to visualize which condition (A or B) was in effect at each data value point. I'd like to be able to display the portion of each li...

Alt Tab not Working in Excel
Hey, hopefully someone can give me a hand here. Alt+Tab just stopped working to switch between sheets in excel. I DO have Tools>>Options>>View>>Windows in Taskbar checked. I do see multiple sheets in the windows taskbar so this isn't the problem. Additionally, I'm aware of Ctrl+Tab and Ctrl+F6 (and their reverse "shift" capabilities). I really need to be able to have the little window pop up (with Alt+Tab) to be able to select the correct file without going through them all like you have to with Ctrl+Tab. Additionally, switching between other windows works ...

Pivot Tables
Hi, I am having trouble creating a pivot table to show variance between planned vs actual units. Currently my data is broken down into the following fields, Team, Type, Month, Actual Units, Planned Units. I would like the pivot table to show if the current teams are meeting their monthly goals by comparing the planned vs actual units and showing the percentage. Team (all) (page) Month Jan Feb March . . . . . (column) Actual 100 110 90 Planned 90 110 100 Variance 10 0 -10 variance % 11% ...

Two tables, one form
I have a form that I created in AC2003. This form will merge info from two tables. So I assigned Table1 as the source for the form, inserted all of the columns from Table 1. Next, I changed to source for the form to Table2 and again inserted all of the columns from Table2 onto the form. I then changed the name of all Table2 columns to precede it with an "n". i.e. FirstName became nFirstName. All source for those items for Table2 were changed to have no source. Again, the source for the form is Table1. I lookup a row in Table1 in Table2 and if a match is found, ...

How to read values from a table?
Hi, I am trying to redesign an old database. I have three forms start, entry, edit forms. Start form is like a switch board to enter into the forms. An audit is entered into the database through "Entry" form which has around 50 controls. All these controls gets stored in the main table. "Edit" forms allows you to edit the audit. The issue here is the "Edit" form doesn't read the values from the main table when the form is loaded. I have a logic to read the values from the main table in "start" form. Everything looks fine with the SQL statemen...

Need to extrcat data from one Excel file to an other
Hi there I'm looking form a solution to press on button in a Excel sheet which ill exctract some datas from an other Excel sheet which can be close or opened. It should beworing on Windows XP, 2003, Vista and 7 as well with 32 bits and 64 bits. Thanks in avance for your advise and best regards Tra Browse through the example codes found at Ron de Bruin's site. http://www.rondebruin.nl/tips.htm Look at the 'Copy/paste/merge" section. Should be some good material to start with. Gord Dibben MS Excel MVP On Sat, 28 May 2011 19:53:03 +0200, Tra Sumaka <trasumak...

enter data to different tables through one form
i have 5 tables. all of them has pers_no as primary key. they have one to one relation ship through the primary key amongest them. i want to create one form having a text box pers_no where i will enter the data and i want the data to get entered in each table. so how i am not able to do that. i request all you guys to help me. thank you. Sorry for the bad news, but that's not the right way to use Access. You cannot write to 5 tables from one form like that. If you are subclassing, you could create a form for entering the main form info, and then 5 subforms (probably on tab pages) f...

Scatter plot
I have an XY scatter graph with some points in it. My data in the spreadsheet looks like this: [Title] [X-value] [Y-value] [size] Data 1 | 10 | 10 | 1 Data 2 | 33 | 44 | 5 What I want to do is to change the size of the data point according to the value I specify. Right now, I have to individually modify each point and add a number corresponding to the size. Is there anyway to take the size-value from the spreadsheet and change the size of the point in the graph? In the long run, I am also looking to modify the color. If the solution requires some VBA coding, I am up for that. Any sugges...

Condtional format with formula
I have this formula =IF(ISNA(VLOOKUP(A17,'Data-FSList'!GEAC2000IntlFSList,2,FALSE)) =TRUE, "DONOR NOT VALID",VLOOKUP(A17,'Data-FSList'!GEAC2000IntlFSList,2,FALSE)) on cell B17. When the user selects a value in A17 from the drop down, it goes to sheet 'Data-FSList' to retrieve a value. If the value is not there it comes back in field B17 'DONOR NOT VALID' However when A17 contains no value from the drop down and is blank I do not want 'DONOR NOT VALID' to show in cell B17. Is there a way to add to the formula above if A17 is blank then...

Importing data #2
hi I would like to import data from the web and although this is quite simple in Excel 2003 the new data (that comes in upon refresh) replaces the old. I was wondering if there is a way to keep previous history of the data, make Excel fill in adjacent cells with the new data for example. Thanks for taking the time to read this George hi, if you are using Microsoft query, the answer would be no. The Micorsoft query becomes a named range in excel that is linked to the query. by desing it expands and contracts at refresh to accommidate new data. I think to accomplish what you want yo...

Offsetting Chart data
I require to view data in chart format, the problem is as the data lines are so close I need to offset the data to view all on one chart for comparison. Can anyone help me with this?? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ What does that mean? "Offset the data?" If you want to see only a subset of the data, see the Dynamic Charts page of my site. You should be able to adapt one of the examples to your needs. -- Regards, Tushar Mehta, MS MVP -...

Append to MS SQL 2005 table
I'm getting a key violation error whenever I try and append records from a table in Access to a table on a MS SQL 2005 database. The SQL db's table has an IID field which is the primary key for the table (and the only indexed field in the table) and I am NOT trying to append records to the IID field with my INSERT INTO tImport(...) statement. It is a clustered index if that makes any difference. Since I can't seem to add the table and write to it without an index field, does anyone have any ideas why I am getting the error message Is the IID field an Identity/Autonumber t...

Pivot Table 2007 display field only in Grand Total
Dear Excel experts, I have a Pivot Table (Excel 2007). As an example, the table consists of two Colum Labels (Expense 1 and Expense 2). Values under the Column Labels are Actual Expense, Budget and Full Year budget. All Values (Actual Expense, Budget and Full Year budget) are totaled in the Grand Total. However, Management want to see Full Year Budget only in Grand Total (not under Expenses 1 and Expense 2). I was manually hiding those values. Is there a way to automate this? Thanks ...

OLE Object Data Type Question
Hi, a friend of mine supports a very simple Access 2000 database for keeping tracks of his .JPG file. The database has a table with several field, and one of them is of OLE Object data typy. He opens that table, rightclicks OLE Object field, selects Insert Oject and creates a link to a .JPG file. After finishing insertion the OLE Object field contains kind of "MSPicture3" or "MSImage3" (I don't remember exactly the text). Before a month ago he moved his database to another computer and now after insertion into the OLE Object field a link, it contains "...

Data Labels on Clustered Column w/ 3D Visual effect #2
Clustered Columns can have Alignment as "Inside End" This doesn't seem possible with 3D Visual effect Is there a way to resolve this? Yes, use 2D charts. They provide a better representation of the values being plotted without the implied false third dimension. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ emwashburn wrote: > Clustered Columns can have Alignment as "Inside End" > > This doesn't seem possible with 3D Visual effect > > Is there a way to ...

Offset in Conditional Formatting?
I have the following formula to check for a condition: =IF(CB3="Reg",MOD(CA3-BZ3,1)>TIME(0,$AG$39,0),IF(CB4="Reg",MOD(CA4-BZ4,1)>TIME(0,$AG$39,0),IF(CB5="Reg",MOD(CA5-BZ5,1)>TIME(0,$AG$39,0)))) Essentialy what it does is check if a value (Reg) is present in one of 3 sequential rows and if so calculates if the time difference between two related values in the same row are greater than a predetermined number. If TRUE then the cell is formatted. The formula works fine for a single cell but when I use the Applies To field in CF and highlight th...

OWA Contacts FAX format
I have Ex2003 installed with GoldFax Exchange Gateway installed. Using OWA 2003 when I type a FAX address into the TO: field I can send a FAX just fine. [FAX:george@7145556789] The problem that I am running into is that if I create a contact in OWA and enter [FAX:george@7145556789] as his email address when I save the contact everything to the left of the ":" disappears. So what I would see if I view the contact from contacts or use it to fill in the To: field is george@7145556789], which OWA obviously won't recognize as a legitimate email address. But if I open the conta...

XML Insert data C# ASP.net 2.0 Need to know how to insert by speci
Hello, I have the following XML Schema: <?xml version="1.0" encoding="utf-8"?> <Schedule> <Day Name="Monday"> </Day> <Day Name="Tuesday"> </Day> <Day Name="Wednesday"> </Day> <Day Name="Thursday"> </Day> <Day Name="Friday"> </Day> <Day Name="Saturday"> </Day> <Day Name="Sunday"> </Day> </Schedule> I want users to be able to input the following tiomeblocks for a scheduling s...

Bullet point formatting
I would like make it single spacings through out my page. When bullet point list is selected, an extra row is created after the text. To explain, I typed 5 words which start at the left side. Then I pressed return. At that point, it is single spacing. Then I select 'Bullets' and automatically an extra row is inserted. I've checked Paragraph Properties and all are set to '0' and single spacing. Please can you advise. Thank you in advance. ...

Getting data from Access
I am trying to use the Get External Data function to pull data from an Access query however I keep getting an error that says: "Microsoft Query: msqry32.exe - Application error The instruction at 0x000000072 referenced memory at 0x000000072. Memory could not be read Click OK to Terminate Click on CANCEL to debug" I am using Excel and Access 2000. Does anyone have any idea why I keep getting this error? Check out this page http://www.rondebruin.nl/accessexcel.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "NChris" <NChris@discussions.micr...

Cannot get datagrid to display data
Hi, I have an XML file that I was hoping to use as a database Here is the code that I have copied that should display the data namespace File_Generation_System { public partial class vrholds : Form { public vrholds() { InitializeComponent(); XmlDataDocument xmlDatadoc = new XmlDataDocument(); xmlDatadoc.DataSet.ReadXml("vrdatabase.xml"); DataSet ds = new DataSet("vrdata DataSet"); ds = xmlDatadoc.DataSet; dataGridView1.DataSource = ds.DefaultViewManager; } But what I g...