Counting Items Unique To A Day/customer Combination
Feb 3, 2010
I 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.
View 3 Replies
ADVERTISEMENT
Sep 15, 2008
I 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 Related
Aug 3, 2006
I 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.
View 11 Replies
View Related
Oct 10, 2008
I 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.
View 6 Replies
View Related
Apr 18, 2014
how can I retrieve a unique list coming from a combination of 2 columns. Using VBA would be much better.
e.g.
Col1Col2
RED 1
RED 2
BLUE 1
BLUE 1
RED 1
BLUE 3
BLUE 2
BLUE 1
RED 1
Result will be
RED 1
RED 2
BLUE 1
BLUE 3
BLUE 2
View 3 Replies
View Related
Jan 15, 2010
I am looking for an efficient solution to the following problem. I have a sales table with two columns, titled C1 and C2. The first column lists the product sold, and the second column lists the associated customer.
Here's what I mean (though I can't figure out how to create neat columns in this post):
[C1] [C2]
Prod1 CharlieCo
Prod3 AlphaCorp
Prod2 BetaInc
Prod3 BetaInc
Prod1 AlphaCopr......................
View 5 Replies
View Related
Dec 6, 2013
I used the RANK and COUNTIF combination to get the unique ranking of a range. For example, the formula is:
=RANK(A1,$A$1:$A$890,0)+COUNTIF($A$1:A1,A1)-1
To my surprise, the failed to produce the results that I want. I attached a sample file here. Basically, the two ranks of 416 and 418 are not there.
A source below really shows the formula should work. [URL] .......
View 2 Replies
View Related
Nov 20, 2013
I have a (phylogenetic) matrix with a species in each row, and morphological character in each column, scored as 0, 1, 2, 3 or 4 for each different species, so something like the following but much bigger:
Character 1 Character 2 Character 3
Species A 1 0 1
Species B 3 2 2
Species C 1 2 0
I want to identify the minimum combination of different columns (i.e. their values, but also which columns) that make a particular row unique.
View 14 Replies
View Related
Jul 17, 2009
I 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.
View 9 Replies
View Related
Jul 20, 2009
I have a separate sheet with the followingvery simplified as I probably have over 300 lines of dates and dollars)
Date Dollars
2/3/2009 $25
5/3/2009 $30
5/4/2009 $50
7/4/2009 $100
8/7/2009 $25
On another sheet, I want to count the number of items by month submitted and sum the $ amount.
January $0
Febuary $25
March $0
April $0
May $80
June $0
July $100
August $25
Through December
Pivot table would be best if possible....but any function that would work would be fine.
View 9 Replies
View Related
Aug 14, 2014
I have a spreadsheet that I use to log incidents. In column "A" I have the date in month/day/year format (8/14/2014). I am attempting to automate the graphing process so I have a graph that will tell me how many incidents I had in the month of August. I have attempted the following formulas (and other similar formates with minor changes)...
=COUNTIF('CY14'!A2:A500,"8,2014") and changed around the date format
=COUNTIF('CY14'!A2:A500,"&DATE(8,2014))
=COUNTIF('CY14'!A2:A500,"August")
All of these come up with "0" while I have 17 listed incidents for August. Am I doing something wrong, or is this not possible.
View 4 Replies
View Related
Nov 5, 2008
I need a formula which can count the number of items which have been applied to a suspense account in between a set of dates so for example i may need all items placed into a suspense account in the month of october. i record all items in a list so i do have a list of dates i just need to know how to count them and keep a running total as i need to feed it into another spreadsheet. I will also need to sum these the format of the spreadsheet is below -
******** ******************** src="http://www.interq.or.jp/sun/puremis/...<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - Book4___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2K2B3K3B4K4B5K5B6K6B7K7B8K8=ABCDEFGHIJKL1MOPO/S Date AppliedValue DateRemovedValue AgeCompany Name2Cheque 01-Nov-08(15.55) 01-Nov-08 15.55 0abc3Direct Receipt 01-Nov-08(109.00) 01-Nov-08 109.00 0def4Cheque 02-Nov-08(50.30) 02-Nov-08 50.30 0ghi5Cheque 03-Nov-08(23.48) 03-Nov-08 23.48 0jkl6Direct Receipt 03-Nov-08(2,236.46) 03-Nov-08 2236.46 0mno7Cheque 03-Nov-08(212.11) 03-Nov-08 212.11 0pqr8BACS/CHAPS 04-Nov-08(58.75) 04-Nov-08 58.75 0stuSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
May 8, 2009
{=SUM((C2:C8="black")*(B2:B8="sneakers"))}
I’ve got following formula counting different kind shoes that are black and sneakers and above formula work very well. What I wonder is if you can convert above formula to count these items in a filtered list. Could you use the subtotal-function in any way?
View 9 Replies
View Related
Sep 8, 2006
In column A, it contains the monthly salary data of each employee, then in column B, it contains the classification level data, Like Grade A, Grade B and Grade C. In this exercise, it want to find out the statistic as follows:
1. How many employee's monthly salary is below 10K and their classification level is Grade A, B or C
2. How many employee's monthly salary is above 10K and their classification level is Grade A, B or C
So any formulars can do that in instead of using the sorting method?
View 3 Replies
View Related
May 7, 2007
I 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: ...
View 9 Replies
View Related
Jan 19, 2014
1. I use this formula to count a number of items in a column per month: =SUMPRODUCT(--(MONTH(B6:B500)=8))
I also have another column C where I put some descriptors against the values in column B. I need a formula to calculate a number of items in Column B, except for those items, which are equal to XYZ in column C.
2. I also calculate a number of items, which were completed in a given month. Completion is marked with "Y" in a separate column (D). I use this formula: =SUMPRODUCT((YEAR(B6:B999)=2013)*(MONTH(B6:B999)=12)*(K6:K999<>"Y")). I can't make it count a number of completed items in January. C
View 8 Replies
View Related
Apr 2, 2012
I need to be able to count items if they match a condition in another column, as below
A B
1 Apple Red
2 Apple Green
3 Apple Red
4 Pear Green
5 Apple Red
6 Pear Green
7 Pear Red
8 Apple Red
How can I count how many Red Apples there are. The answer should be 4 but when I use countifs I can either count the no of apples in Col. A or the no of colour Red in Col. B.
I have tried every combination of if, vlookup, and count that I can but keep getting errors or single col. counting.
View 3 Replies
View Related
Jan 8, 2009
How 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
View 9 Replies
View Related
Jun 15, 2009
I 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 Related
Jun 16, 2009
Counting unique items in column (numbers)
View 9 Replies
View Related
Jan 22, 2009
Can 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.)
View 14 Replies
View Related
Feb 19, 2014
I 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
View 11 Replies
View Related
Sep 25, 2013
I 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] .....
View 4 Replies
View Related
Nov 29, 2005
I'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?"
View 10 Replies
View Related
Sep 13, 2012
I 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]....
View 7 Replies
View Related
Oct 30, 2012
I'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]....
View 3 Replies
View Related
Dec 19, 2006
I 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 Related
Dec 7, 2007
Looking 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.
View 9 Replies
View Related
Nov 28, 2008
I 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)))
View 9 Replies
View Related
Aug 30, 2006
I 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.
View 2 Replies
View Related
