Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Count Unique Values In A Pivot Table


I have a spreadsheet with several records for each person's name.
I want to have pivot tables based on various columns, with the data field being a count of unique occurrences of a person's name.

When I set up a basic Pivot, it counts each occurence of the person's name.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Count Unique Values In Pivot Table
I have a worksheet with a list of employees and the workgroup they belong to, along with other data like manager, start dates, etc. Recently a couple of the workgroups were duplicated (change in managers), so these employees are showing up on two rows even though the workgroup has the same name. The only differences in the two rows are the workgroup effective start and end dates. I need to be able to count, in a pivot table, the number of unique employee/workgroup combinations there are per workgroup. I can add columns to the sheet, but it's a dynamic set of data that will grow each time it's refreshed...

View Replies!   View Related
Count Of Unique Values In Pivot Table
I'm trying to create a pivot table that will count how many employees have completed a Learning Plan. This task becomes complex (for me) because each learning plan has multiple Courses, each Course has a status of "Completed" or "Incomplete". A Learning Plan would only be considered "Completed" if all the courses within that Learning Plan were completed. In doing some research, it looks like I'll need to create another column of data, that shows per employee, per Learning Plan, if the entire Learning Plan has been completed, but I'm not sure of the best way to go about this. Please find SampleData attached.

View Replies!   View Related
Count Of Unique Entries As The Subtotal Of A Pivot Table
I have attached an example of what i am trying to do. I can work out how to calculate unique entries by putting in a formula under a pivot table, but is it possible to select an option in the pivot table settting which will give this result?

View Replies!   View Related
Listing Unique Values And Summing W/o A Pivot Table
I have a set of data that I'm trying to identify the unique values in a column and then sum the related quantites against each of those values:

View Replies!   View Related
Pivot - Unique Count
I want to have a unique count field in my pivot table. I already read (via forum search) that this can only be achieved by adding an extra column to the database.

The problem is: I have no clue which formula to use for this column. Please have a look at the attached picture. Can someone provide me with a formula for this example?

View Replies!   View Related
Is There A Way To Get A Unique Count In A Pivot
I have a pivot that shows Patients, Invoices and charges. I want to throw Patient, Inv # and charge amount in the row area and put PatientId in the Page area as a count. It works fine except it double counts patients with more than one invoice...I want a unique patient count.

View Replies!   View Related
Count All Unique Values (text) + Definition Of UNIQUE :D
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?

View Replies!   View Related
Count Pivot Fields In Pivot Table
I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows;

Sub RemoveGhostPivotItems()
Dim ghost As PivotItem
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt.ManualUpdate = True
For Count = 1 To 10
On Error Resume Next
For Each ghost In pt.PivotFields(Count).PivotItems
ghost.Delete
Next ghost
Next Count
pt.ManualUpdate = False
End Sub

My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient. In otherwords;..............

View Replies!   View Related
Pivot Table And Counting Unique Fields
Found what it think is the correct methodology (Pivot table) for doing this, but I'm struggling to apply it.

I have a very simple excel 2007 document that has one column with multiple rows;

blue
Blue
green
red
red
green
Green
blue
red
Blue
blue
Bleue
Rouge
red
Bleue

I would like the easiest way to count the total of each and sort in highest first,

e.g.

red 4
blue 3
Blue 2
green 2


View Replies!   View Related
Unique Values In Pivot Tables
I have a list of purchase order line items. Each Purchase order could have multiple line items, so the list could have many occurances of the same purchase order number. I have designed a pivot table which summaries line items by ordering area (one of the fields in the list).

This works fine but what I would also like to check is the number of purchase orders by area. How can I acheive this? I would prefer not to use any helper columns but will adapt my application if this is needed.

View Replies!   View Related
Count In Pivot Table
I have created a pivot table from a spreadsheet that had around 27 rows for each employee (i.e: each paycheck the employee received). The pivot table turned out great, but I need to know how to make it count how many employees are in each department and show it in the table.

View Replies!   View Related
Pivot Table With Count
My requiremntis to get the number of count for the specific pivot data column.

In the EXCEL I am selecting "reason" and "reason_details" as the " range" and creating a Pivot table based on it.

The resulting PT is not giving me count for each set.

In the attached Excel. I have DATA sheet and PT sheet ( whih is created by Pivot ) and

Required PT. My desired requirement is in the Worksheet 'REQUIRED PT".

View Replies!   View Related
Pivot Table Count Function
I'm working with Pivot tables, and have run into a issue with counting unique cells, when there are multiple like cells. Here is an example

Account
Sally
Joe
Sally

I need to know how many accounts there are (answer=2)
When I use Pivot table, Count of Accounts, it returns a count of 3.


View Replies!   View Related
Pivot Table Count Only 1 Query
the attachment is a sample spreadsheet of what i am trying to do. I would like to create a pivot table so that for each site I can list all of the organic suites individually. Then show a count of only 1 for each site, even when there are several counts for each site. And then for this to be related to a cost of each of the suites so that I can get a total cost for each site.

eg:

site name

organic suites suite cost
o1 x
o2 y
o3 z
Total A
etc

View Replies!   View Related
Pivot Table Grand Total And Sum/count
I started a pivot table for our budget and on the left side I have the account names and about 4 columns of applicable account codes to which I turn on and off when needed. On the right side I have all the budget numbers divided by quarter and halfs. On the right side, I can drop any list of numbers and it does the sum but for some reason, when I drop my 4th quarter numbers, it gives me a count and not sum. How do I change it to sum?

Second problem: I have grand totals number going down the columns meaning I have a sum of all my 1st Quarter numbers but I do not have them going across each line on row. How do I add that?

Third problem: I forgot a list of account codes to be put on the left, how can I add it?

View Replies!   View Related
Count By Categories Within Pivot Chart/Table
I can not get my Pivot Chart to count, sort and categorized my data. I have included a sample file for your viewing. I basically want the data in cells C2:E18 counted, sorted and totaled by categories and locale. I thought the pivot charting was simple, but I'm having quite a challenge with this one. The pivot chart data should reflect as my sample indicates in cells E24:K27

View Replies!   View Related
Count Unique Values, For Specific Values
I have an array that I'm using - and it works perfectly: =SUM(If((FREQUENCY(If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""),If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""))>0),1))

Now, what I would like to do, is see how many of these unique numbers relate to another number in column D
See attachment.

View Replies!   View Related
Count Unique Values ....
So here is my dilemma and I haven't been able to find anything to exacly help me out:

I have a large (100k+ records) sheet of sales invoice details.

I need to add a total item line per invoice number to each record ....

View Replies!   View Related
Count All Unique Values
Is there a formula that can be used to count all unique values in a specified column.
Example:
Col. A
123
123
Test
ABC
ABC
ABC

Result: 3 (since there are 3 unique values in col. a)


View Replies!   View Related
Count Unique Values
Card Number
XXXX5512
XXXX5512
XXXX4211
XXXX4510
XXXX6134
XXXX9035
XXXX6002
XXXX8653
XXXX7826
XXXX8819
XXXX8315
XXXX0274

I'm looking for a formula to generate the number of unique card numbers are in the column...I'm using this daily, simply pasting the new data in so if I want to keep it as automated as possible.

View Replies!   View Related
Unique Values - Table(?)
It is a sample of my table to operate on: excel.jpg

My task is to make a list of mobile phone brand and model name success, fail, reject, keeping in mind that one particular phone could have been contacted several time during the time of the campaign, but it should appear counted in all stats only once (hint: track phones by their unique Bluetooth code).

View Replies!   View Related
Pivot Table (count As A Single Fail Against The Associate)
I have a source data tab laid out like this:

QC Date.......Policy#......Associate.......Pass/Fail......1stError.....2ndError.....3rdError

QC'ers can enter up to 3 different error types committed on a single failed policy, thus the reason for 3 error fields, even though all 3 fields offer the same value list selections (ie ErrorTypeA, ErrorTypeB, ErrorTypeC......). A policy, whether it has one error or three errors assigned, should only count as a single fail against the associate.

My problem comes into play when attempting to pivot the data. I would ideally like to see the pivot table in this format:

(filter)Associate
(filter)QC Date
...
ErrorTypeA.......Count
ErrorTypeB.......Count
ErrorTypeC.......Count
ErrorTypeD.......Count

Where the "count" is the total number of occurances of that error type across all three error fields - 1stError, 2ndError, 3rdError.

But since 1st/2nd/3rd error fields are different columns, I am unable to do a single count.

My pivot ends up looking something like this:

1stError.....ErrorTypeA.......Count
1stError.....ErrorTypeB.......Count
1stError.....ErrorTypeC.......Count
1stError.....ErrorTypeD.......Count
Then repeated for the 2ndError and 3rdError fields.

View Replies!   View Related
Macro Resetting Pivot Table Field To Count
I've recorded a macro that clears a worksheet, fetches data from an Access Table and then creates a new pivot table. The Pivot Table Fields are summarized by "sum" and this worked the first few cycles for the macro. Now it is returning the Pivot Table Fields summarized by "count". Can an option be inserted into the existing code to specify "sum"? Here is the

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'raw data'!R1C1:R205C12").CreatePivotTable TableDestination:="", TableName _
:="PivotTable14"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet. Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable14").SmallGrid = False
ActiveSheet.PivotTables("PivotTable14").AddFields RowFields:=Array("Name", _
"FIELD_ASM_USER_NAME", "Data")
With ActiveSheet.PivotTables("PivotTable14").PivotFields( _
"SumOfSumOfSumOfCYYTD_SHARE_QTY")
.Orientation = xlDataField
.Position = 1
End With..................................

View Replies!   View Related
Find And Count Unique Values
I have a spreadsheet that holds customer information. What I want to do is find how many customers there were last month. I have a cell (C1) which has last month displayed as 2009/05. A1 holds the customer ID and B1 holds the date they used us. Each customer may has used us many times and I'm having a nightmare trying to solve this.

View Replies!   View Related
Count Unique Values With Critera
I have a large list of data and need to filter unique values of 1 column with a criteria.

For example:

A B
Car 1
Bike 1
Car 2
Car 2
Bike 3
Bike 3
Bike 4
Car 4

I want to filter out only the lines whereby the column A & B is the same comment/value.

Other words: both column A & B have to be unique values.

Based on the list above this means that the list should look like this:

A B
Car 1
Bike 1
Car 2
Bike 3
Bike 4
Car 4

View Replies!   View Related
Count/Sum Unique Values/Entries
Can DCOUNTA be used to only count unique values?

If the above is not possible how would I do the following:

Criteria

Column H = text1 AND Column B date is between date1 & date2

Count unique values in column C

View Replies!   View Related
Count Of Unique Values With Duplicates
Good Morning Peps (Oh thats for users in Western Europe, got to get this right!)

I have these 12 columns (Well there is 12 Columns but this forum's HTML is not showing the last two lol, would much easier if Office Web Components was able to run on Firefox): ...

View Replies!   View Related
Count Unique Values Under The Influence Of Different Variables
My data: four variables A,B,C and D.

A growing dataset representing a group of people being nominated in different weeks for different jobs in different locations. Within a week and over several weeks an individual may be nominated for any number of jobs, in any number of locations.

A=week number (52 possibilities), B=job code (20 possibilities), C=location (2 possibilities), D=name (500+ possibilities) .....

View Replies!   View Related
Count/Sum Unique Values Based On Criteria
I'm facing a big problem and I can't found a solution for days.

I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below:

=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))

Let me Put to you an example for you been able to understand my problem.

Example considering Range "C2:C20" exluding blank cells

(1) - To count unique values
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))

(2) - To count unique values based on criteria in range "B2:B20"
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E"))

(3) - To sum unique values on "E2:E20" based on same criteria
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20))

They all work fine. But I need to insert a merge at Row 10 for
better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's.
I just don't know what to do guys ....

View Replies!   View Related
Count Unique Values Based On Another Column
I am using Excel 2007, and I'm stuck with this problem.

View Replies!   View Related
Count Unique Values In A Filtered Column
I need to count the number of unique names from a column of filtered text. On the attached example D5 is where I need the value, the current array I have does not work when the filter is applied by changing the product in drop box. I need to have a count of the unique account names from column A.

View Replies!   View Related
Count Unique Values With Specific Criteria
I need to count the amount of Unique codes in column A BUT ONLY if they are still OPEN status. Hopefully the attached example will be explaination enough (I want to automate the RED figure...

View Replies!   View Related
Count Unique Values Based On Matching Criteria
I have a worksheet with two tabs.

First Tab
Account Name
Account Number

Second Tab
Account Name
Account Number
Account Ship Location Number

On the first sheet each account name and number only appears once.

On the second sheet there may be multiple entries for each Account Name and Number.

On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.

View Replies!   View Related
How To Count The Number Of Unique Values In A List, Based On Another Value
I need to count the unique values in a list based on another value. i.e.

AB ___ Jones
AB ___ Smith
BC ___ Henry
AB ___ Jones
BC ___ Brown
BC ___ Henry
AB ___ Tomkins

For all the AB's in column 1, I need a formula that will give me all the unique names from column 2, in this case 3. I've tried adapting:

=IF(LEN(June!D5:D100),SUMPRODUCT(1/COUNTIF(June!D5:D100,June!D5:D100)))

but haven't been able to get it to work.

View Replies!   View Related
Count Unique Values, And Also Average The Time Per Assignment
I am working on a Productivity Log and can't figure out a way to count unique values, and also average the time per assignment.

Assignment with the action code A - should take 2 hrs to complete, all other action codes should take 20 minutes.

I need to show how many of each action codes were done for the day, and what was the average time taken to complete each. Even better, how much for A action codes, and how much for all other.

View Replies!   View Related
Why Does Pivot Table Double My Values When It Is Set To Sum
I can't figure it out it simply doubles the value the only thing i can think of is the totals in question that are related to subtotaled columns but is not subtotaled

View Replies!   View Related
Sum The Highest 3 Values In A Pivot Table Row
I have a Pivot Table with 10 columns of data (numbers with a value of 0 to 1000) and a Total per row - all okay (1500 or so records).

For each row, I need to add the highest 3 values from the 10 columns to make a new total. Is there an easy formula I can add to the Pivot to do this?

View Replies!   View Related
Manipulating Pivot Table Values
I Am trying to create a pivot table. I have attached a sample of what i want. I have same kind of data for different dates. I would like for one field to list out seperately under each date and one field to sum up for the entire week( Total paid hrs). How do i go about doing this... is there a work around?

View Replies!   View Related
Pivot Table Saves As Values Only
We are using a file containing a pivot table in the 2007 version of Excel. Every time the file is saved under a new name for a new month, the pivot table becomes a table of values only (in other words it is no longer a pivot table!) The pivot links to an Access query (the 2007 version of Access). What is causing this?

View Replies!   View Related
Pivot Table - Subtotal Of Negative Values
I have data in a pivot table that has some positive and negative values. At the end of the Pivot Table, it sums the values for each month and then a grand total.
I would like to also have it show the summation of all negative values per month. So it would be:Jan Total
Jan Negative Total
Feb Total
Feb Negative Total
etc...
Grand Total
Grand Negative Total
Is it possible to add such functionality?

Image for reference:

View Replies!   View Related
Copy Pivot Table Values And Formatting Only
In Excel 2007, is it possible to copy & paste a pivot table, and have the result look like a pivot table, but not actually be a pivot table? I want to keep the values and the formatting (the colors and borders, etc) but I want it to not actually be linked to the data or have the ability to change with dropdowns, etc. I've tried the usual copy & paste special (values) thing, and the other otions in the paste special box, but it doesn't keep the formatting.

View Replies!   View Related
Pivot Table Drilldown For Multiple Values
Is there a way to drill down the data of 100 values in a pivot table into one worksheet without resorting to drilling down each value, having 100 worksheets for each value, to paste into one worksheet? I got my company to give me an experimental computer to test for this in 2007, though if there is a way to do this in 2003,

View Replies!   View Related
Display Time Values In Pivot Table
why when pivot tables display individual or single time values they are rounded up or down. i.e. a value of 1:31.5 is displayed in the pivot table as 1:35.0 etc. I have tried custom formatting the table itself but to no avail.

View Replies!   View Related
List Values From Excel Pivot Table Field
I have a Pivot Table with pivot fields and data. I would like, through VBA, to get the list of values that can be chosen from a given pivot field.

For example, a list would be Product1 / Product2 / Product3 .... I would like to read that list and put it into a drop down list in a form.

GOAL: I have several Pivot tables on the same sheet with similar fields and I want through macro to allow the user to update them all with one click.

View Replies!   View Related
Format Cells Based On Values In Pivot Table
I have a pivot table and next to it I added some calculations which are based on data from the table. In my "real" file, the cells next to the PT are formatted with conditional formatting based on other values in the sheet (see attached sample file). What I now need to do is add borders to the cells in columns D:G only if the cells in B5 and downwards are not empty. I have tried doing it with conditional formatting but there are some other rules which contradict this setting.

The idea is to make the cells in columns D:G look like they are a natural extension on the PT. However, the table can grow or shrink (in length) and I want to see borders only around cells that are on the same rows as the data in column B (staring with B5). I think that whatever solution you can provide (if you can...) should be based a workbook_change event, because in my real file, the length of the table is changed whenever I select a different page of the PT.

View Replies!   View Related
Copy Pivot Table & Paste As Values & Formats
my macro pulls download in on sheet1. On sheet2 it makes a pivottable of it with horizontally displayed the suppliers and vertically the codes of products. This is of course dynamic (one month it may contain 10 suppliers + 8 products, other month 15 suppliers + 20 products). On sheet3 is the (static) lay-out of all suppliers and all products. Now, what I want is that all fields <> empty (or zero) from the pivot table are to be copied and placed in sheet3, the 'report' I have to fill in. index and match won't do the job I think.

View Replies!   View Related
Create Pivot Table: Cannot Open Pivot Table Source File
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.

View Replies!   View Related
Pivot Table Query: Make A Pivot Table To Summarise The Data
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.

View Replies!   View Related
List Values In Pivot Table Page Field Dropdown
I want to print one sheet for each of the values in a page field drop-down. I can do this by supplying the values individually (code below) but would rather determine the list of values dynamically. Can this be done?

Private Sub pbPrintAll_Click()
Dim cix As Integer
Dim Ctrct As String
cix = 3
While (Sheets("Database"). Range("B" + Trim(Str(cix))).Value <> "")
' Get value from database sheet
Ctrct = Sheets("Database").Range("B" + Trim(Str(cix))).Value
' Set CurrentPage value
Sheets("Customer P&L Pivot1"). PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = Ctrct
' Print formatted sheet
Sheets("Customer P&L").PrintOut
cix = cix + 1
Wend
' Reset Current Page value
Sheets("Customer P&L Pivot1").PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = "(All)"
MsgBox "Prints sent to printer."
End Sub

View Replies!   View Related
Pivot Table Labels Based On Comma Delimited Cell Values
"Is there a way to make values in a cell that are comma delimited (ex. cat, dog, fish) became separate labels in a pivot table instead of getting labels like 'cat, dog', 'fish, cat', 'cat, dog, fish' which represents the exact value in the cells.

The only real option I can think of would be to make an extra rows with the same data for each pet type. For example if the pets were 'cat, dog', there would be two almost identical rows except one would have 'cat' and the other 'dog'."

I have made a vba setup which can solve the problem. But it is pretty complicated and unstable. I need a solution which is not vba based. ~Or a least a very simple code!

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved