Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif - For Each problem (several column comparison)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif - For Each problem (several column comparison)
Hi
try using Sumproduct. See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "Mats W" schrieb im Newsbeitrag ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column comparison | New Users to Excel | |||
Column Comparison and Like Rows | Excel Discussion (Misc queries) | |||
Column Comparison Trouble | Excel Worksheet Functions | |||
Excel Chart - 2 column stack /w 1 comparison column | Charts and Charting in Excel | |||
sumif based on cell comparison in excel | Excel Worksheet Functions |