CODEWITHSUNDEEP
vbaexcel
honey
honey

Reputation: 199

Not able to calculate of count based on two conditions in VBA?

I am trying to calculate the count of some data occerence based on two conditions by using VBA in Excel. Below is the scenario that i am trying to implement :

There is one Excel Sheet say SHEET1 having data as mentioned below.

    |Contract No| Ledger No|
    |   Con1    |   Led1   |
    |   Con1    |   Led2   |
    |   Con1    |   Led3   |
    |   Con2    |   Led4   |
    |   Con2    |   Led2   |
    |   Con3    |   Led5   |
    |   Con3    |   Led6   |
    |   Con3    |   Led3   |

There is another Excel Sheet say SHEET2 having base data correspond to Ledger numbers.

    | Ledger No| ProductBaught
    |   Led1   | P1
    |   Led1   | P2
    |   Led1   | P3
    |   Led2   | P4
    |   Led2   | P3
    |   Led3   | P2
    |   Led3   | P1
    |   Led3   | P4
    |   Led4   | P2
    |   Led4   | P3
    |   Led5   | P1
    |   Led6   | P4

I am looking for the below calculation (count of products based on Contract Numbers).Here Cont1=Led1(P1,P2,P3),Led2(P4,P3),Led3 (P2,P1,P4) so count is P1,P2,P3,P4 means 4. Similarly applies to other contarct numbers.

    |Contract No| Count of Products Baught|
    |   Con1    |           4             |
    |   Con2    |           3             |
    |   Con3    |           3             |

I am thinking it this way.

Store Contract Number and Ledger number in a Dictionary as Key, Value pair. In form of one Key multiple values. so Dictionary would have only three keys Con1,Con2,Con3 and Ledger numbers would be corresponding values.

    Integer count
    Foreach Key in Dictionary.Keys()
          count = 0
      Foreach Value in Key.Values()
          IF(Lookup in SHEET2 = True)
             count = count + 1
          END IF
      Next Value
        First column in Result Sheet = Key
        Second column in Result Sheet = count
    Next Key

I am still not able to figure out the right way as this count would also not the count that is required because it will consider the count of P1,P2,P1 as 3 rather than 2. I have to implement this in VBA and i am not sure if the way i am thinking is correct.

Appreciate for Help.

Thanks, Honey

Upvotes: 0

Views: 73

Answers (1)

Scott Craner
Scott Craner

Reputation: 152605

You can use this UDF:

Function COUNTX(crit As String, critrng As Range, prdrng As Range) As Long
Dim critArr, prdArr
Dim i&, j&
Set dict = CreateObject("Scripting.Dictionary")

critArr = critrng.Value
prdArr = prdrng.Value

For i = LBound(critArr, 1) To UBound(critArr, 1)
    If critArr(i, 1) = crit Then
        For j = LBound(prdArr, 1) To UBound(prdArr, 1)
            If prdArr(j, 1) = critArr(i, 2) Then
                On Error Resume Next
                    dict.Add prdArr(j, 2), prdArr(j, 2)
                On Error GoTo 0
            End If
        Next j
    End If
Next i

COUNTX = dict.Count

End Function

Put this in a module attached to the workbook, NOT in the worksheet code or in ThisWorkbook code.

Then you can call it like a normal formula like this:

=COUNTX(A1,Sheet1!$A$2:$B$9,Sheet2!$A$2:$B$13)

enter image description here

The three criteria are:

  1. The string of the Contract number.
  2. The Range on Sheet one, Both columns. It is this range that the UDF will search the first column for the contract and use the second column for the Ledger.
  3. The Range on Sheet two, Both columns. It will search the first column and use the second column for product.

Upvotes: 1

Related Questions