Count Unique Items
Jun 16, 2009Counting unique items in column (numbers)
View 9 Replies
ADVERTISEMENT
Count Condition Unique Items
Jan 22, 2009Can someone please help me with formulas to calculate the # of Unique Customers, #of Items, and Amount for Q1, Q2, Q3 and Q4 for the year in A2 (which contains a drop-down list).
Please see attached sample workbook. (I can’t figure out how to combine the formulas for count unique items with a specific quarter.)
Count Total Items Per Unique ID
Feb 19, 2014I need a formula to count the total items per unique id. For example
Column A Column B
11111 Basketball
11111 Basketball
11111 Basketball
33333 Baseball
33333 Baseball
22222 Hockey
Output to Column C,
ID 11111=3 Basketball
ID 33333 2 Baseball
ID 22222 1 Hockey
Count Unique Items With A Condition?
Oct 30, 2012I've an excel sheet with 1000 rows data. I need a formula to count number of unique items in a column with a condition. Please find a sample data sheet.
I've tracking list that has ordered and delivered items for a given month on weekliy basis such as Week1(W1), Week2(W2), etc. I need a formula to count number of unique item delivered on this month.
Item 1 to item 8 delivered and item 10 also delivered in week 4, So totally I need to get the result as 9.
I was trying to use "countif" for validating and counting each row, it's throwing value! Error =1/(COUNTIFS(A:A,A2, c2:c23, ""&0)) i.e., = 1/countifs(unique items from column A, then nonblank cells in column C) but it's giving Value error..
I just need to count the unique item with another condition.
Item details
Order week
Delivered Week
Item 1
W1
W1
Item 2
W1
W1
[code]....
Count Unique Items In A Column
Dec 7, 2007Looking for a formula that will give me a count of unique entries (i.e. duplicate entries to not be counted.)
basically a formula that says look in D4:D4001 and count only one of each entry....do not count the duplicate entries.
Count Unique Items In Autofilter
Jun 10, 2007I have a Excel sheet and I have put Autofilter for a particular category I choose I need to count number of items in another column(autofilter) , for eg... there are two columns Category , Items When i choose a particular Category I need to count number of Items in the autofilter for that particular category?
View 9 Replies View RelatedCount Unique Items In Filtered Range
Dec 13, 2009As the subjects states I need to count the unique entries in a filtered range.
View 7 Replies View RelatedCount Unique Items With Multiple Criteria
Aug 12, 2009A B C D
1 Yes Red Green
2 Yes Blue Black
1 No Blue Black
2 No Blue Green
2 Yes Red Black
I am interested in finding the number of unique entries under column A where column B = Yes; Column C = Red OR Column D = Black (answer is 2)
Creating A Macro To Count Unique Items In A List
Jun 1, 2006I need to count the unique for a customer. The areas to be counted are separated by blank lines. At present, I am using sumproduct(1/countif(range1,range1), to count the unique items. This formula works perfectly except that it takes me almost an hour to do this for all the measurements. Is there a macro that can provide me with the same results -- putting the same values where I am presently have the formulas (the cells that before the blank lines)?
View 6 Replies View RelatedCount / Calculate And Graph Sales Of Unique Items Over Time?
Nov 24, 2013My company has a catalog of ~6000 unique SKUs that we sell online. Currently we do not have a way to quickly determine how much a given item has sold month over month, and the rate of that change.
I have an excel workbook full of our last year's worth of sales data. I have organized it by placing each month of data in its own worksheet. I would like to be able to create a search box that will allow us to enter in a product ID, and have excel then create a graph with each month's of sales for that particular item. Is that clear?
Pivot Table To Count Unique Items In Large Dataset
Oct 15, 2012I'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 RelatedCount Items In Column That Match Multiple Data Items?
Mar 27, 2014I need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
Creating A Unique List Of Items In Column A That Have A Corresponding Non-zero Value In Column B, I.e. Excluding All Items Where Sumif ColumnB Would Sum To 0
Jul 17, 2009I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
Count All Unique Values (text) + Definition Of UNIQUE :D
Aug 24, 2009If 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 14 Replies View RelatedAdd Unique Items
May 7, 2007I have a code which works perfectly and adds the dates from Column A to UserForm1 ComboBox2.
In column B, I have textdata - UserForm1 ComboBox5.
I'd like to transform the code to add only the dates which correspond (cascading) to 1.1 textdata in ComboBox5 (Column B) and 1.2 dates in ComboBox2 (Column A).
Code: ...
Copy Unique Items
Jan 8, 2009How to make it copy over A, B, and C not just A and B? I tried adjusting some of the ranges, but I get a Run time Error 104. Also, what is the (Rows.Count, 3) doing?
Sub CopyUnique()
Dim LR&, LR2&
Application.ScreenUpdating = False
With Sheets("AY")
.Range("A1:B1").EntireColumn.Insert
LR& = .Cells(Rows.Count, 3).End(xlUp).Row
.Range("C1:D" & LR&).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A1:B" & LR&), Unique:=True
LR2& = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("A1:B" & LR2&).Copy Sheets("ORG").Range("A1")
Sheets("ORG").Columns("B:B").Columns.AutoFit
.Columns("A:B").Delete Shift:=xlToLeft
Application.CutCopyMode = False
End With
Application.ScreenUpdating = True
End Sub
List Unique Items ...
Jun 15, 2009I have a list of >50 contract numbers listed in cells A1:A350, with several of the numbers listed more than once. What I would like to do is, on a separate worksheet, list each contract number only once, in column A.
View 9 Replies View RelatedListbox With Unique And Dependent Items - VBA
Sep 25, 2013I have a Listbox1 in which I was able to populate it with unique items (from column B in Programs worksheet) using a collection (that was a good achievement for me!)
Now, I want to populate a the Listbox2 with items (not repeated) from column C, BUT only if the correspondent value on column B is equal to listbox1.value.
[Code] .....
Retrieve Unique Items With 2 Criteria
Nov 29, 2005I've been using the following formula from the xldynamic website detailing
sumproduct usage as a base for retrieving uniqe instances in a data series.
I've been trying to modify the formula to allow me to utilize one additional
criteria.
Original formula:
=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))
Here is my formula:
=SUMPRODUCT((($O$7:$O$2710<>"")*($M$7:$M$2710="1986"))/COUNTIF($O$7:$O$2710,$O$7:$O$2710&""))
I'm trying to say how many unique instances are there for column O, where
column m = "1986?"
Dynamic List Of Unique Items?
Sep 13, 2012I have a sheet of un-ordered data with duplicates. Looks a little like this:
Sheet2
*
A
B
[Code]....
What I want to do is: on a separate sheet, when the user inputs a Region name, it will return an array of unique Locations under that Region. For example, if the input is "NORTHEAST", the following would be returned:
Sheet3
*
A
B
C
[Code]....
Populate Combo Box With Unique Items
Dec 19, 2006I have a combo box (from the Control Toolbox toolbar). I like this to be populated with the contents of column A. however, in column A there can be duplicates. I obviously don't wan't any duplicates in the combobox.
View 9 Replies View RelatedCounting Unique Number Of Items
Sep 15, 2008I want to paste a list of customers into a sheet and then have a formula that counts the number of customers. So, even if a customer appears 10 times it would only count it once and and continues down the column and counts the next company name and so on. Then it gives me a total of the number of different companies in that column.
View 9 Replies View RelatedSubtotal Non-Unique Items By Employee
Nov 28, 2008I have a list of sales associates by items by manufacturer. I need to count how many manufacturers are represented by each associate. It might look something like this:
Smith, John Brand A Beer 12 oz Beer Industries, Inc.
Smith, John Brand A Beer 8 oz Beer Industries, Inc.
Smith, John Brand B Vodka 700ml Vodka Industries
Jacobs, Sally Brand C Wine 1989 Wine Makers Co
Jacobs, Sally Brand C Wine 1979 Wine Makers, Inc.
Jacobs, Sally Brand D Wine 1908 Old Wine Makers, Ltd.
Jacobs, Sally Brand E Beer Six Pack Fun Beer Co.
So the totals would be:
Smith, John Total # of Manufacturers: 2
Jacobs, Sally Total # of Manufacturers: 3
So the formula ignores the duplication in employee names and manufacturer names.
Is this possible? I'm thinking something like =sumproduct((Range=AssociateName)*(sum(1/countif(ManufacturerRange, ManufacturerRange)))
Add Unique Items To ComboBox Control
Aug 30, 2006I have a database of data that looks like this
Centre Name Training Type Expert 1 Expert 2 Expert 3 Expert 4
MyCentre MyTraining Me
MyCentre MyTraining Me
MyCentre MyTraining Me
MyCentre OtherTraining Me
NewCentre NewTraining NewPerson
I am trying to create a user form (in VBA) with a combo box that has each centre name appear only once (despite the fact that in the database each occurs multiple times). When a specific centre is selected, this brings up all the different training types associated with that centre in a separate list box. When that training type is selected, it should bring up all of the experts in that training type (for the specific centre). I should mention that the range is static (though the data is always contiguous) as the database is updated on a going forward basis.
Formula To Create List Of Unique Items
Feb 12, 2009I have a column that looks something like (it is VERY long and has over 20 different names):
abe
abe
abe
abe
ben
ben
ben
cat
cat
john
john
john
john
tex
tex
I want to create another column (automatically) that lists each of of those once:
abe
ben
cat
john
tex
Any idea of how I can do this automatically?
Counting Unique Items(values Or Text)
Aug 3, 2006I want to count unique items in a list with an array formula, like the items is in 3 columns A,B,C are from row 2 to 101. Now i use an array to filter out some rows in the columns of B,C & then count unique items in Column A.
so,
column A has "30 diffrent names repeated from A2 to A101"
column B has numbers 0 to 100
column c has value either 0 or 1
now i use an formula TO filter out rows in column B & c
formula =count(IF((B2:B101>0)*(C2:C101<>1),1))) using ctrl+shift+enter
now what should i suffix or prefix to this formula to count unique values in column A.
Counting Number Of Unique Items In Column
Oct 10, 2008I am trying to count the number of unique items in a single column (~5,000 rows of data). For example, I may have the following data
a1 = apples
a2 = pears
a3 = oranges
a4 = apples
a5 = apples
a6 = apples
a7 = pears
in this case number of uniques items is 3
Right now I am using a Pivot Table to figure out the number of unique items but I am sure there is an easier way to do this.
Counting Items Unique To A Day/customer Combination
Feb 3, 2010I need to identify the number of occasions on which a product type is bought by a customer in isolation from other product types. I have attached a sample to illustrate. The actual data is more complex and is actually medical data concerning issue of oral or IV drugs. There are many thousands of records.
To clarify, in the example, there was only one occasion when Bread was bought on its own by a particular customer on a particular day. The way the data is presented, 'Bread' could be listed before 'Milk' or, as with 'Steve' on the 2/4, it could be in the middle of a series of 'Milk' purchases. I can sort by date/name/type, but I cant work out a formula to resolve the count.
Populate Combobox List With ONLY Unique Items ()
Nov 16, 2006Populate Adv Filter Criteria from Inputbox with valid data
I've set up a macro to extract 3 different sets of data using Advanced Filter (same data range, different criteria & extract ranges)
There are multiple options (different individual dates) to satisfy the criteria (a date) for each of the 3 extracts and I want the user to select the criteria (a specific date) from the range of unique values in the data range upon which the Advanced Filter criteria is going to be applied. (A bit like the effect provided by Autofilter)
My initial attempt was to capture the criteria date from the user and populate a cell, and then have a vlookup function in another cell check if that crieteria value exists in the specified range. I then ran a loop in the VBA code until the value is valid (ie. exists in the range). However, to streamline this I was hoping to allow the user to see a list of the valid (available) dates when they respond to my Inputbox request for the criteria.
Display Unique Items That Occur More Than X Times In Column?
Jan 25, 2012I have a long column of 9-digit numbers which represent the swipe-card-ID's of patrons accessing a parking facility. Most of them occur more than once, some of them many times. There is an operational rule that bars them from using the facility more than x times. (I can't implement this limitation in the parking software/hardware, or I would happily do so.)
So my question is, is there a way to process through that column, identifying each unique ID, and reporting any that occur more than x times?