Summing Group and insert value next to it

[franciz]

Hi

In column A, I have names that appear as ABC 01, ABC 02,ABC 03, I want to sum
the group ABC, XYZ or any other groups which appear in the column A and put
a value in column B depending on the sum total figure. For example, if the
total sum
for ABC is more than 100, all the entries in the column B related to ABC, ie
ABC 01, ABC 02, ABC 03.....so on, will have a Y in the respective rows,
otherwise, N will be input in the related entries.

Thanks in advance.

regards, xlsops

 

[Otto Moehrbach]

What do you mean by "I want to sum the group ABC"? What do you want to sum?
Do you want to sum all the numbers that appear after the ABC in the cells?
Or do you want to simply count the number of cells that contain ABC? HTH
Otto

 

[franciz]

Hi,

Thanks for looking into this. Let me explain once again.

Let say I have 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 B, I have amount for related to each names. If the total sum of
the amount in a particular group, eg. all the names under ABC, 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.

Hope this make sense now. Thanks

regards,xlsops

 

[Otto Moehrbach]

This macro will do what you want. I assumed that your data starts in A1.
You must place all of your group names somewhere in your file, select the
list, and name it "NamesList". Come back if you need more. HTH Otto
Sub SumGroup()
Dim i As Range, j As Range, rColA As Range
Dim TheSum As Double, YN As String
Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False
For Each i In Range("NamesList")
TheSum = 0
For Each j In rColA
If InStr(j.Value, i.Value) <> 0 Then _
TheSum = TheSum + j.Offset(, 1).Value
Next j
YN = "Y"
If TheSum <= 100 Then YN = "N"
For Each j In rColA
If InStr(j.Value, i.Value) <> 0 Then _
j.Offset(, 2).Value = YN
Next j
Next i
Application.ScreenUpdating = True
End Sub