match two column in sheet 1 v shee 2 and find the result column

[smlim7]

Hi all,

I have two identical column in sheet 1 & sheet 2, I intend to compare both column and find the result of the value into sheet 1. Using vlookup can only search for one column. I look thru other site on net, it said needs to use both index & match function. But I am bit confuse and find no where how to go about. Please help me.

my data in sheet 1
Partno________JOB_______Qty
PART1________JOBA______1
PART2________JOBB______22
PART1________JOBC______5

compare to data in sheet 2
Partno________JOB_______Qty
PART1________JOBA______5
PART2________JOBK______11
PART1________JOBC______5
PART2________JOBM______4

I intend to compare both sheet and take the result of qty column in sheet 2 to sheet 1.

Thanks.

Lim

[ron2k_1]

You can use SUMPRODUCT. Assuming that Partno is on Col A and Job and Qty on Col B and C resp. Type the following on D2 of Sheet1:

=SUMPRODUCT(--(Sheet2!$A$2:$A$100=A2),--(Sheet2!$B$2:$B$100=B2),(Sheet2!$C$2:$C$100))

[stanleydgromjr]

smlim7,

I assume that your title Partno on both worksheets is in cell A1.

The macro uses columns D in both worksheets as a helper column.

Detach/open workbook UpdateW1 if Partno_JOB found with w2 Qty - smlim7 - EF781452 - SDG12.xls and run the UpdateW1 macro.



If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub UpdateW1()
' stanleydgromjr, 06/23/2011
' http://www.excelforum.com/excel-general/781452-match-two-column-in-sheet-1-v-shee-2-and-find-the-result-column.html
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
With w1.Range("D2:D" & w1.Cells(Rows.Count, 1).End(xlUp).Row)
  .ClearContents
  .FormulaR1C1 = "=RC[-3]&RC[-2]"
End With
With w2.Range("D2:D" & w2.Cells(Rows.Count, 1).End(xlUp).Row)
  .ClearContents
  .FormulaR1C1 = "=RC[-3]&RC[-2]"
End With
For Each c In w1.Range("D2", w1.Range("D" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w2.Columns(4), 0)
  On Error GoTo 0
  If FR <> 0 Then
    c.Offset(, -1).Value = w2.Range("C" & FR).Value
  End If
Next c
w1.Range("D2:D" & w1.Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
w2.Range("D2:D" & w2.Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
Application.ScreenUpdating = True
End Sub

Then run the UpdateW1 macro.