Multiple criteria and lists for one result

**gyj** · 06-14-2012, 03:45 PM

HELP! I can't seem to get this to work.

I need to be able to determine how many points each Sales rep earn for selling eligible products. Of course it's not just that simple. The requirements and rules are as follows: sell any SKU form list A, AND sell any SKU from list B, with the same PO#, receive points.

1. A PO must contain at least 2 eligible SKUs: 1 from eligible SKUs list "A" AND 1 from eligilble SKUs list B (different SKUs in each list)
2. BOTH of the eligible SKUs from the lists must have the same PO# (i.e. they must be made in the same sale)
3. If criteria 1 and 2 are met, sales rep receive points.
4. Points can be tricky also, please see example lists attached.

Please see the 3 different types of lists I have to work with to accomplish this.Attachment 162061

Columns A,B,C = Sales Data
Columns E, F = Eligible SKUs
Columns H, I, J = Points

**rylo** · 06-14-2012, 08:20 PM

Hi

About the only way I could get this to function is with a UDF.

Create a module in the example workbook, and add the code

Function myfunc(po, po_rng, lista_rng, listb_rng)
  Application.Volatile
  myfunc = 0
  
  For Each ce In po_rng
    If ce = po Then
      Set findit = lista_rng.Find(what:=ce.Offset(0, -1))
      If Not findit Is Nothing Then
        lista = True
        listasku = ce.Offset(0, -1)
      End If
    End If
    If ce = po Then
      Set findit = listb_rng.Find(what:=ce.Offset(0, -1))
      If Not findit Is Nothing Then
        listb = True
        listbcnt = listbcnt + 1
        If ce.Offset(0, -1) = "SKU L" Then skulcnt = skulcnt + 1
      End If
    End If
  Next ce
  'If lista And listb Then myfunc = True
  If lista And listb Then
    Select Case listasku
      Case "SKU A"
        myfunc = 500
      Case "SKU B"
        myfunc = 1000
      Case "SKU C"
        myfunc = 2000
      Case "SKU D"
        If listbcnt = 6 And skulcnt = 0 Then
          myfunc = 4000
        ElseIf listbcnt > 0 And skulcnt = 1 Then
          myfunc = 20000
        End If
    End Select
  End If
End Function

Then go to the spreadsheet and in D2 enter:
=IF(COUNTIF($C$2:C2,C2)=1,myfunc(C2,$C$2:$C$26,$E$2:$E$5,$F$2:$F$8),"")

I've copied this down to D26 as I've put in some extra items to try to test the SKU D / SKU L combinations.

See how it goes.

rylo