ok, try this. i assumed that the sheetnames were sheet1 and sheet2. that the
names start in a2 and the scores in b2 on sheet1. there are column headings
on sheet2, name and score. i assumed i could use c1 on sheet1 for a subtotal
formula, move it if you have something in c1 and change the references to
c1.
watch for word-wrap in the post, there are no line breaks in the code
Sub consolidate()
Dim enames As Collection
Dim lastrow As Long
Dim j As Long, i As Long
Dim c As Range
Dim empStr As String
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim escore As Double
i = 2
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
ws.Range("C1").Formula = "=subtotal(109,b2:b" & lastrow & ")"
Set enames = New Collection
For Each c In ws.Range("A2:A" & lastrow)
On Error Resume Next
empStr = Trim(c.Value)
enames.Add empStr, CStr(empStr)
On Error GoTo 0
Next
For j = 1 To enames.Count
With ws.Range("A1:B" & lastrow)
.AutoFilter Field:=1, Criteria1:=enames(j),
Operator:=xlAnd
End With
Debug.Print enames(j)
escore = ws.Range("C1").Value
ws2.Range("A" & i) = enames(j)
ws2.Range("B" & i) = ws.Range("C1").Value
i = i + 1
Next
ws.AutoFilterMode = False
End Sub
--
Gary
"Boss" wrote in message
...
EMP name Score
Leo Brown 9
Leo Brown 9
Leo Brown 9
Leo Brown 9
Leo Brown 9
Leo Brown 9
Leo Brown 10
Andrea Jones 10
Paul Adams 10
Andrea Jones 10
Andrea Jones 10
Andrea Jones 10
Andrea Jones 13
Leo Brown 13
Leo Brown 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 18
Bob Hudson 18
Andrea Jones 20
Andrea Jones 20
Andrea Jones 21
Leo Brown 21
Leo Brown 21
Leo Brown 21
Leo Brown 23
Leo Brown 43
Leo Brown 22
Leo Brown 12
It continues till 70 emp with more than 250 records. I need the total score
of each emp in other sheet.
It is a huge project on which i am working, entire coding is done i am stuck
up in this.. please help..
Thanks!
Boss
"Gary Keramidas" wrote:
give an example of the data so we can help.
"Boss" wrote in message
...
Thanks for the reply but as i said i caanot use sumproduct because i have
many criteria to sum for.
i have database of 250 rows where scores of 70 emp is filled. I have to
pull
the total score of each employee in a different sheet.
i need to find the total score of each emp..
Hope thsi makes issue clear.. please help..
Thanks a lot..
Boss
"Gary Keramidas" wrote:
with data in a1:b8
excel formulas
=SUMPRODUCT((A1:A8="yes")*(B1:B8))
=SUMIF(A1:A8,"=yes",B1:B8)
in vb, if you just want the values
range("e3").Value = application.Evaluate("SUMPRODUCT((A1:A8=" & """yes"""
&
")*(B1:B8))")
range("f3").Value = application.Evaluate("SUMIF(A1:A8," & """=yes""" &
",B1:B8)")
--
Gary
"Boss" wrote in message
...
I have foll data
criteria score Unique answer
yes 4 yes 20
yes 6 no 116
yes 6
yes 4
no 45
no 57
no 6
no 8
I need is sum of all the score for each criteria
Yes would give 20
No would give 116
I tried the foll...
Range("b3").Value = Application.WorksheetFunction.SumIf(Range("a:a"),
Range("c2"), Range("b:b"))
Problem is the criteria "c2", i wish to change using a loop. i cannot
use
sumproduct because the criteria are more in number close to 50.
Thsi something very imp for me and i am badly stuck.. please help..
Thanks!
Boss