M
Mats W
Hi,
I've got a problem. I need to compare two columns in one
sheet(1) with two columns in another sheet(2). If the
cells in several rows (column A&B) in one sheet(1) exactly
match a row in the other sheet(2) (column A&B), I like the
values in column C to be summarized in the second sheet(2).
(This is a part of a bigger problem and a PIVOT solution
is not applicable)
Sheet 1 (data) Sheet2 (result)
ColA ColB ColC ColA ColB ColC
Row1 AA BA 15 Row1 AA BA 25
Row2 AA BA 10 Row2 AB BA 5
Row3 AB BA 5
I'll tried a For each - sumif script, but did not succeed.
Se below:
Sub I_UpdateHours()
Dim rng As Range, rng1 As Range, cell As Range
Dim res As Variant
With Worksheets("Works")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 2).End
(xlUp))
End With
With Worksheets("Database")
Set rng1 = .Range(.Cells(2, 33), .Cells(Rows.Count,
34).End(xlUp))
End With
For Each cell In rng
If Application.SumIf(rng1, cell.Value, rng1.Offset(0,
1)) = 0 Then
Else
cell.Offset(0, 2).Value = _
Application.SumIf(rng1, cell.Value, rng1.Offset(0, -
25))
End If
Next
End Sub
Appreciate any help to get this to work!
Thanks!
Mats W
I've got a problem. I need to compare two columns in one
sheet(1) with two columns in another sheet(2). If the
cells in several rows (column A&B) in one sheet(1) exactly
match a row in the other sheet(2) (column A&B), I like the
values in column C to be summarized in the second sheet(2).
(This is a part of a bigger problem and a PIVOT solution
is not applicable)
Sheet 1 (data) Sheet2 (result)
ColA ColB ColC ColA ColB ColC
Row1 AA BA 15 Row1 AA BA 25
Row2 AA BA 10 Row2 AB BA 5
Row3 AB BA 5
I'll tried a For each - sumif script, but did not succeed.
Se below:
Sub I_UpdateHours()
Dim rng As Range, rng1 As Range, cell As Range
Dim res As Variant
With Worksheets("Works")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 2).End
(xlUp))
End With
With Worksheets("Database")
Set rng1 = .Range(.Cells(2, 33), .Cells(Rows.Count,
34).End(xlUp))
End With
For Each cell In rng
If Application.SumIf(rng1, cell.Value, rng1.Offset(0,
1)) = 0 Then
Else
cell.Offset(0, 2).Value = _
Application.SumIf(rng1, cell.Value, rng1.Offset(0, -
25))
End If
Next
End Sub
Appreciate any help to get this to work!
Thanks!
Mats W