Possible to use pivot table on non-mumeric fact data without calculating?

Hi,

does anybody know if it is possible to use a pivot table and not do
any calculation on the fact data? I have a flat table in my Orace DB
and just want to use Excel's pivot table to transform the data into
columns (X axis) and rows (Y axis) displaying the non-numeric fact
data. The combination of X- and Y-dimension data is unique.

My fact data is a 1-letter alphanumeric code encoding various options
for the X- and Y-dimension data with the letters O, M, A and G. To
work arround I decode the alphanumeric code into 1-digit numbers
0,1,2,3 and use "maximum" as formula for the pivot table. As the
combination of X/Y is unique the pivot table displays the need values
in the matrix. But I would prefer the alphanumeric values as O, M, A
and G have more meaning to the end user than 0, 1, 2, 3.

TIA,
Stefan
0
9/29/2009 7:02:25 AM
excel 39879 articles. 2 followers. Follow

3 Replies
556 Views

Similar Articles

[PageSpeed] 35

Maybe you should look at John Walkenbach's "reverse pivot table" technique:
http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/



StefanM wrote:
> 
> Hi,
> 
> does anybody know if it is possible to use a pivot table and not do
> any calculation on the fact data? I have a flat table in my Orace DB
> and just want to use Excel's pivot table to transform the data into
> columns (X axis) and rows (Y axis) displaying the non-numeric fact
> data. The combination of X- and Y-dimension data is unique.
> 
> My fact data is a 1-letter alphanumeric code encoding various options
> for the X- and Y-dimension data with the letters O, M, A and G. To
> work arround I decode the alphanumeric code into 1-digit numbers
> 0,1,2,3 and use "maximum" as formula for the pivot table. As the
> combination of X/Y is unique the pivot table displays the need values
> in the matrix. But I would prefer the alphanumeric values as O, M, A
> and G have more meaning to the end user than 0, 1, 2, 3.
> 
> TIA,
> Stefan

-- 

Dave Peterson
0
petersod (12005)
9/29/2009 12:05:59 PM
Dave,

thanks for the link but I just don't understand how this would help
me. If you take a look at the sample data in the example, my data in
the DB looks much like the data on the right side (this is what I
called a "flat" table in my post), except that the "Sales" column is
my "Option" column and contains one of the lettes O,M,A,G. The result
should look like the table on left side displaying the corresponding
"option" for each combination of "Product" (X-axis) and "Month" (Y-
axis). So the data from the "flat" table was transformed/pivoted to a
two dimensional table with products as X-axis and months as Y-axis.

Stefan


On 29 Sep., 14:05, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Maybe you should look at John Walkenbach's "reverse pivot table" technique:http://spreadsheetpage.com/index.php/tip/creating_a_database_table_fr...
>
>
>
> StefanM wrote:
>
> > Hi,
>
> > does anybody know if it is possible to use a pivot table and not do
> > any calculation on the fact data? I have a flat table in my Orace DB
> > and just want to use Excel's pivot table to transform the data into
> > columns (X axis) and rows (Y axis) displaying the non-numeric fact
> > data. The combination of X- and Y-dimension data is unique.
>
> > My fact data is a 1-letter alphanumeric code encoding various options
> > for the X- and Y-dimension data with the letters O, M, A and G. To
> > work arround I decode the alphanumeric code into 1-digit numbers
> > 0,1,2,3 and use "maximum" as formula for the pivot table. As the
> > combination of X/Y is unique the pivot table displays the need values
> > in the matrix. But I would prefer the alphanumeric values as O, M, A
> > and G have more meaning to the end user than 0, 1, 2, 3.
>
> > TIA,
> > Stefan
>
> --
>
> Dave Peterson

0
10/2/2009 10:08:32 PM
You could build your header row of products and header column of rows, then use
a formula like this in each cell:

=index(othersheet!$c$1:$c$100,
   match(1,($a1=othersheet!$a$1:$a$100)
          *(b$1=othersheet!$b$1:$b$100),0))

This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you do it
correctly, excel will wrap curly brackets {} around your formula.  (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

StefanM wrote:
> 
> Dave,
> 
> thanks for the link but I just don't understand how this would help
> me. If you take a look at the sample data in the example, my data in
> the DB looks much like the data on the right side (this is what I
> called a "flat" table in my post), except that the "Sales" column is
> my "Option" column and contains one of the lettes O,M,A,G. The result
> should look like the table on left side displaying the corresponding
> "option" for each combination of "Product" (X-axis) and "Month" (Y-
> axis). So the data from the "flat" table was transformed/pivoted to a
> two dimensional table with products as X-axis and months as Y-axis.
> 
> Stefan
> 
> On 29 Sep., 14:05, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > Maybe you should look at John Walkenbach's "reverse pivot table" technique:http://spreadsheetpage.com/index.php/tip/creating_a_database_table_fr...
> >
> >
> >
> > StefanM wrote:
> >
> > > Hi,
> >
> > > does anybody know if it is possible to use a pivot table and not do
> > > any calculation on the fact data? I have a flat table in my Orace DB
> > > and just want to use Excel's pivot table to transform the data into
> > > columns (X axis) and rows (Y axis) displaying the non-numeric fact
> > > data. The combination of X- and Y-dimension data is unique.
> >
> > > My fact data is a 1-letter alphanumeric code encoding various options
> > > for the X- and Y-dimension data with the letters O, M, A and G. To
> > > work arround I decode the alphanumeric code into 1-digit numbers
> > > 0,1,2,3 and use "maximum" as formula for the pivot table. As the
> > > combination of X/Y is unique the pivot table displays the need values
> > > in the matrix. But I would prefer the alphanumeric values as O, M, A
> > > and G have more meaning to the end user than 0, 1, 2, 3.
> >
> > > TIA,
> > > Stefan
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
10/2/2009 10:52:04 PM
Reply:

Similar Artilces:

Mounting old Data Stores
Hi. Due to problems with Win2003 SBS, I did a reinstall of Win2003 SBS sp1 from original CD-s. Before 'deleting' the old Windows, I made an ASR tape backup of the running server. It seams the ASR backup backed up only drive C: with all relevant data. As other data were on drive D: (Exchange data files, SQL data files,...), I backed up the D: drive too to a separate tape in a separate ntbackup session. Before the backup I used Exmerge to export all users mailboxes. Later I imported those mailboxes in a new Exchange installation. Unfortunately the user forgot to told me, they hav...

Import external data-text files but placed in the next column rather than next empty row
I want to import text files in to one worksheet. The text files are sent daily and all the same format. However the text file names change buy an incremental number. Is it possible to create a macro that imports these text files, ignoring their exact filename but from the same source directory into one worksheet. Can you use wild cards for the name e.g. "********.txt"? At the moment I can manually do this, by selecting the cell below the last record then using the process "Get external Data" and following the wizard which works fine. As soon as I try to create a m...

Calculation 12-15-07
I design one form with TAB control I place sub form in tab control, the Sub form is base on datasheet view, I add one unbound control text field which enabled property set to NO, this control source property base on two other column value like I have two bound(Table) fields I want to show the addition of this field at runtime in this unbound field, every thing is perfect here but I also want SUM of this unbound feild in datasheet view, I try many things but It did not show sum of this unbound field, only Count function work(Total Row option in 2007) for this field even I set format to g...

Plotting 1 curve from 2 separate x-axis data
I have stock portfolio info for January thru June, which plots easily. After portfolio changes, I have another plot for July thru December. Plots easily. These data are on 2 separate worksheets. Now I would like to plot a single curve for the year. The first 6 months is easy. How can I plot a continuous curve for the whole year without having to insert the first 6 months worth on the last 6 months worksheet? Not sure I explained this very well, but --- Dave The easiest way is to have both sets of data on one (a new?) worksheet. Then you can plot as with one or two series. Copy a...

Possible Exchange 2003 Problem
Hello all , I am running Windows 2003 with Exchange server 2003. This morning I added an additional machine the ability to relay through my exchange server through exchange server manage / servers / <my server> / Protocols / SMTP / Default Virtual SMTP server. Since then I cannot receive email from my isp, but I can send email to external sources, and I can send/receive internally, but I just cannot receive email from external sources. I was wondering if adding another machine to the relay allow list would have caused this? Or is it probably through my ISP. I have tried contact...

Why can't I find a table in the lookup wizard?
Using Access 2007, I need to create a lookup field for an existing table, but it is not listed when creating it. Jenni, I can't tell by your posting, are you trying to create a circular reference? Look-up field using the same table you are creating the field in? Well, I'm going to say it makes sense that it would not be in the list but let me know if that's what you meant. On another note... Look-up fields = Bad idea and here's why... http://www.mvps.org/access/lookupfields.htm -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been deni...

Using Excel with Access
Is it possible to have excel update automaticlly if I'm entering data into an offline Access Report. I'd like to be able to enter data into access and have it update my excel report at the same time. Is this Possible? If so How? Thanks -- petevang ------------------------------------------------------------------------ petevang's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25034 View this thread: http://www.excelforum.com/showthread.php?threadid=400501 ...

Email
Hello everyone, I am in need of some help ASAP. I have a high ranking miltary client who has a Latin name, and is really interested in his name being spelled correctly in his email; and displayed properly in his word documents. We are running Exchange 5.5 and NT4 Server. I did not think it was permissible to utilize these special characters... however I told him I would look into it. If I can accomplish this, or even provide supporting documentation (one way or the other), it could be the difference in me obtaining permanent employment. So far none of the permanent employees hav...

Accessing email using MS Outlook
in company all users using Outlook 2003 to access email. Users using OWA 2003 to access email from home or outside company. Anyways to configure such that users can also access thier email from home or outside company using Outlook 2003 client ? Yes. You can either setup VPN server in the office or setup RPC over HTTP. See http://support.microsoft.com/search/default.aspx?catalog=LCID%3D1033&1033comm=1&spid=1773&query=rpc+over+http&pwt=false&title=false&kt=ALL&mdt=0&res=20&ast=1&ast=2&ast=3&ast=4&mode=a&adv=1 2Tian wrote: > in c...

In Excell--- I want to rotate a table 90 degrees
I need to rotate a spreadsheet (11 x 8 1/2)landscape to be on a portrait (8 1/2 x 11). Is it possible to rotate a spreadsheet? you mean actually print it with what shows as the columns being the rows and vice versa? highlight the original table and as you have the last cell highlighted, note in the NAME box on the upper left the Row x Column numbers. Select a range on another sheet that is exactly the flip of those numbers (in other words, stretch until you get the Row x Column to be the Column x Row of the original size). When you have selected this new range, type =TRANSPOSE(hig...

Publisher 2007 crashes when I use spell check
With Pub 2007, I can get spell check to go through about three words and then I get thefamous "an error has ocurred and Publisher has to shut down." Spell check works fine in Word and Outlook and I have reinstalled the spell-checker and have also run diagnostics, finally employing the "repair" function... all to no avail. I've searched everywhere I know and have found nobody else who has ever reported this problem. Any suggestions a layman might understand? TIA How to view error signatures if an Office program experiences a serious error and quits http://suppor...

My Office application cannot start because Office is already in use...?
Upon opening all of my microsoft word and powerpoint documents (but not excell for some reason) I recieve a message that informs me that "an office program is being used by ... Your installation exceeds the number of installations permitted by the license agreement." Clicking out of this message shuts down the document. My confussion stems from the fact that I have not reinstalled or altered my program in any way. The only change in my computer, when I began recieving this message, was that I was connnected to the internet (which I usually am not). Why could this be happening??? &...

Junction Tables
I'm just curious if it makes sense to use junction tables in this instance: student - studentID, studentfirst, studentlast, gender, age (adult, teen, or child), lessonday, status (current or retired) address - addressID, address, city, province, postalcode phone- phoneid, phonenum, extension, phlocation email - emailid email, emailLocation gaurdian – gaurdianid, gaurdianfirst, gaurdianlast, relationship studentaddress - studentID, addressID studentguardian - studentID, guardianID GuardianAddress - guardianID, addressid Studentphone - studentID, PhoneID Guardian...

Frx: linked data worksheet summing error
We use linked data worksheets for some of our reporting. The column format displays both current period and YTD. The YTD column is not calculating the correct amounts from the worksheet. For example, if we were in period 12, it would sum the YTD column as follows. period 1: add 12x period 2: add 11x period 3: add 10x period 4: add 9x ....and so forth period 12: add 1x Obviously, this gives us a huge error in the ytd column. Anyone have an idea why this is occurring? We are using the /cpo format for the linked worksheet. Thanks ...

Help! Selecting data according to date range
I'm attempting to setup a worksheet for reminding employees to rene their licenses. I have input the data where the data range is from ro 3 - 84 (this could increase or decrease with hiring/firing, etc.). Th columns range from A - K with column H being *date*. I would like to start a new sheet (sheet2) in this workbook with th range of months in a year. In each monthly section it would search th data range in sheet 1 and return the records with the correspondin dates for that month. For example: If a employee's license expires i January, the entire record for that employee would...

using RANK
I know i can rank a column of numbers from largerst to smallest. Can I use rank for smallest to largest? Any help appreciated. Skip Help has a pretty good explanation, look for the order, 0 or omitted is descending and 1 is ascending -- Regards, Peo Sjoblom "Skip" <ssummer@earthlink.net> wrote in message news:27e85c6e-c145-414e-9051-b3adb627f908@a1g2000hsb.googlegroups.com... >I know i can rank a column of numbers from largerst to smallest. > Can I use rank for smallest to largest? Any help appreciated. > Skip ...

failed to restore full backup using ntbackup
hi everybody I tryed on my lab inviroment backup my xp (C:) partitian using ntbackup, then I changed ntldr file, the restore didn't work here is the report restore Status Operation: Restore Backup of "C:", Restored to"C: " Backup set #1 on media #1 Backup description: "Set created 12/7/2009 at 1:13 PM" Restore started on 12/7/2009 at 1:34 PM. Warning: File AUTOEXEC.BAT was skipped Warning: File boot.ini was skipped Warning: File CONFIG.SYS was skipped Warning: File IO.SYS was skipped Warning: File MSDOS.SYS was skipped Warning: File nag...

Can a form's query use dbSeeChanges to get new autoincremented key?
When a form was bound to a jet table I could get the new autoincrement pkey value in the before insert event. Is there a way to do with with a slq server 2005 odbc linked tables? Something like the dbSeeChanges param that one can use with recordset inserts? No, you can't because of the case of an ODBC linked table to a SQL-Server, the new autoincremented value will be defined only after the creation of the new record. Access/JET seeds the new value before creating the record - so you can access it even if the record doesn't exist yet in the table - but SQL-Server doesn't w...

Pivot Table chart field buttons
I have a pivot table chart with several field buttons across the top. How can I move them to the side? Thanks in advance for your help! You should be able to drag the field buttons from the page area to the column area. What happens when you try to move them? Jeff M wrote: > I have a pivot table chart with several field buttons across the top. How can > I move them to the side? > > Thanks in advance for your help! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

about inserting data
hi, i am new to this newsgroup. I have little knwldge in Excel. How to create tables in Excel file. and also i want to execute the quries. How can i do that. thanks, regards, koti ...

using dlookup in a query
I'm creating a Bill of Materials using a one-to-many relationship table between InternalPN and ExternalPN. So the query sometimes returns more than one record when an InternalPN is specified, because there are multiple External PN's. I would like to alert the user to this in my query by substituting the word "Multiple" for the ExternalPN. I can find multiples easily enough with the Query Wizard & so have created a separate query called QryPNMultiple. I'm trying to refer to that query in a dlookup statement in my BOM query. I've tried this: Expr1:...

Manipulating totals and columns in Pivot Tables
Hi, I'm constructing a pivot table that has investors as columns, investments as rows, and the general data is values. Trouble is there is a column which I want to only display half of each value. I know that I can make a formula that divides a column "column A/2", and then hide the unwanted columns. But I would also like the hidden columns to not be included in the grand totals. How do I make this happen? Thanks! Hi, So let me understand what you want, something like this in the Pivot Table: A B C Name Jp ...

Using "do Visual Basic" from AppleScript to access Office 2004
I am trying to automate word,excel and ppt via Java-Cocoa-AppleScript-VB. The Java application exists already. The objective here is to have the *same* interface access all three apps .... this seems impossible given the inconsistent level of method exposure MS have provided. Using all AppleScript fails because PPT has a very limited set of objects that can be accessed from AS. For this reason I decided to investigate using VB to do the work (yes, even though the mechanism to return values is broken) and use the 'do Visual Basic' command to send it to the app. It does not look lik...

Calculating shift patterns (Sorry can't be more specific)
I really hope that someone can help me. I'm at the end of my wits tryin to work this one out. I have a time sheet M T W T F S S (ETC for 3 months) PERSON A E E L M E O O PERSON B O O L M M E E E Shifts M Shifts L shifts O (Off) I want a formula that could help calculate how many people are on certain shift on each day. So, E shifts for M = 3, for instance, so that each time I add a shif it automatically calculates the number at the bottom. Also and I know this is cheeky...but does anyone know how I can tall how many L shifts someone does in month etc. To keep an automa...

Need help with data validation format
I'm trying to make this format mandatory when someone enters his/her data in a cell. The format would be this : "### ###". Therefor, if the worksheet user doesn't enter a chain like this one : "113 244", the data just won't enter. And yes, the space beetween the 3 first and last numbers has to be there. I can't seem to find anything in the Data / Validation menu that enables me to do this :confused:. Any help would be much appreciated. Thanks in advance. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~...