Detect Presence of a Value From Another Worksheet

C

censo

Board Regular
Joined
Dec 25, 2015
Messages
161
Office Version
  1. 2013
Platform
  1. Windows
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:

VBA Code: 
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
ABC
1SKUMIN QTYACTIVE CHECK
2TEST-12345-QTY42 
3TEST-12345-QTY22 
4TEST-12345-QTY32 
5TEST-12345-QTY3-0012XXX
SKU
Cell Formulas
RangeFormula
C2:C5C2=IF(A2=IFERROR(VLOOKUP(A2,ACTIVE!G:G,1,0),""),"","XXX")


Book2
ABCDEFG
1local_idvendor_urlvendor_variantvendor_stockvendor_pricevendor_shippingreference
21.87E+08https://www.sample.com19.980TEST-12345-QTY4
31.87E+08https://www.sample.com120.883.98TEST-12345-QTY2
41.87E+08https://www.sample.com110.545TEST-12345-QTY3
ACTIVE


Thanks in advance.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Try this macro. Change the sheet names to suit your needs.
VBA Code: 
Sub CompareData()
    Application.ScreenUpdating = False
    Dim i As Long, dic As Object, WS1 As Worksheet, WS2 As Worksheet, v1 As Variant, v2 As Variant
    Set WS1 = Sheets("SKU")
    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), Nothing
        End If
    Next i
    For i = LBound(v1) To UBound(v1)
        If Not dic.exists(v1(i, 1)) Then
            WS1.Range("G" & i + 1) = "XXX"
        End If
    Next i
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
mumps said:
Try this macro. Change the sheet names to suit your needs.
VBA Code: 
Sub CompareData()
    Application.ScreenUpdating = False
    Dim i As Long, dic As Object, WS1 As Worksheet, WS2 As Worksheet, v1 As Variant, v2 As Variant
    Set WS1 = Sheets("SKU")
    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), Nothing
        End If
    Next i
    For i = LBound(v1) To UBound(v1)
        If Not dic.exists(v1(i, 1)) Then
            WS1.Range("G" & i + 1) = "XXX"
        End If
    Next i
    Application.ScreenUpdating = True
End Sub
Click to expand...
Thanks for the prompt reply!

The code resulted in outputting the "XXX" way out in Column G of WS1, so I just changed the code to column C and that did the trick.
VBA Code: 
WS1.Range("C" & i + 1) = "XXX"

So, I got the results I want; however, the results did not sort. What would need to be included to achieve a descending sort on column C of WS1 so that the instances of "XXX" appear towards the top of the list?
 
Upvote 0
Try:
VBA Code: 
Sub CompareData()
    Application.ScreenUpdating = False
    Dim i As Long, dic As Object, WS1 As Worksheet, WS2 As Worksheet, v1 As Variant, v2 As Variant
    Set WS1 = Sheets("SKU")
    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), Nothing
        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"
        End If
    Next i
    Cells(1, 1).Sort Key1:=Columns(3), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
mumps said:
Try:
Code: 
Cells(1, 1).Sort Key1:=Columns(3), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
Click to expand...
Yes, that did it!

Final question pertaining to this topic; I have additional columns that I want to compare on a separate worksheet using the same strategy (that we'll refer to as WS1 because it would be executed as a separate macro) that will check for values on WS2 in a similar fashion as you presented above.

(If this requires a separate New Post I'll be happy to create one)

The question is: how do I insert the additional columns to check for what I'm looking for?

APPROACH
I'll re-purpose the code above for Column C because it does the exact same check
Column D (WS1 - "Manual_Ship") looks for an exact match in column F (WS2 - "ACTIVE"). Return a blank value if Column C already shows no match "XXX" for that line item)
Column E performs a logic test such as
Excel Formula: 
=IF(C2="XXX","",B2>D2)
and returns either TRUE or FALSE

Book2
ABCDE
1SKUSHIPPING FEEACTIVE CHECKSKUG SHIPSHIP COMPARE
2TEST-12345-QTY410 9.98TRUE
3TEST-12345-QTY25 3.98TRUE
4TEST-12345-QTY36 5TRUE
5TEST-12345-QTY3-0012XXX 
MANUAL_SHIP
Cell Formulas
RangeFormula
C2:C5C2=IF(A2=IFERROR(VLOOKUP(A2,ACTIVE!G:G,1,0),""),"","XXX")
E2:E5E2=IF(C2="XXX","",B2>D2)


Book2
ABCDEFG
1local_idvendor_urlvendor_variantvendor_stockvendor_pricevendor_shippingreference
21.87E+08https://www.sample.com19.980TEST-12345-QTY4
31.87E+08https://www.sample.com120.883.98TEST-12345-QTY2
41.87E+08https://www.sample.com110.545TEST-12345-QTY3
ACTIVE
 
Upvote 0
Try these two versions:
VBA Code: 
Sub CompareData()
    Application.ScreenUpdating = False
    Dim i As Long, dic As Object, WS1 As Worksheet, WS2 As Worksheet, v1 As Variant, v2 As Variant
    Set WS1 = Sheets("SKU")
    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), Nothing
        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"
        End If
    Next i
    WS1.Cells(1, 1).Sort Key1:=Columns(3), Order1:=xlDescending, Orientation:=xlTopToBottom, Header:=xlYes
    Application.ScreenUpdating = True
End Sub

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
    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), Nothing
        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"
        End If
    Next i
    WS1.Cells(1, 1).Sort Key1:=Columns(3), Order1:=xlDescending, Orientation:=xlTopToBottom, Header:=xlYes
    With WS1
        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
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
mumps said:
Try these two versions:
VBA Code: 
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
    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), Nothing
        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"
        End If
    Next i
    WS1.Cells(1, 1).Sort Key1:=Columns(3), Order1:=xlDescending, Orientation:=xlTopToBottom, Header:=xlYes
    With WS1
        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
    End With
    Application.ScreenUpdating = True
End Sub
Click to expand...
The first macro works perfectly (a separate worksheet)

I ran the second macro but Column D did not return any values. The purpose of this macro is not only to determine if a SKU still exists but if it does, return the documented shipping from Column F of WS2 and then Column E compares the two values.

Btw - column E did populate successfully

Taking a stab here, shouldn't there be something to this effect in the code?

VBA Code: 
v3 As Variant
...
v3 = WS2.Range("F2", WS2.Range("F" & Rows.Count).End(xlUp)).Value

If so, what would the entire code look like then?
 
Upvote 0
Try:
VBA Code: 
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
    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), Nothing
        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
            WS1.Range("D" & i + 1) = WS2.Range("F" & i)
        End If
    Next i
    WS1.Cells(1, 1).Sort Key1:=Columns(3), Order1:=xlDescending, Orientation:=xlTopToBottom, Header:=xlYes
    With WS1
        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
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Most excellent, I want to thank you for your continued assistance. That generated the result I was looking for but two final modifications will make this request complete.

  1. The values generated in column D are single digit numbers. How can we get the results to be in "0.00" format?
  2. And lastly, is it possible to have a 2nd level sort on column E so that all the TRUE values appear on top? (remembering to keep Column C sort in place on the first level)
 
Upvote 0
Try:
VBA Code: 
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
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

I
  • Question Question
Check if cell value is present in another worksheet, True False, conditional format, anything!
Replies
3
Views
609
kevin9999
K
F
  • Question Question
VBA: Autofilter containing specific cell value from another Worksheet
Replies
1
Views
538
Fanalis
F
O
  • Question Question
Vlookup() and Index(Match()) returning N/A, even though the lookup value exists in the range and is identical to that value within the range.
Replies
6
Views
2K
nemmi69
nemmi69
J
  • Question Question
Using index and match from another source excel file
2
Replies
10
Views
666
Peter_SSs
Peter_SSs
S
  • Question Question
Vlookuping matching values with hidden rounding errors
Replies
5
Views
507
awoohaw
awoohaw

Forum statistics

Threads
1,223,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top