Sumif - double column comparison
 

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

-----Original Message-----
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/assistan...articles/xlcon
PT101.aspx

HTH. Best wishes Harald


"Mats W" skrev i
melding
...
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


.

Sumif - double column comparison
 

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" skrev i melding
...
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

-----Original Message-----
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/assistan...articles/xlcon
PT101.aspx

HTH. Best wishes Harald


"Mats W" skrev i
melding
...
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


.

Sumif - double column comparison
 

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


-----Original Message-----
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" skrev i
melding
...
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

-----Original Message-----
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/assistan...articles/xlcon
PT101.aspx

HTH. Best wishes Harald


"Mats W" skrev i
melding
...
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


.



.

Sumif - double column comparison
 

"Mats W" skrev i melding
...
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