PRAVEEN T
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
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
Why do you need VBA? Attach a small example file.
ASKER
SMALL CORRECTION..
FINAL RESULT ON SHEET ONE
FINAL RESULT LIKE
ITEM_NAME QTY RESULT
A 22 NO
B 60 YES
C 55 YES
FINAL RESULT ON SHEET ONE
FINAL RESULT LIKE
ITEM_NAME QTY RESULT
A 22 NO
B 60 YES
C 55 YES
ASKER
Attached excel book. I need it to use in vba.. I have more data in the original sheet.
TEST_5.xlsx
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
Please refer to the attached.
TEST_5.xlsm
TEST_5.xlsm
ASKER
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")
Check attached....
Compare-with-VLookup.xlsx
Compare-with-VLookup.xlsx
Try this...
On Sheet1
In C2
On Sheet1
In C2
=IF(B2=INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)),"Yes","No")
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...
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(B4=INDEX(Sheet2!B:B,MATCH(A4,Sheet2!A:A,0)),"Yes","No"),"No")
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!")
ASKER
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
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:
Compare-with-VLookup_v2.xlsx
=IF(SUMIF(A:A,A2,B:B)=VLOOKUP(A2,Sheet2!A:B,2,0),"Yes","No")
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
Below is formula version:=IFERROR(IF(SUMIF($A:$A,$A2,$B:$B)=VLOOKUP($A2,Sheet2!$A:$B,2,0),"Yes","No"),"Not Found")
Compare-with-VLookup_v3.xlsm
ASKER
Thank you. Let me try it and update you.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Its working .. Thank you.
@ Praveen
You taught me a good lesson. Lol
Don't worry, I will ignore all your future questions. :)
You taught me a good lesson. Lol
Don't worry, I will ignore all your future questions. :)