Should I use VLOOKUP?
Closed
Allen
-
Oct 17, 2011 at 04:55 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 18, 2011 at 11:00 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 18, 2011 at 11:00 PM
2 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Oct 18, 2011 at 12:05 AM
Oct 18, 2011 at 12:05 AM
it is presumed sheet 2 is sorted according to column A and then by column B
first row in both sheets are column headings.
in sheet1 c2 copy this formula
invoke this formula with control shift enter
copy C2 down
the last row is 5 change if the data is larger. wherever 5 comes change it to the number of the last row
first row in both sheets are column headings.
in sheet1 c2 copy this formula
=INDEX(Sheet2!C2:C5,MATCH(A2&B2,Sheet2!A2:$A$5&Sheet2!B2:$B$5,3))+INDEX(Sheet2!C2:C5,MATCH(A2&B2,Sheet2!A3:$A$5&Sheet2!B3:$B$5,3))
invoke this formula with control shift enter
copy C2 down
the last row is 5 change if the data is larger. wherever 5 comes change it to the number of the last row
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Oct 18, 2011 at 11:00 PM
Oct 18, 2011 at 11:00 PM
will you be comfortable with a macro
row 1 is having column headings in both the sheets
row 1 is having column headings in both the sheets
Sub test()
Dim r1 As Range, c1 As Range, r2 As Range, filt As Range, tot As Double, x1, x2
With Worksheets("sheet1")
Set r1 = .Range(.Range("A2"), .Range("A2").End(xlDown))
For Each c1 In r1
x1 = c1.Value
x2 = c1.Offset(0, 1).Value
With Worksheets("sheet2")
Set r2 = .UsedRange
'MsgBox r2.Address
r2.AutoFilter field:=1, Criteria1:=x1
r2.SpecialCells(xlCellTypeVisible).AutoFilter field:=.Range("B1").Column, Criteria1:=x2
tot = WorksheetFunction.Sum(r2.Offset(1, 0).Resize(Rows.Count - 1, Columns.Count).SpecialCells(xlCellTypeVisible).Columns("c:c"))
'MsgBox tot
r2.AutoFilter
End With
c1.Offset(0, 2) = tot
Next c1
End With
End Sub
Sub undo()
With Worksheets("sheet1")
Range(.Range("c2"), .Cells(Rows.Count, "C").End(xlUp)).Clear
End With
End Sub
Oct 18, 2011 at 06:26 PM
Column A, Column B, Column C
3.1.01, 1.06, 155.38
3.1.01, 1.06, 0.00
3.1.01, 1.06, 0.00
3.1.01, 1.06, 740.83
3.1.01, 1.06, 0.00
3.1.01, 1.06, 0.00
3.1.01, 1.06, 0.00
3.1.01, 1.06, 0.00
3.1.01, 1.08, 0.00
3.1.01, 1.08, 23.53
3.1.01, 1.08, 108.78
3.1.01, 1.08, 0.00
3.1.01, 1.08, 0.00
3.1.01, 1.08, 0.00
3.1.01, 1.08, 0.00
3.1.01, 1.08, 0.00
As you can see there are multiple lines and some of the valves in Column C are 0. Should the formula you provided still work (as I can't seem to get it working)? Thanks again for your help.