Count unique values - Pivot Table

Hi,

I've some problems to count unique items (Invoice #) in a pivot table. There
is the default solution "Add-a-new-calculated-column" as mentioned on
http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't
works.

My problem is, that these values aren't in a Excel worksheet; it's a
external data source - a SQL-Select via DAO/ADO. Because of that, I don't
have the possibility to add a new column. Another reason is, that the pivot
table should always be dynamic: Group over this field, group over another
field - and always show the number of unique invoices, not the sum of data
rows (please take a look at the Excel sheet: 
http://e-tom.ch/Count_Unique_Invoice_No.xls).

Thx, Thomas

(or is there an easy way to write an User Definied Function and access to
the data rows in each pivot table group?)



0
Thomas
11/3/2005 10:10:05 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
555 Views

Similar Articles

[PageSpeed] 10

How about something like:

=SUMPRODUCT(1/COUNTIF(Data!$A$2:$A$24,Data!$A$2:$A$24)*(Data!$B$2:$B$24=Pivot!B6))

in E6 (copied down), and something like:

=COUNTIF(Data!$C$2:$C$24,Pivot!G7)

in J7 (copied down)

HTH

"Thomas Mueller" wrote:

> Hi,
> 
> I've some problems to count unique items (Invoice #) in a pivot table. There
> is the default solution "Add-a-new-calculated-column" as mentioned on
> http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't
> works.
> 
> My problem is, that these values aren't in a Excel worksheet; it's a
> external data source - a SQL-Select via DAO/ADO. Because of that, I don't
> have the possibility to add a new column. Another reason is, that the pivot
> table should always be dynamic: Group over this field, group over another
> field - and always show the number of unique invoices, not the sum of data
> rows (please take a look at the Excel sheet: 
> http://e-tom.ch/Count_Unique_Invoice_No.xls).
> 
> Thx, Thomas
> 
> (or is there an easy way to write an User Definied Function and access to
> the data rows in each pivot table group?)
> 
> 
> 
> 
0
Gary76 (17)
11/3/2005 2:00:04 PM
Jep, first formula works correctly, thx! But I'd like to have something like 
a pivot field - gives the clients the possibility to add this field to the 
pivot table with drag'n'drop - without a "hack".

I'll write a User Defined Function and put it in a calculated field, the 
only way to solve this problem (in my eyes).
A lot of people have been having this problem for years - but there is no 
Excel built-in function... Looks like nobody of the Excel Dev Team cares 
about...

Thx, Thomas


"Gary76" <Gary76@discussions.microsoft.com> schrieb im Newsbeitrag 
news:15B3AA88-005F-4929-8402-8D32FEE7A393@microsoft.com...
> How about something like:
>
> =SUMPRODUCT(1/COUNTIF(Data!$A$2:$A$24,Data!$A$2:$A$24)*(Data!$B$2:$B$24=Pivot!B6))
>
> in E6 (copied down), and something like:
>
> =COUNTIF(Data!$C$2:$C$24,Pivot!G7)
>
> in J7 (copied down)
>
> HTH
>
> "Thomas Mueller" wrote:
>
>> Hi,
>>
>> I've some problems to count unique items (Invoice #) in a pivot table. 
>> There
>> is the default solution "Add-a-new-calculated-column" as mentioned on
>> http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't
>> works.
>>
>> My problem is, that these values aren't in a Excel worksheet; it's a
>> external data source - a SQL-Select via DAO/ADO. Because of that, I don't
>> have the possibility to add a new column. Another reason is, that the 
>> pivot
>> table should always be dynamic: Group over this field, group over another
>> field - and always show the number of unique invoices, not the sum of 
>> data
>> rows (please take a look at the Excel sheet:
>> http://e-tom.ch/Count_Unique_Invoice_No.xls).
>>
>> Thx, Thomas
>>
>> (or is there an easy way to write an User Definied Function and access to
>> the data rows in each pivot table group?)
>>
>>
>>
>> 


0
Thomas
11/3/2005 4:26:18 PM
You won't be able to use a User Defined Function in a pivot table's 
calculated field.

Thomas Mueller wrote:
> Jep, first formula works correctly, thx! But I'd like to have something like 
> a pivot field - gives the clients the possibility to add this field to the 
> pivot table with drag'n'drop - without a "hack".
> 
> I'll write a User Defined Function and put it in a calculated field, the 
> only way to solve this problem (in my eyes).
> A lot of people have been having this problem for years - but there is no 
> Excel built-in function... Looks like nobody of the Excel Dev Team cares 
> about...
> 
> Thx, Thomas
> 
> 
> "Gary76" <Gary76@discussions.microsoft.com> schrieb im Newsbeitrag 
> news:15B3AA88-005F-4929-8402-8D32FEE7A393@microsoft.com...
> 
>>How about something like:
>>
>>=SUMPRODUCT(1/COUNTIF(Data!$A$2:$A$24,Data!$A$2:$A$24)*(Data!$B$2:$B$24=Pivot!B6))
>>
>>in E6 (copied down), and something like:
>>
>>=COUNTIF(Data!$C$2:$C$24,Pivot!G7)
>>
>>in J7 (copied down)
>>
>>HTH
>>
>>"Thomas Mueller" wrote:
>>
>>
>>>Hi,
>>>
>>>I've some problems to count unique items (Invoice #) in a pivot table. 
>>>There
>>>is the default solution "Add-a-new-calculated-column" as mentioned on
>>>http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't
>>>works.
>>>
>>>My problem is, that these values aren't in a Excel worksheet; it's a
>>>external data source - a SQL-Select via DAO/ADO. Because of that, I don't
>>>have the possibility to add a new column. Another reason is, that the 
>>>pivot
>>>table should always be dynamic: Group over this field, group over another
>>>field - and always show the number of unique invoices, not the sum of 
>>>data
>>>rows (please take a look at the Excel sheet:
>>>http://e-tom.ch/Count_Unique_Invoice_No.xls).
>>>
>>>Thx, Thomas
>>>
>>>(or is there an easy way to write an User Definied Function and access to
>>>the data rows in each pivot table group?)
>>>
>>>
>>>
>>>
>>
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/3/2005 11:55:45 PM
Reply:

Similar Artilces:

Pivot tables in Excel
Yippee, I am so happy, I finally figured out how to trick excel into not showing the blank cell in a pivot table without limiting my data set! In the Pivot table layout move the field from row to page. Then double click on the field and click on the "blank" to hide it. Move the field back to row and hit finish. It seems like you always have to "trick" MS software to do what you want. ...

Form for table
Here's the problem: Access 2003, XP Pro- I have three tables that are related by primary keys. I have a table[Table QC] that has the field [EmployeeID], the data type is number--that field is linked to the autonumber primary key of the Employees Table. So far so good. I am trying to make a form that has a combo box that looks to the Employee Table to get me the drop down of the employees (Last Name, First Name). The SQL statement builds out okay, and I get the correct name config., but because the data type is a number it won't accept the data. I looked at the Northwind example and i...

Change <value> in outlook
We Have Exchange 2003 sp2 and outlook 2003 sp2. I have renamed 2 AD accounts. Changed all display names, accounts name etc with the 2 accounts. When users open outlook and send to renamed account they see account name <oldaccountname> in the autocomplete list. I have cleared nk2 files in profile , but it still shows newaccount <oldaccountname> in drop down box. Is there a way to edit the <oldaccountnam> value of the renamed account? I tried the same thing with a freshly loaded PC. with the same result. Thanks You can delete the nickname files. -- Ed Crowley MVP - E...

Sorting Alphanumeric values in a text field
I'm using Access 2003 for a database for my company. I have a field in a table that has both text and numbers. They are part numbers, for example 21BC124. I kept the field as text because of the text with in the numbers and didn't figure that a numeric field would alow the text. In my part numbers table it sorts correctly (first by number then by letter then by number again), but in my reports and queries there are a few number that sort in the wrong place. Like this... 20D10-3 21BC123 21BC128 22D10 25TD47 21FA101 21FA200 25FA203 38FA601 21FP604 38WS100 I can't quite f...

Pivot Table formatting #6
When I format my pivot tables I have "preserve formatting" checked under "PivotTable Options", and "Autoformat Table" unchecked. Even so, I either lose portions of my formatting, or different formatting is applied when I refresh. Does anyone know what I am doing incorrectly or have any other suggestions? Thanks, Phil Other things to try -- if they don't work, you could record a macro as you refresh and reformat the pivot table. Then, run that when you want to update. --Instead of selecting the cells to format the numbers, right-click the field but...

Count unique values
Hi, I've some problems to count unique items (Invoice #) in a pivot table. There is the default solution "Add-a-new-calculated-column" as mentioned on http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't works. My problem is, that these values aren't in a Excel worksheet; it's a external data source - a SQL-Select via DAO/ADO. Because of that, I don't have the possibility to add a new column. Another reason is, that the pivot table should always be dynamic: Group over this field, group over another field - and always show the number of unique...

Passing a variable as a parameter value instead of a literal string to a child report?
In the Navigation section (Jump to URL), I have the following: ="javascript:void(window.open('http://localhost/reportserver?/Reports +Folder/My +Report&rs:Command=Render&StartDate=01/01/2009&FinishDate=12/31/2010'))" This works as designed - a new window pops up with the "My Report" report and the StartDate and FinishDate of 01/01/2009 and 12/31/2010 are passed respectively. But what is the syntax for changing the "01/01/2009" and "12/31/2010" to variables that point to the StartDate and FinishDate parameters of the PAR...

pivot
How to take a copy of data on the web to a pivot tabel? ex. take a copy of ford and fiat cars in bilbasen.dk... HOW!? Import the data onto a worksheet (Data>Import External Data> New Web Query) Then, create a pivot table from the imported data Louise wrote: > How to take a copy of data on the web to a pivot tabel? ex. take a copy of > ford and fiat cars in bilbasen.dk... HOW!? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Hi. thanks.. but I already tried it. when I click the import button, to my selected "zone&qu...

From temporary table to master table and then join this in junction table
Hello, I have imported two spreadsheets from excel to two temporary tables called "import_tbl_contract" and "import_tbl_products". I do this on daily basis and import many spreadsheets. I end up with these data: [import_tbl_contract] contract_number contract_title start_date end_date [import_tbl_products] product_number product_text price I now append these data to the following two tables: [tbl_contract] contract_id (PK) contract_title start_date end_date e.g. 1; Syringes and needles; 01-01-2010; 31-12-2010 [tbl_products] product_id ...

Top values
Dear friends, need your help again please. I have a table: tbl_Plots (PlotID is the primar key - number byte) and tbl_Data (ID is the primary key - autonumber, PlotID related to tblePlots, height - number byte and diameter - number integer). I need to select the 20 bulkiest trees of each plot, i.e. having the biggest diameter. Also, perhaps in a plot less than 20 trees will be present so I will need all of them. Any suggestions? Thanking you in advance, GeorgeCY hi Geroge, George wrote: > I have a table: tbl_Plots (PlotID is the primar key - number byte) and > tbl_Data (ID is...

Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% ...

formula: counting presence
Hello! I have to count presence of employees from sheets between START and END, which is stored in G9 cell. I think it should be something like: =SUM(IF(START:END!G9="present"; 1; 0)), but this one returns #REF and I don't don't why. Try these from a post of mine today. One way. Put the sumif on each sheet with an indirect reference to d12 of the master. then use =sum(sheet1:sheet21!a2) where a2 in your sumif formula. One way to put=SUMIF(B:B,Sheet1!D12) on each sheet is to select all>type the formula in the cell desired>after the error msg>delete from the ...

Can I set my custom views on Pivot table
I want to get Pivot table data on my invoice, is it possible to set custom views so that I can get Pivot table results on invoice format, I've tried views that offer excel but it doesn't offer custom views please help Thanks ...

Z Report Customer Count vs Hourly Sales
My Z report shows customer count of 380 and hourly sales total of 384. Why the difference? This happens all the time. Thanks Your glass is: Half-Empty : Do folks at register forget to ask "Is there anything else I can help you with?" Half-Full: Your impulse items at POS are working -- better late than never! sammy wrote: > My Z report shows customer count of 380 and hourly sales total of > 384. Why the difference? This happens all the time. > Thanks -- ______________________________________________________ Larry Leveen OlyBikes Locally-Owned B...

List Running Horizontally-Pivot Table Possible?
I just had a customer ask this one and I'm not so sure about it. 1) The customer has setup a list of information that is running horizontally instead of the typical list running vertically. 2) I just taught him how to do pivot tables and he wants to generate a pivot table with his "horizontal" list. 3) I know he can copy and paste special and use the Transpose feature to change the list from horizontally oriented to vertically oriented. 4) the question is: can he leave the table in its original orientation and still generate Pivot tables? I can't seem to make it work ...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

Pivot table and organizing data
This one is really making me scratch my head. Here is the story. I have a list of information which I am pulling in via a query from SQL. Data is good and it correctly comes into Excel (03 or 07). I have five columns with data: Date, Time, AccountID , Status. First two are self-explanatory; third is a 3-letter ID, forth is a status (pass/fail). Ok, now that you have an idea, here is what I need: 1. List the account IDs as a column 2. List the dates as rows 3. Place the alert into the location that corresponds to the appropriate data and account I know this 'sounds' like a strai...

ComboBox passing values from selected
In my form (Items_frm) I have a combo box (Itemcbx) that displays 4 columns (Item, Makebuy, Revision, IsActive) but is bound to the first column. However, I would like to pass the other values from the remaining columns to other fields in my form after the selection. Is this possible? Can someone provide a sample code? You can use the Column() attribute. Me.Itemcbx.Column(1) etc. Note that the index for combo box columns is zero-based so 0 is the first, 1 is the second and so on... Steve "Angel G" wrote: > In my form (Items_frm) I have a combo box (Itemcbx) that displ...

Deleting Unique Values
How do you delete unique values in a column? I need to filter 7500+ rows to only display duplicate values. The VBA code below will delete the entire row when the value in col. "A" will be uniqe. Consider to make a copy of your entire sheet in order to test the code and see if this is what you need. ------------------------------- Sub Delete_Uniques() LR = Cells(Rows.Count, 1).End(xlUp).Row For R = LR To 1 Step -1 If Application.CountIf(Range("A:A"), Cells(R, 1)) = 1 Then Cells(R, 1).EntireRow.Delete End If N...

Making words in a list a value!!
I am trying to create a list that will generate a value in another column once that word or phrase is chosen. Is this possible and how. Exp. In the drop down list I would chose Product, then in the price column the price automatically appears. brco1, Much better than I could ever explain the process. Here's exactly wha you need. http://www.contextures.com/xlFunctions02.html HT -- Case ----------------------------------------------------------------------- Casey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=454 View this thread: http://www.excelforu...

find match then change cell value
In column A I have cells filled with text and in column B I have cells filled with numbers. I need to check if the number in cell C1 equals any of the numbers in column B. If a match is found then I need to change the text in column A to CBO. e.g. Column A Column B Column C aep 5 7 apa 0 gci 59 xto 5000 xle 7 oih 253 ed 8 Since the cell C1 = 7 equals the 7 from column B, I need to change the data in column A from xle to cbo. Is this possible...

Getting right date value
I setup my DTPicker control to be used only as a date control, yet I'm noticing that sometimes it will give back a date AND a time all in the same "value" variable. Since it appears that a variable of type "Date" can give back both a date and time, how can I eliminate the time half of a date value??? I might not be able to exactly control the DTPicker control to give me JUST a date, so I'm just curious what to do if it gives me back both a date & time. thank u Hi, Try this : Dim x as date x = cdate(clng(DTPicker1.value)) &qu...

VLookup #VALUE! error help needed to resolve
The following is the funcation I have: =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0) I have all the columns formatted the same; as in the column that the function is using to lookup is text and so is the column for this figure in order to pull back the appropriate answer. I have keyed the data instead of having links. I have replaced the final '0' with TRUE & FALSE then put it back. I have formatted the columns for text and for numbers. But I am getting the #VALUE! error in SOME of the cells NOT all of the cells. I don't know what else to d...

#VALUE error when linking to other files
I have a summary sheet that pulls info from approx 10 other excel files. In one column of the summary sheet, the links perform just fine. Each cell in this column is the sum of an entire column in another workbook. When I open the summary page and choose "yes" to update with new information, everything works properly. However, in the same worksheet as this, I have another column where each cell is the result of a SUMIF where the data comes from another workbook. The odd part that I cannot understand is why when I open the summary sheet and update the information do all the cells...

Show a blank result in a cell when there is no value in the "Lookup" cell
I apologize if this question has been asked, but I have been unable to find an answer searching the topics. I'm using the following formula in cell C3: =INDEX(LastName,MATCH(A3,EmpID,0)) When I type in an employee ID in A3, his/her last name shows in C3. However, when there is no value in A3, C3 shows error "#N/A". Is there a way to show a blank cell in C3 until a value is entered into A3? Thanks in advance! Mike On Aug 5, 10:45 am, "Michael Slater" <mslater...@comcast.net> wrote: > I'm using the following formula in cell C3: > =INDEX(LastNam...