Detect Presence of a Value From Another Worksheet

[censo]

Greetings,

I would like the ability to check to see if the values I have located in Column A are present (detected) on another worksheet within the same workbook.

For clarification, I feel as though I've come up with a solution but I'd like feedback if the method I've used is the most efficient way for Excel to return the result I'm looking to achieve or if there is a better alternate method.

STEPS TAKEN:

I attempted an INDEX & MATCH solution as I understand it's a more efficient search method in Excel but was unsuccessful in getting it to work (due to my limited knowledge). Therefore, I created a VLOOKUP function and pointed the array to the exact column the list of values exist and that worked fine (because the values I'm searching for isn't in the left most column in the array).

INTENDED RESULTS:

When an exact match is found, return nothing ("")
When no match is found, return "XXX"

The code I've come up with thus far is:

Sub D_ActiveChecks()

With Worksheets("MIN_QTY").Range("B2").CurrentRegion.Columns("B")
    '=IF(A2=IFERROR(VLOOKUP(A2,ACTIVE!G:G,1,0),""),"","XXX")

    .Offset(1, 1).Resize(.Rows.Count - 1, 1).FormulaR1C1 = _
        "=IF(RC[-2]=IFERROR(VLOOKUP(RC[-2],ACTIVE!C[4],1,0),""""),"""",""XXX"")"
       .Value = .Value
End With
End Sub

FORUM POST OBJECTIVES

1. Determine if the code above is the best solution
2. Have the results sorted so that all the "XXX" entries appear at the top of the list
3. The output from the VBA function remains in Formula form, I would prefer the results be in static (PASTE VALUES) format

Book2
	A	B	C
1	SKU	MIN QTY	ACTIVE CHECK
2	TEST-12345-QTY4	2
3	TEST-12345-QTY2	2
4	TEST-12345-QTY3	2
5	TEST-12345-QTY3-001	2	XXX
SKU

Cell Formulas
Range	Formula
C2:C5	C2	=IF(A2=IFERROR(VLOOKUP(A2,ACTIVE!G:G,1,0),""),"","XXX")

Book2
	A	B	C	D	E	F	G
1	local_id	vendor_url	vendor_variant	vendor_stock	vendor_price	vendor_shipping	reference
2	1.87E+08	https://www.sample.com		1	9.98	0	TEST-12345-QTY4
3	1.87E+08	https://www.sample.com		1	20.88	3.98	TEST-12345-QTY2
4	1.87E+08	https://www.sample.com		1	10.54	5	TEST-12345-QTY3
ACTIVE

Thanks in advance.

 

[censo]

Try:

Sub CompareData2()
    Application.ScreenUpdating = False
    Dim i As Long, dic As Object, WS1 As Worksheet, WS2 As Worksheet, v1 As Variant, v2 As Variant, LastRow As Long, fnd As Range
    Set WS1 = Sheets("Manual_Ship")
    Set WS2 = Sheets("ACTIVE")
    v1 = WS1.Range("A2", WS1.Range("A" & Rows.Count).End(xlUp)).Value
    v2 = WS2.Range("G2", WS2.Range("G" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v2) To UBound(v2)
        If Not dic.exists(v2(i, 1)) Then
            dic.Add v2(i, 1), i + 1
        End If
    Next i
    For i = LBound(v1) To UBound(v1)
        If Not dic.exists(v1(i, 1)) Then
            WS1.Range("C" & i + 1) = "XXX"
        Else
            Set fnd = WS2.Range("G:G").Find(v1(i, 1), LookIn:=xlValues, lookat:=xlWhole)
            WS1.Range("D" & i + 1) = WS2.Range("F" & fnd.Row)
        End If
    Next i
    With WS1
        .Cells(1, 1).Sort Key1:=Columns(3), Order1:=xlDescending, Orientation:=xlTopToBottom, Header:=xlYes
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("E2:E" & LastRow).Formula = "=IF(C2=""XXX"","""",B2>D2)"
        .Range("E2:E" & LastRow).Value = .Range("E2:E" & LastRow).Value
        .Range("D2:D" & LastRow).NumberFormat = "0.00"
        .Cells(1, 1).Sort Key1:=Columns(5), Order1:=xlDescending, Orientation:=xlTopToBottom, Header:=xlYes
    End With
    Application.ScreenUpdating = True
End Sub

Perfect results. Thanks again for everything!

 

[mumps]

My pleasure.