Listing Unique Values And Summing W/o A Pivot Table

May 27, 2009

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 14 Replies


ADVERTISEMENT

Count Unique Values In A Pivot Table

Mar 3, 2004

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 9 Replies View Related

Count Unique Values In Pivot Table

Feb 28, 2008

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 9 Replies View Related

Count Of Unique Values In Pivot Table

Jun 19, 2008

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 5 Replies View Related

Count Unique Values By Month In Pivot Table?

May 16, 2013

I have data that has a customer name and the month that they were taken care of" (of course not spaces

Customer Month
Smith January
Smith January
Mark January
Suzy January
Smith February
Mark February

I want a pivot table to say:

Month #customers
January 3
February 2

but when I do the count it adds the total number of customer names and gives (it is counting smith twice in January

Month #customers
January 4
February 2

I was reading something about doing the count (so it shows 4) and then doing an index within the count but I keep just getting 1 as the value (for both months).

I tried doing a formula in the document that said =IF(ISERROR(MATCH(A2,$A$1:A15,0)),1,0). basically looking to see if the name is above the location I am looking at and if it is to put a 0 if not put 1 and then do a sum of that column....trouble is that when a customer has orders on more than one month, the second month is coming up with a 0 instead of a 1 (and this happens quite often).

I am not sure what to do. if the customer shows up in more than one month I want them to count towards the unique count of both january and february...we look at each month separately.

View 3 Replies View Related

Excel 2010 :: Count Unique Values In Pivot Table

Jun 20, 2014

I have data set up like the example but then for 1797 lines.

D39BKYes
D39BKYes
D39BKYes
D39BKYes
GHFLBNo
R80FANo
R80FANo

[code].....

What I need to get in a pivot table is: two columns or rows (Yes / No) and the unique count of the code.In this case is should show Yes: 2 and No: 3

View 2 Replies View Related

Excel 2010 :: Count Unique Values On Pivot Table

Jun 21, 2013

How do count unique values in category in pivot table. (my table, im taking data from ms query). I am using excel 2010

Pivot table example: the result i want is the "no of types" as my data only show "category" and "types".

Category
Types
No of Types

Fruits
Apple
Pear
Orange
3

Vegetable
Cabbage
1

View 4 Replies View Related

Pivot Table With Columns Listing Counts

Dec 10, 2012

I have a spreadsheet similar to this:

Name........Cat....Dog....Fish....Mouse
NY............Cat.....Dog....Fish..........
NY............Cat...............Fish....Mouse
DC.....................Dog..............Mouse
DC...............................Fish............
CA ...........Cat................................

I want to create a pivot table to do this:

Name.........Cat....Dog....Fish....Mouse
NY...............2.......1.........2........1..
DC...............1......1..........1............
CA ...............1.............................

I have been unable to get it formatted correctly. The closest I get are "sub-colums" with counts.

View 2 Replies View Related

Listing Pivot Table Field List Fields

Oct 17, 2006

Is there any code or way to generate the compete list of fields that are selectable from the " PivotTable Field list"?

I have various cubes I need to check and compile the list of fields for each.

View 7 Replies View Related

Moving Cell Values Appearing Multiple Times Into A Table Listing Them Only Once?

Jul 5, 2014

I have a table listing certain values (Column C) and their respective probabilities, based on normal distribution (Column D). As these probabilities are dependant only on values from Column C, I'd like to list all the values from that column into another fragment of the sheet, say F6 and below, but each value should appear there only once, no matter how often it occurs in its original Column C (I listed them manually in Column F in the enclosed example). Moreover, if a new value appears in Column C, it should be also included in the new place and sorted in increasing order.

View 7 Replies View Related

Summing A Pivot Table Based On For Criterias

Jan 16, 2009

I do not know if this is possible, I have a pivot table, however I would like to be able to sum a particular range based on start and end date. then by make and model as the second set of criteria, The sum would be displayed into a form on a different worksheet. attached is a file so I would like to know the total

View 3 Replies View Related

Pivot Table - Stop It Summing Date Entries

Dec 5, 2013

How do I stop a pivot table adding data together?

e.g.

1st Nov -100
1st Nov - 100
2nd Nov - 200
2nd Nov -200

I want this but instead get this:

1st Nov - 200
2nd Nov - 400

I need the pivot to split it out,

View 1 Replies View Related

Summing Multiple Table Values Using VLookup

May 20, 2013

I'm trying to get a more manageable formula for totaling multiple values in one box using Vlookup. For clarification this is what I mean:

On spreadsheet A, I have this table:
[IMG][/IMG]

The value in B2 is the name of the item I am making and the items below are the components required to manufacture said item. In order to build them, I need so many (Column E) and I have indexed my remaining values simply by doing =E3-H3. To build each component requires minerals which are listed on a seperate sheet (Sheet B) as such:

[IMG][/IMG]

The only Value in the above table I am truly interested in is "Current" (E Column).

Back on SheetA, I have a second table which adds all of the component minerals up for a grand total of the minerals required to make the item in B2 as such:
[IMG][/IMG]

Now, this is my issue: The code for N3 is incredibly unwieldy:

=IF($F$3>0, $F$3*VLOOKUP($L3,
'Component Materials'!$A$15:$E$21,5,FALSE))+IF($F$4>0, $F$4*VLOOKUP($L3,
'Component Materials'!$A$25:$E$31,5,FALSE))+IF($F$5>0, $F$5*VLOOKUP($L3,
'Component Materials'!$A$45:$E$51,5,FALSE))+IF($F$6>0, $F$6*VLOOKUP($L3,
'Component Materials'!$A$55:$E$61,5,FALSE))+IF($F$7>0, $F$7*VLOOKUP($L3,
'Component Materials'!$A$65:$E$71,5,FALSE))+IF($F$8>0, $F$8*VLOOKUP($L3,
'Component Materials'!$A$115:$E$121,5,FALSE))+ IF($F$9>0, $F$9*VLOOKUP($L3,
'Component Materials'!$A$135:$E$141,5,FALSE))

I set this up to look at a specific value and if it was >0, it would multiply that value times whatever value it found on SheetB ("Component Materials"). The size of this formula makes this difficult to migrate to further items, so I would like to reduce the complexity of the formula.

View 7 Replies View Related

How To Count Unique Customers Using Pivot Table

Oct 7, 2011

I have the following data:

TRX_IDQTYCUST_ID892486021-67.991623230892486140-35.411623230892486378-29.271623230892486515-60
.711623230892488006-69.081623230892488082-43.261623230892486668-31.091623230892486794-27
.0562118898892546057-42.829337675892487417-19.331623230892487513-33.251623230892488890-33
.61719407892543537-13.891623142892544321-11.021623142892487602-18.921623230

Would like to know the number of unique customers (CUST_ID) have purchases and the sum of those purchases and how many transactions:

CUST_ID Sum of QTY Count of Transactions
50 $2000 1500

I cant seem to find how to do unique Cust using a pivot table - it just gives me a count of cust which is the same number as the number of transactions.

View 5 Replies View Related

Pivot Table - Count Of Unique Value Not Total?

Feb 9, 2012

The pivot table has only two columns, the first is the identification number and the second is the count of the identification number. I am trying to get a count of the number of identification numbers, not how many times it was entered in the spreadsheet (some numbers are entered more than once on different days). It currently looks like:

Column A Column B
00000001 1
00000002 1
00000003 2
00000004 1
00000005 3
Grand Total 8

I'm trying to arrive at 5 for the answer, so that each number is only counted once even if used more than once.

View 3 Replies View Related

Unique Item Count In Pivot Table?

Jun 29, 2012

I have 2 columns. One with a store number. The other with an item. I need to get the count of each item for each store. So:

Store, Item
1, Cashier 1
1, Cashier 2
1, Cashier 2
1, Photo PC 1
1, Cashier 1
1, Cashier 2
2, Photo PC 1
2, Cashier 1
2, Cashier 2
2, Cashier 1
2, Photo PC 2
2, Photo PC 2
2, DriveThru 1
3, Cashier 1
3, Photo PC 1
3, Photo PC 1
3, Photo PC 2

I would like to get to

Store, Cashier 1, Cashier 2, Photo PC 1, Photo PC 2,
1, 2, 3, 1, 0
2, 2, 1, 1, 2
etc....

How can this be done?

View 3 Replies View Related

Pivot Table And Counting Unique Fields

Feb 9, 2008

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 9 Replies View Related

Summing Up Values In Column Corresponding To Unique Distinct Values In Another Column?

Dec 24, 2013

I have two columns in excel, col A and col B.What I would want to achieve is following:

Col A Col B
A 2
A 2
A 2
B 3
B 3
B 3
B 3
C 3
C 3
C 3

Now I want a total of values in column B pertaining to unique (or say 1st occurences) of values in Col A. So in this case the output should be 8 ,Achieved as follows(2 +3+3).

[URL]

View 5 Replies View Related

Count Of Unique Entries As The Subtotal Of A Pivot Table

Jan 28, 2010

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 2 Replies View Related

Sum Of Unique Values Using VBA Pivot

Nov 9, 2011

How to use Pivot table and its features in VBA. Lets say i have a data as shown below.

Col ACol BDataVolumeAAA10BBB20CCC30CCC40CCC50CCC60AAA70AAA80BBB90

I would Like to have a VBA code using Pivot where Unique values from Column A is taken and its sum of the volume in it (as shown below).

Col CCol DDataVolumeAAA160BBB110CCC180

View 5 Replies View Related

Excel 2007 :: Extracting All Unique Items From Pivot Table

Feb 20, 2012

I am looking for a way to extract all of the unique items in a Pivot Table. For example, I have a Pivot Table that has category A items and category B items. There may be several B items to one A. For example:

A0
B1
B2
B3A1
B3
B4
B5

Now, what I need is a list that looks like this:

A0 - B1
A0 - B2
A0 - B3
A1 - B3
A1 - B4
A1 - B5

Formatted so that those are two columns. However, in Excel 2007, I cannot set the option to have it repeat the A series items in the Pivot table. Is there some easier way to do this?

View 2 Replies View Related

Pivot Table To Count Unique Items In Large Dataset

Oct 15, 2012

I'm trying to count how many production orders i have per week. However, there are duplicated production orders per week. I only want to count how many unique orders there are for each week. I only see the ability to "Count", which counts my duplicates as well so it over inflates my true quantity.

View 3 Replies View Related

Unique Values In Pivot Tables

Jul 19, 2005

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 8 Replies View Related

Pivot Table Layout - Generate List Of Unique Names And Their Associated Phone Numbers

Mar 18, 2013

I've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.

I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.

But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.

View 7 Replies View Related

Unique Values - Table(?)

Nov 25, 2008

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 5 Replies View Related

Formula To Count Unique Values Within A Group For Pivot - Paste Special

Jun 26, 2013

I use the following formula to count unique values within a group for a pivot:

=IF(SUMPRODUCT(($CO$2:$CO55681=CO55681)*($E$2:$E55681=E55681))>1,0,1)

When I paste values over the formula it takes around 1 hours to do the 70k cells which is excessive. I want to know if there is a way to speed it up?

I tried doing an =cell and then paste special that and it works really fast but when I delete the actual formula's again the whole sheet locks up.

View 9 Replies View Related

Identify Unique Values In Table?

Mar 11, 2013

I am trying to get an Array formula to pull unique values from an ENTIRE table. I was able to write one just for a column but not for an entire table. The unique value column is my desired results - t

View 11 Replies View Related

How To Create Table Of Unique Values

Mar 17, 2012

I have a Column, say column A, setup with a lot of repeated data. Now what I want is to pick only a single value from each set of data and put them into a separate column. for example:

Col A
1
2
3
2
4
1
5
4
1s
a2
1s
a2
a2
6

What I want the excel to do is to pick from the above data only a unique value and put them into a separate col, like this:

Col C
1
2
3
4
5
1s
a2
6

View 5 Replies View Related

VBA To Return Unique Values From Table

Jul 5, 2013

This is my first post here!

The Question:

This is a hard one, I promise! Is there a way/formula/VBA to return multiple unique values from a table or from multiple columns with criteria?

My Objective

I want to create a table that has unique company names in the first column (done already) and all the connections that belong to a given company in the columns #2....#n. Like this:

Column1 Column2 Column3 ..... Column n
Company X1: Co. ABC Co.XYZ Co.Acme
Company X2: Co. SEB Co. Becme Co. Cecme
....
Company Xn: Co. X1 Co. ABC Co. Acme

I have a small sample that has 20.000 rows and 25 columns of data of 1946 unique companies downloaded from my schools database. This means that doing this by hand would take forever!

The data has many duplicates which makes this complicated. The first column shows company name and columns 2...n present connections. Like this:

Column1 Column2 Column 3 ...... Column n
Company X1: Co. ABC Co. XYZ Co. Acme
Company X1: Co. ABC
Company X1: Co.Acme
Company X1: Co. XyZ Co. ABC

In this example Company X1 is connected to companies ABC, XYZ and Acme. The formula should be able to filter/omit overlapping entries.

View 1 Replies View Related

How To Sum Pivot Table Filtering Values In Other Table

May 12, 2014

how can i sum pivot table filtering some values in other table. if i change filter, sum is changed

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved