Solved: Flag based on Criteria

[francis]

If I want to expand the macro provided to include additonal criteria. For example, if I need to sum with an additional criteria and only meeting these 2 criteria, then a "Y" or "N' be input in column C

Let say, Names in Column A such as ABC 01, ABCD 001, XY 0001 and so on....the names can be varies in length, but we can identify by the group
name, like ABC xxx, XY xxx ...etc

In Column D, I have Code like "US", "CA", "SG"...etc for the corresponding rows and in column B, I have amount for related to each names.

If the total sum of the amount in a particular group and code, eg. all the names under ABC and code under "US", is less than 100, put a "N" in Column C of the corresponding rows, otherwise put a "Y" if the amount is greater than 100.

The macro below put a "N" under Name group if the sum is less than
100 and "Y" if greater than 100. How to adjust the macro to include 2 criterias mentioned.


SUb FlagCriteria()
Dim rCells As Range, rCell As Range, vUni As Variant, c, nCell As String
Set rCells = Range("A2", Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
For Each rCell In rCells
nCell = Split(rCell, " ")(0)

If nCell <> "" Then
If Not .Exists(nCell) Then
.Add nCell, ""
c = c + 1
End If
End If
Next rCell
vUni = .keys
End With

Dim oDex As Integer, SumCell As Double
Dim p As Integer, oAd(), Ans As Integer, oRes As String

For oDex = 0 To UBound(vUni)
For Each rCell In rCells
nCell = Split(rCell, " ")(0)

If nCell = vUni(oDex) Then
SumCell = SumCell + rCell.Offset(, 1)
p = p + 1
ReDim Preserve oAd(p)
oAd(p) = rCell.Offset(, 2).Address
End If
Next rCell

If SumCell > 100 Then
oRes = "Y"
Else
oRes = "N"
End If
For Ans = 1 To UBound(oAd())
Range(oAd(Ans)) = oRes
Next Ans
SumCell = 0
p = 0
Next
End Sub

thanks

regards, xlsops

[Bob Phillips]

How do you know that ABC and ABCD are in the same group? Does AB count as well? If so, what is the rule? If not, what is the rule?

[francis]

The rule is that there is always a space to differentiate the group, such as ABC 001 or ABCD 01 or AB 0100.

thanks for looking into this.

regards, xlsops

[Bob Phillips]

So ABCD 001 is not in the same group as ABC 001?

[francis]

right, they are of different group

[Bob Phillips]

How about thius



Sub FlagCriteria()
Dim rCells As Range, rCell As Range, vUni As Variant, c, nCell As String
Set rCells = Range("A2", Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
For Each rCell In rCells
nCell = Split(rCell, " ")(0)

If nCell <> "" Then
If Not .Exists(nCell & ":" & rCell.Offset(0, 3)) Then
.Add nCell & ":" & rCell.Offset(0, 3), ""
c = c + 1
End If
End If
Next rCell
vUni = .keys
End With

Dim oDex As Integer, SumCell As Double
Dim p As Integer, oAd(), Ans As Integer, oRes As String

For oDex = 0 To UBound(vUni)
For Each rCell In rCells
nCell = Split(rCell, " ")(0)

If nCell = Split(vUni(oDex), ":")(0) And _
rCell.Offset(0, 3).Value = Split(vUni(oDex), ":")(1) Then
SumCell = SumCell + rCell.Offset(, 1)
p = p + 1
ReDim Preserve oAd(p)
oAd(p) = rCell.Offset(, 2).Address
End If
Next rCell

If SumCell > 100 Then
oRes = "Y"
Else
oRes = "N"
End If
For Ans = 1 To UBound(oAd())
Range(oAd(Ans)) = oRes
Next Ans
SumCell = 0
p = 0
Next
End Sub

[francis]

Hi Xld

Excellent! Thanks. Actually what have been change form my original code??

PS : I still own you something

cheers, xlsops

[Bob Phillips]

I add the value of cell D to the dictionary object in the first loop, and then in the second loop I tested the 2 components against A and D values.

[francis]

Hi xld

Thank for the explantion. I was premature

If I have more criteria how do I add it into the macro.
Let say I need to first group "Product" in column E, then "Names" in column A, and then "Code" in column D, then sum the "Amount" in column B.
If the total sum of the amount in a particular product, and names and code, eg. all the products under XYZ and names under ABC and code under "US", is less than 100, put a "N" in Column C of the corresponding rows, otherwise put a "Y" if the amount is greater than 100.

I have been looking at your explaination and the codes you provided for a long time but can't figure out how to add an additional criteria.

I would say this macro is beyond me.

TIA

regards, xlsops

[francis]

Hi

Pls assist.

Thanks, xlsops