Manipulating Pivot Table Data Fields

Hi all,

Have a question regarding the Data Field in pivot tables.  I have a number 
of rows that have repeating values.  What I want in the Data Field (Sum) is a 
count of DISTINCT values in the column.

ie:
FR065817  \
FR065817  |---->  counted as one
FR065817  /
FR066154  \___> counted as one
FR066154  /
FR066158  -----> counted as one
FR066159  \___> counted as one
FR066159  /

However I need all the data in the table as I'm using other columns for 
different fields in the pivot table.  Is there a way to do this?  If I have 
to split the table somehow, I'm willing to do it, but I'd need some help with 
how do go about doing that.

Any help is appreciated.

Thanks,
Jay
0
Jay1 (212)
10/31/2005 6:09:33 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
636 Views

Similar Articles

[PageSpeed] 22

Why not create 2 PivotTables?  One that contains the distinct counts 
and the other with the individual items?  When you create the 2nd table 
XL will ask you if it can base it on the first table.  If you answer 
yes, it will save some amount of resources.

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <D9FA5662-A1C5-421F-8298-97A3DAF7D106@microsoft.com>, 
Jay@discussions.microsoft.com says...
> Hi all,
> 
> Have a question regarding the Data Field in pivot tables.  I have a number 
> of rows that have repeating values.  What I want in the Data Field (Sum) is a 
> count of DISTINCT values in the column.
> 
> ie:
> FR065817  \
> FR065817  |---->  counted as one
> FR065817  /
> FR066154  \___> counted as one
> FR066154  /
> FR066158  -----> counted as one
> FR066159  \___> counted as one
> FR066159  /
> 
> However I need all the data in the table as I'm using other columns for 
> different fields in the pivot table.  Is there a way to do this?  If I have 
> to split the table somehow, I'm willing to do it, but I'd need some help with 
> how do go about doing that.
> 
> Any help is appreciated.
> 
> Thanks,
> Jay
> 
0
11/1/2005 3:48:00 AM
Reply:

Similar Artilces:

Data range properties
Hi I have a problem with external data. In the Data Range Properties, under Data Layout. There are 3 options after 'If the number of rows in the data range changes upon refresh:' 1 - Insert cells for new data, delete unused cells 2 - Insert entire rows for new data, clear unused cells 3 - Overwrite existing cells with new data, clear unused cells. I want option 3 to be the default oprion but it always defaults back to option 2. Is there any way I can change this.?? Cheers Baz Apologies somehow managed to post twice "baz" wrote: > Hi > > I have...

Table Import or Mail Merge Macro?
I need to set up (item card, assign site, assign creditor) about 250 new items. I've been testing the table import process in the test company, but keep getting handling exception errors when testing the item after the "successful" import because of an obscure (for me) blank field. All the items have the same Class ID, creditor, site, etc. They only differ in item code, desc, curr cost, list price, gen desc, and creditor item code. So would I have better luck recording a macro setting up the first one and mail merging the necessary fields? If you believe table import is ...

Pivot Table
Hi all, I have one issue when i get the data through the pivot table option. after creating a pivot table, i enabled the sub-total and the grand total in the pivot table properties. after enabling that the sub-total and the grand total columns appear as #VALUE!. I have office 2003 in OS 2000 proffesion. Please help me Regards Yazh You have one suggestion in .excel yazh wrote: > > Hi all, > I have one issue when i get the data through the pivot table > option. after creating a pivot table, i enabled the sub-total and the > grand total in the pivot table properties....

Is any group function which concatenates data?
Hello. In a query as follows I would like to concatenate data instead of aggregating in a field which is not a part of group clause. SELECT invoice_no, device_type, concatenate_text(device_serial),customer FROM t1,t2,t3,t4 WHERE .... group by invoice_no, device_type, customer Full data set without grouping looks like: SRW/1/2010, AAASSSQQWE, sn000001, ABIX SRW/2/2010, AAASSSQQWE, MSD001XXX, ADA SRW/2/2010, AAASSSQQWE, sn000002, ADA SRW/3/2010, AAASSSQQWE, sn000001, ADA SRW/4/2010, AAASSSQQWE, sn000001, ADT I would like to have something like this after group by and "co...

on NoData, show value in table
My report recordsourse is based on qrystudent where i have 2 tables. 'duration' table has 2 fields [from] and [to]. on NoData event of the report, I want to give message like 'You have not logged in during ' [from] and [to] If NoData, how can I give message include fields value in 'duration' table? 'duration' table has only one record. Thanks Song Su wrote: >My report recordsourse is based on qrystudent where i have 2 tables. >'duration' table has 2 fields [from] and [to]. > >on NoData event of the report, I want to give message l...

Delete a person from the drop down in the to field
Hello, How do I delete a name from my automatic drop down menu in the to field. If I send a mail to fx. microsoft@microsoft.com, the next time I write mic... in the to field, the drop down menu keeps the address in the list. How do I delete this name again. Best regards Kim use the arrow buttons to select it and hit Del -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Outlook Tips: http://www.outlook-tips.net/ http://www.poremsky.com - http://www.cdolive.com Expert Zone http://www.microsoft.com/windowsxp/expertzone Search for answers: http://groups.go...

Combining rows of data that have one cell in common
Hi everyone, let me explain... I'm working with a large excel file (13,000+ rows) of cancer cases recorded in the state of Florida. Each time a patient presents with a cancer, that "case" is recorded as a row on the spreadsheet, along with a UNIQUE patient ID assigned to that particular individual. If that same person develops another cancer sometime later, that information appears in a new case/row, but with the SAME patient ID. I need to export this file to another program (SPSS v13.0) to do some advanced statistical analysis. However, the program requires that all of t...

Changing font in rich text table
I want to standardise the font of all data in a table's rich text field. If I use: Dim DB As Database Dim rst As Recordset Dim strSQL As String Set DB = CurrentDb() strSQL = "SELECT tblReportTermly.Aims FROM tblReportTermly;" Set rst = DB.OpenRecordset(strSQL) Do Until rst.EOF With rst .MoveFirst .Edit !Aims.FontName = "Calibri" !Aims.FontSize = "11" .Update .MoveNext End With Loop i get "Object doesn't support this property or method". Am I approaching this incorrectly? ...

Dimension Update and Pivot table report
I have an Excel (03) Report file which uses Analysis Services (SP4) Cube. Works fine still using AS 2000 not 2005. When I make any changes to Dimension and process Dim. and Cube Excel file doesn't takes my Dim changes... I have to take (drag) the changed Dim. back to field list and bring it back to report.... then only it works.....problem is we have 100s of Excel Cube Reports. Error I get: The item Could not be found in OLAP Cube. Thank you - Ashok ...

Alternatives to MS Query for Pivot Tables & Cubes
I am using Excel 2003. I would like to know if there is an add-in or other (preferably free) tool out there that I could use to create OLAP cubes for Excel pivot tables that would have better functionality than MS Query. I find MS Query very difficult to use and almost impossible for creating calculated fields that can then be built into a cube. (Note: I thought I could just make calculated fields in the pivot table itself, but now I see that Excel will not let you do that when using an OLAP cube as a source.) Any suggestions would be greatly appreciated. ...

Money Premium 2006: Moving my data to my laptop
For reasons too numerous to go into here, I believe it likely that I'm going to have to move my Microsoft Money 2006 Premium database from my desktop, where it has been the past 6 years or so, to my laptop. I don't have Money installed on my laptop, but will have to. What I want to know is, how do I get all of that data, safely to my laptop? Rod In microsoft.public.money, Rod wrote: >For reasons too numerous to go into here, I believe it likely that I'm going >to have to move my Microsoft Money 2006 Premium database from my desktop, >where it has been the past ...

Split combined date time data #3
From file dump have combined date time cells eg "14/04/03 14:20" (value 37725.59722). Want to perform time analysis so need to split to 2 separate cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a fnc to do this? (Currently convert cell to value, then fncs trunc & cell less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively) One way: Data/Text to Columns. Select Delimited, and click Next. Check the Space checkbox and click Finish. In article <D84AFF1F-CA34-456B-AA06-926914397A3C@microsoft.com>,...

Font manipulation
Hello. I'm curious I can change apperance in chat window. Like the background of the tab of my messages and different background of the tab of the different user. I don't mean the background of the text. And is there any way of increace the spacing of lines between them? The lines are really near... For more understanding: I wish to control it somewhere with like a css command "Padding" or "margin". Like ..text_me { font color: gold; font size: 1.5em; padding: 4px; } ..text_others { font color: black; padding: 4px } I think y...

Getting a value from large table.
Hi, I have a data table where column A = Make (named MAKE), B = Model (named MODEL), Row 1 = Model Year (named MODELYR). On a different sheet, I have a list of miscellaneous combinations. On this sheet, column A contains the make, B the Model and C the Model Year. How can I get the contents from the table to be populated in column D for my list? I have tried SUMPRODUCT, SUMIFS and INDEX (with MATCH) functions, but continue to get the #Value error. I have also array-entered each of the functions. I would like the value in D1 where MAKE=A1, MODEL=B1 and MODELYR=C1. -- TIA, Br...

VBA Import from Access using ODBC
I am trying to import from Access using ODBC - then I want to basically concatenate two of the columns. If I run my macro it actually runs the concatenate part first, then brings in the odbc information. If I put a pause or step through it - it works no problem. Code is provided.. What am I doing wrong? Thanks in advance... dc With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ "ODBC;DSN=MS Access Database;DBQ=" & fileandpath & ";DefaultDir=" & spath & "; DriverId=281;FIL=MS Access;MaxBufferSize =2048; PageTimeout =5;")), Dest...

Using ad hoc table to select which records to print
Hi all. I have a situation I could use some help with. I have a database used for a high-volume electronics repair business, and I'm trying to streamline pre-shipment paperwork functions for customer service. Depending on the customer and order type, up to 6 different reports might be printed for each order. I've set up a table populated on an ad hoc basis by customer service through a continuous form with a text box for the order identification number and a check box for each report to select which to print. Each of these reports might also be printed from elsewhere in the database (...

Re: Debtor Period Summary Table RM00104
hi guys, i was doing some customer monthly sales report, and i come across this particular table, it has a field called HISTTYPE, History Type. and it has a value or either 0 or 1, for the financial year of 2006, which is also the 1st year GP started in the company, there are some customers having both 0 and 1 for the same period, e.g. customer1, period5, year2006, histtype1, $309 customer1, period5, year2006, histtype0, $253 could anyone shed some light on what the history type is? coz in GP, when reviewing past month sales, seems it brings up figers in the row where histtype is 1 ...

Moving data across columns
Hello Everyone, I hope someone can help me. I have a file containig contact informatio with all the data in column 1 i.e. name in cell A:1, complete addres in cell A:2, phone number in cell A:3. I need the data in a database format i.e. name in A:1, address in B:1 city in C:1, zip in D:1 and phone in E:1. I have about 1,500 files s the Macro would have to move down and repeat the process. Also, since A:2 contains the full address, the macro should also brea apart this information so that the city, state and zip is in its ow field. I may need two separate macros to accomplish this. Does a...

Field Service #3
Hi all Has anybody had this problem in field service. 1) I create a couple of new service calls 2) I add parts to them 3) I then go to inventory requirements and mark p = po and c = consolidate then create PO It is important to note that I am purchasing from the same supplier. In the next screen I make sure that the first record is not marked for consolidation and the rest are ( which means the firts record should create a new PO and the rest should add lines to the PO. However this is not the case Field service will at random create all as new po's or next time round will create 2 new...

changing proportion of chart and data table
I have a chart with 19 lines of data and I have been requested to print the data table with the chart. When I add the data table so that I can read the text (e.g., 11 or 12 pt font), I cannot see all the data table line in the page viewer or print it all out. I can only print out all the data table lines along with the chart if I make the font so small that it renders the data table useless (or similarly, if I shrink the size of the size the chart so that what I am looking at is so small you can't read the labels anymore). In either case, when I do this I also I end up with half ...

performance issues linking sql 2000 table in access
I hope I'm just overlooking the obvious and there is an easy solution for my issue. I scanned the messages here and didn't see anything that looked similar. My issues is I have a SQL database with a table containing a little over 2.5 million rows linked to access. We typically open the linked table and filter for a subset of records to modify in table view. The database was recently moved to a new faster server with more memory and a bigger hard drive. After the initial move performance when opening and using the table seemed more robust than on the older slower machine. The ...

Pivot Table Filter Error
When I filter data in my pivot table in Excel 2007, it is splitting up the same data. For instance, if there are numbers describing a job poisition the filter is splitting the people who are in group 12. instead of showing me 25 people in group 12 it is showing two groups of 12's and splitting them up as 23 and 2, instead of one group of 25. I formatted the column as all numbers and that didn't work. Any other suggestions? ...

How to return the address of the range of plotted data on an XY scatter chart?
I can't figure out the VBA code that will return the address of the range of data plotted on an embedded chart on say sheet1. Everything I try just results in the "object does not support this property or method" error message. I've been recording macros for clues as well as studying the Object Browser, all to no avail. Any clues? Ken Johnson Hi, To get the ranges used by a chart data series use the formula property. Use this in the immediate window. ?activechart.SeriesCollection(1).formula =SERIES(,Sheet1!$A$1:$A$5,Sheet1!$B$1:$B$5,1) See John Walkenbach's page on...

pivot table #5
I have a pivot table and would like to view and change my SQL and database link. Are there any way to open it to debug and verify it? I know that I can edit SQL from MS SQL if I use MS SQL to retreive data. I could not figure to open the cub file to deit and debug. Your inforamtion is great appreciated, The free Pivot Play add-in, located at Debra Dalgleish's website, may help: http://www.contextures.com/xlPivotPlay01.html It allows you to view/edit the connection and SQL code for Pivot Tables that use external data sources. It has many other features, too. Does that help? -----...

Importing data from MS Access to Outlook
I have tried unsuccessfully to send text message from MS Access to Outlook Express (MS Access has a SendObject command to send data. It works but will only send 255 or 256 characters, the rest is truncated). I'm wondering if the opposite will work. Can I set up a form text message in Outlook and import certain data from my MS Access database (someone's name, email, etc) or am I way off base? Thanks in advance for any help. -- Ceedy "Ceedy" <Ceedy@discussions.microsoft.com> wrote in message news:38A8EED0-3795-4977-B0FE-A5F5F37908BB@microsoft.com... >I have...