Link to home
Start Free TrialLog in
Avatar of PRAVEEN T
PRAVEEN TFlag for United States of America

asked on

NEED HELP ON EXCEL VBA

Hi Experts,

I need help on following requirement to build the excel vba formula

I have two sheets
sheet one having following columns

ITEM_NAME       QTY
A                           22
B                            60
C                           55

SHEET 2 ND HAVING FOLLOWING COLUMNS

PROD_ID         QTY
A                       12
B                       60
C                       55
D                     90

I NEED TO ADD ONE MORE COLUMN IN THE FIRST SHEET AND CHECK IF PRODUCT MATCH THEN CHECK THE QUANTITY MATCH,
IF MATCHED "YES" ELSE "NO"

FINAL RESULT LIKE

ITEM_NAME      QTY      RESULT
A                          22          NO
B                          60          YES
C                           55          NO
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Why do you need VBA? Attach a small example file.
Avatar of PRAVEEN T

ASKER

SMALL CORRECTION..

FINAL RESULT  ON SHEET ONE

FINAL RESULT LIKE

ITEM_NAME      QTY      RESULT
A                          22          NO
B                          60          YES
C                           55          YES
Attached excel book. I need it to use in vba.. I have more data in the original sheet.
TEST_5.xlsx
Please give this a try...
Sub CompareData()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim x, y, z(), dict
Dim i As Long
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
x = ws1.Range("A1").CurrentRegion.Value
y = ws2.Range("A1").CurrentRegion.Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(y, 1)
    dict.Item(y(i, 1)) = y(i, 2)
Next i

ReDim z(1 To UBound(x, 1) - 1)

For i = 2 To UBound(x, 1)
    If dict.exists(x(i, 1)) Then
        If dict.Item(x(i, 1)) = x(i, 2) Then
            z(i - 1) = "Yes"
        Else
            z(i - 1) = "No"
        End If
    End If
Next i
ws1.Columns(3).Clear
ws1.Range("C1").Value = "Result"
ws1.Range("C2").Resize(UBound(z)).Value = Application.Transpose(z)
End Sub

Open in new window

Please refer to the attached.
TEST_5.xlsm
Can we use the VLOOKUP?
Formula can do without VBA:
=IF(VLOOKUP(A2,Sheet2!A:B,2,0)=VLOOKUP(A2,Sheet1!A:B,2,0),"Yes","No")

Open in new window

Try this...
On Sheet1
In C2
=IF(B2=INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)),"Yes","No")

Open in new window

and then copy it down.
To handle the error in case if an item from Sheet1 is not found on Sheet2, you may try something like this...

=IFERROR(IF(B4=INDEX(Sheet2!B:B,MATCH(A4,Sheet2!A:A,0)),"Yes","No"),"No")

Open in new window


The last "No" will be returned if the item is not found on Sheet2, you can replace it with "Item not found" if you wish.
=IFERROR(IF(B2=INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)),"Yes","No"),"Item Not Found!")

Open in new window

Hi Guys,
Its working fine. Thanks for the help..

If in the sheet having multiple same item rows..  can we do group the item and take that final Qty check with sheet 2.
then if matched >> set "Yes" Else "NO"


sheet 1
A      5
A      5
A     10
B      11
B      44
C     20
C     20
C     20
Try below:
=IF(SUMIF(A:A,A2,B:B)=VLOOKUP(A2,Sheet2!A:B,2,0),"Yes","No")

Open in new window

Check attached...
Compare-with-VLookup_v2.xlsx
Below is VBA Version:
Sub UpdateRemarks()
Dim Ws As Worksheet
Dim LRow As Long
Set Ws = Worksheets("Sheet1")
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Ws.Range("C2:C" & LRow).FormulaR1C1 = "=IFERROR(IF(SUMIF(C1,RC1,C2)=VLOOKUP(RC1,Sheet2!C1:C2,2,0),""Yes"",""No""),""Not Found"")"
Ws.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Open in new window

Below is formula version:
=IFERROR(IF(SUMIF($A:$A,$A2,$B:$B)=VLOOKUP($A2,Sheet2!$A:$B,2,0),"Yes","No"),"Not Found")

Open in new window

Compare-with-VLookup_v3.xlsm
Thank you. Let me try it and update you.
Hi Shums,

I am not able to modify the code based on my original sheet...

My original sheet1

Item_number on  'I' column  and Qty on 'S' Column  and the result should on 'V'column

Sheet 2
Item_number on 'A' column and the Qty on 'O' ....

can you please modify based on above....

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Its working .. Thank you.
@ Praveen

You taught me a good lesson. Lol
Don't worry, I will ignore all your future questions. :)