Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Chart - 2 column stack /w 1 comparison column | Charts and Charting in Excel | |||
sumif based on cell comparison in excel | Excel Worksheet Functions | |||
Why does a sumif use double speech marks | Excel Discussion (Misc queries) | |||
Sumif - double columns | Excel Worksheet Functions | |||
Sumif - For Each problem (several column comparison) | Excel Programming |