Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count unique numbers multiple criteria
The numbers in this problem may contain letters as well as numbers.
I have a lot of invoice numbers that I need to count. Some are associated with a Purchase order number (in some cases multiple occurances of the same invoice number are associated with one purchase order and sometimes it is different invoices associated with a purchase order number.) First, I need to count the number of unique PO nos. for those where a PO number shows up. Then, I need to count the unique invoice numbers without a PO number. I was using the following array formula: =SUM(IF(FREQUENCY(IF(LEN(E2:E148)0,MATCH(E2:E148, E2:E148,0),""), IF (LEN(E2:E148)0,MATCH(E2:E148,E2:E148,0),""))0,1) ) But it doesn't seem to be producing the right results. Here is an example of my numbers Invoice no. PO Number 40701429 763334 40701429 40701429 40701431 768346 40701431 40701525 763334 40701525 763334 40701525 40701525 40701592 748407 40701592 40701393 5 invoice numbers 4 po's in the list above is what the answer should be in this short example. How is the best way to get accurate results with many numbers? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count unique numbers multiple criteria
From your formula, I see you are referencing to one column. Which column do you want to count? Invoice No or PO or both -- Thank You cheers, ======================== please click the Yes button if this help "allie357" wrote: The numbers in this problem may contain letters as well as numbers. I have a lot of invoice numbers that I need to count. Some are associated with a Purchase order number (in some cases multiple occurances of the same invoice number are associated with one purchase order and sometimes it is different invoices associated with a purchase order number.) First, I need to count the number of unique PO nos. for those where a PO number shows up. Then, I need to count the unique invoice numbers without a PO number. I was using the following array formula: =SUM(IF(FREQUENCY(IF(LEN(E2:E148)0,MATCH(E2:E148, E2:E148,0),""), IF (LEN(E2:E148)0,MATCH(E2:E148,E2:E148,0),""))0,1) ) But it doesn't seem to be producing the right results. Here is an example of my numbers Invoice no. PO Number 40701429 763334 40701429 40701429 40701431 768346 40701431 40701525 763334 40701525 763334 40701525 40701525 40701592 748407 40701592 40701393 5 invoice numbers 4 po's in the list above is what the answer should be in this short example. How is the best way to get accurate results with many numbers? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count unique numbers multiple criteria
On Dec 9, 8:31*am, xlm wrote:
From your formula, I see you are referencing to one column. Which column do you want to count? Invoice No or PO or both -- Thank You cheers, ======================== please click the Yes button if this help "allie357" wrote: The numbers in this problem may contain letters as well as numbers. I have a lot of invoice numbers that I need to count. Some are associated with a Purchase order number (in some cases multiple occurances of the same invoice number are associated with one purchase order and sometimes it is different invoices associated with a purchase order number.) First, I need to count the number of unique PO nos. for those where a PO number shows up. Then, I need to count the unique invoice numbers without a PO number. I was using the following array formula: =SUM(IF(FREQUENCY(IF(LEN(E2:E148)0,MATCH(E2:E148, E2:E148,0),""), IF (LEN(E2:E148)0,MATCH(E2:E148,E2:E148,0),""))0,1) ) But it doesn't seem to be producing the right results. Here is an example of my numbers Invoice no. PO Number 40701429 763334 40701429 40701429 40701431 768346 40701431 40701525 763334 40701525 763334 40701525 40701525 40701592 748407 40701592 40701393 5 invoice numbers 4 po's in the list above is what the answer should be in this short example. *How is the best way to get accurate results with many numbers? *Thanks!- Hide quoted text - - Show quoted text - Hi Allie357, To get the number of unique numbers I'd probably use some VBA logic and a dictionary object: dim rngCell as range dim dictPONumbers as Dictionary set dictPONumbers = New Dictionary 'Read every PO number and add it to dictionary if not already there with sheets(POSheet) for each rngCell in .columns(1).rows if not dictPONumbers.Exists(rngCell.value) then dictPONumbers.Add rngCell.value,vbnullstring next rngCell end with 'Display number of unique PO numbers debug.print dictPONumbers.count ** I haven't tested this code, just written it from memory** Rob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count unique numbers multiple criteria
On 8 Dec, 19:11, allie357 wrote:
The numbers in this problem may contain letters as well as numbers. I have a lot of invoice numbers that I need to count. Some are associated with a Purchase order number (in some cases multiple occurances of the same invoice number are associated with one purchase order and sometimes it is different invoices associated with a purchase order number.) First, I need to count the number of unique PO nos. for those where a PO number shows up. Then, I need to count the unique invoice numbers without a PO number. I was using the following array formula: =SUM(IF(FREQUENCY(IF(LEN(E2:E148)0,MATCH(E2:E148, E2:E148,0),""), IF (LEN(E2:E148)0,MATCH(E2:E148,E2:E148,0),""))0,1) ) But it doesn't seem to be producing the right results. Here is an example of my numbers Invoice no. PO Number 40701429 763334 40701429 40701429 40701431 768346 40701431 40701525 763334 40701525 763334 40701525 40701525 40701592 748407 40701592 40701393 5 invoice numbers 4 po's in the list above is what the answer should be in this short example. *How is the best way to get accurate results with many numbers? *Thanks! Phillip London UK This works for me using your example set of data in columns A and B in a new workbook Copy this code into a new sttanard module Dim Rng As Range Sub GetUniques() Dim UniqueInvoice As Long Dim UniquePO As Long ResetDB DoPrimaryKey ChangeDB SetResults 1 DoSort "B", 2 DoFilter UniqueInvoice = WorksheetFunction.CountA(Range("IV:IV")) - 1 Columns("IT:IV").Delete SetResults 2 ResetDB DoSort "B", 1 Rng.Columns(2).SpecialCells(xlCellTypeConstants, xlLogical).ClearContents DoFilter UniquePO = WorksheetFunction.CountA(Range("IV:IV")) - 1 Columns("IT:IV").Delete DoSort "C", 1 Columns("C:C").Delete MsgBox _ "Number of Unique Invoices : " & UniqueInvoice & vbNewLine & _ "Number of Unique Purchase Orders : " & UniquePO, _ vbOKOnly, "Results" End Sub Private Sub ResetDB() Set Rng = Range("A1:" & Range("A65536").End(xlUp).Offset(0, 1).Address) End Sub Private Sub ChangeDB() Dim KntPO As Long Dim LastRecord As Range KntPO = WorksheetFunction.CountA(Rng.Columns(2)) - 1 Rng.Columns(2).SpecialCells(xlCellTypeBlanks).Valu e = False Set LastRecord = Range("A" & Rng.Rows.Count - KntPO) Set Rng = Range("A1:" & LastRecord.Offset(0, 1).Address) End Sub Private Sub DoSort(sortkey As String, sortorder As Integer) With Worksheets("Sheet1") .Range("A1").Sort _ Key1:=.Columns(sortkey), _ Order1:=sortorder, _ Header:=xlGuess End With End Sub Private Sub DoPrimaryKey() Dim ExanpleRange As Range Dim FillRange As Range Columns("C:C").Insert Range("C1:C3").Value = Application.Transpose(Array("Record", 1, 2)) With Worksheets("Sheet1") Set ExampleRange = .Range("C2:C3") Set FillRange = .Range("C2:C" & Rng.Rows.Count) ExampleRange.AutoFill Destination:=FillRange End With End Sub Private Sub DoFilter() Rng.AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("IT1:IU2"), _ CopyToRange:=Range("IV1"), _ Unique:=True End Sub Sub SetResults(Cl As Long) Range("IT1:IU1").Value = Rng.Range(Cells(1, 1), Cells(1, 2)).Value Range("IV1").Value = Rng.Cells(1, Cl).Value End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count unique with multiple criteria | Excel Worksheet Functions | |||
Count Unique with Multiple Criteria | Excel Worksheet Functions | |||
unique record count with multiple criteria | Excel Worksheet Functions | |||
Count unique numbers in a range with a given criteria | Excel Discussion (Misc queries) | |||
Count Unique Values with Multiple Criteria | Excel Worksheet Functions |