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
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
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
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 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
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
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 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 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/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 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
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
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
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
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
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
Change The Date On One Of The Pivot Table And Pivot Table Match
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.
View Replies!
View Related
|
TRACKER
