# Detect Presence of a Value From Another Worksheet

#### censo

##### Board Regular
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

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### mumps

##### Well-known Member
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
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``````

#### censo

##### Board Regular
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
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``````

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?

#### mumps

##### Well-known Member
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
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``````

#### censo

##### Board Regular

Try:
Code:
``Cells(1, 1).Sort Key1:=Columns(3), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes``
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

#### mumps

##### Well-known Member
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
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
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``````

#### censo

##### Board Regular

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
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``````
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?

#### mumps

##### Well-known Member
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
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``````

#### censo

##### Board Regular
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)

#### mumps

##### Well-known Member
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``````

Replies
13
Views
163
Replies
4
Views
56
Replies
8
Views
155
Replies
8
Views
138
Replies
0
Views
186

1,141,204
Messages
5,704,944
Members
421,372
Latest member
Jamie11

### 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.

### Which adblocker are you using?

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

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