Sumif - double column comparison

[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).

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

 

[Harald Staff]

HJi Mats

You seem to be reinventing the Pivot table. See
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

HTH. Best wishes Harald

 

[Mats W]

Thanks Hubert,

But this is not really the answer to my problem, the row
in the result sheet is predifined (not neccearly all
combinations). This also only part in a much bigger
operation, there a pivot table do not fit.
rgds,
Mats W

 

[Harald Staff]

Hi again Mike

Ok. The trick is to concatenate the twin cells, either in a spare column
=A1&B1
or in code, and do the a search or a loop:

Sub test()
Dim sFind As String
Dim sTest As String
Dim L As Long
sFind = Sheets(1).Cells(1, 1).Text & Sheets(1).Cells(1, 2).Text
With Sheets("Database")
For L = 1 To 1000
sTest = .Cells(L, 1).Text & .Cells(L, 2).Text
If sFind = sTest Then
MsgBox "Row " & L & " matches"
End If
Next
End With
End Sub

HTH. best wishes Harald

 

[Mats W]

Hi Harald,

I already did the combined column in the sheet solution.
Thanks for the code solution which was whar I was after!

Rgds,
mats

 

[Harald Staff]

Hi Harald,

I already did the combined column in the sheet solution.
Thanks for the code solution which was whar I was after!

Glad to hear that. Thanks for the feedback.

Best wishes Harald