Excel: VBA code that look for values and compare it

[moutoncito]

Hi all,

I need some help with a VBA code. I have tried differents codes that i fund in the forum but they didn t match with what i m looking for. So i will explain what i have , what i need and what i expect.

I have a workbook with 2 sheets (Sheet1 , Sheet2). Both sheets have values in columns A to F. (A:F). Values Start at A2. B2..... etc in both sheets, and end up sometimes at A1000. B1000. ... etc.

What i want to do:
I need a macro that look for the values that are in Sheet 1 and NOT in Sheet 2.
And also the macro will look for the values that are in Sheet2 and NOY in Sheet1.

What i need:
Is that the result appears in a new workbook ( or in Sheet3), and that the result appears in columns A and B. In column Ait will display the values that are in Sheet1 and NOT in Sheet2. And in Column B the values that are in Sheet2 and NOT in Sheet 1.

So if anyone can help me it would be great because i really don t know how to do this.

Thanks

if anyone has a different idea of displaying results i m open to your advices.

[ADezii]

Here is the basic Logic that you would need to compare every Value in Sheet1 within the Range $A$2:$F$1000 to every Value in Sheet2 for the same Range. Keep in mind that this is a very CPU intensive process since it involves approximately 36,000,000 iterations of the Nested Loops (approximately 2 minutes).

Dim rng_1 As Range
Dim rng_2 As Range
Dim rngRef_1 As Range
Dim rngRef_2 As Range
 
 
Set rng_1 = Worksheets("Sheet1").Range("$A$2:$F$1000")
Set rng_2 = Worksheets("Sheet2").Range("$A$2:$F$1000")
 
For Each rngRef_1 In rng_1
  For Each rngRef_2 In rng_2
    If rngRef_1.Value <> "" Then
      If rngRef_1.Value = rngRef_2.Value Then
        Debug.Print "Value " & rngRef_1.Value & " in Sheet1 found at " & _
                     rngRef_2.Address & " in Sheet2"
      End If
    End If
  Next
Next

Sample OUTPUT:

Value 66 in Sheet1 found at $D$31 in Sheet2
Value 77 in Sheet1 found at $C$16 in Sheet2
Value 9998887 in Sheet1 found at $F$100 in Sheet2
Value 3456 in Sheet1 found at $C$7 in Sheet2
Value 66 in Sheet1 found at $D$31 in Sheet2
Value 77 in Sheet1 found at $C$16 in Sheet2
Value 9998887 in Sheet1 found at $F$100 in Sheet2
Value 3456 in Sheet1 found at $C$7 in Sheet2
Value 66 in Sheet1 found at $D$31 in Sheet2
Value 77 in Sheet1 found at $C$16 in Sheet2
Value 9998887 in Sheet1 found at $F$100 in Sheet2
Value 3456 in Sheet1 found at $C$7 in Sheet2
Value Way down here! in Sheet1 found at $E$999 in Sheet2

[moutoncito]

Hello ADezii :

I tried to use the macro that you send me, and i have no results displays !!!
I don t know how to do?? And i didn t understood the last part of your code??

OUTPUT
Value 66 in Sheet1 found at $D$31 in Sheet2
Value 77 in Sheet1 found at $C$16 in Sheet2
Value 9998887 in Sheet1 found at $F$100 in Sheet2
Value 3456 in Sheet1 found at $C$7 in Sheet2
Value 66 in Sheet1 found at $D$31 in Sheet2
Value 77 in Sheet1 found at $C$16 in Sheet2
Value 9998887 in Sheet1 found at $F$100 in Sheet2
Value 3456 in Sheet1 found at $C$7 in Sheet2
Value 66 in Sheet1 found at $D$31 in Sheet2
Value 77 in Sheet1 found at $C$16 in Sheet2
Value 9998887 in Sheet1 found at $F$100 in Sheet2
Value 3456 in Sheet1 found at $C$7 in Sheet2
Value Way down here! in Sheet1 found at $E$999 in Sheet2

How does it works??

Thanks for the help

[ADezii]

One question at a time.

1. Press the ALT+F11 Key Combiniation to Open the VBA Code Window.
2. If the Immediate Window is not already Open, press CTRL+G.
3. The results of the comparison will be in this Window.

[ADezii]

I've taken the liberty to modify the Code and output the Results (Matches) to the 1st Column of Sheet3. Just make sure that you have 3 Worksheets named: Sheet1, Sheet2, and Sheet3 in order for the Revised Code to work properly.

Dim rng_1 As Range
Dim rng_2 As Range
Dim rngRef_1 As Range
Dim rngRef_2 As Range
Dim intRowNum As Integer
 
Worksheets("Sheet3").Range("A1:A1000").ClearContents
 
Set rng_1 = Worksheets("Sheet1").Range("$A$2:$F$1000")
Set rng_2 = Worksheets("Sheet2").Range("$A$2:$F$1000")
 
For Each rngRef_1 In rng_1
  For Each rngRef_2 In rng_2
    If rngRef_1.Value <> "" Then
      If rngRef_1.Value = rngRef_2.Value Then
        intRowNum = intRowNum + 1
        Worksheets("Sheet3").Cells(intRowNum, 1).Value = "Match# " & intRowNum & ": " & _
                    "Value " & rngRef_1.Value & " in Sheet1 found at " & _
                     rngRef_2.Address & " in Sheet2"
      End If
    End If
  Next
Next
 
'Allow for 1,000 Matches
Worksheets("Sheet3").Range("A1:A1000").Columns.AutoFit

[NeoPa]

I would consider using VLookup(...) for this task. It is a built-in Excel function that you can use in a formula that can be copied to all the relevant cells in Sheet3. I have included a document as an attachment that I set up for some of my users at work on how they can use this most powerful of functions. I include the text part of it here to be visible.

VLookUp(A, B, C, D)

VlookUp or Vertical LookUp is a function that enables Excel to 'Find' related items from within a range stored elsewhere. Elsewhere can mean another excel workbook on another PC, but can also mean a range in the current worksheet.

In any Range reference in Excel, a '$' preceeding an element of the address, simply tells Excel not to be clever when copying or dragging the formula to other cells. Notice the formulas displayed in column B below: First B1 is created, then that cell is copied and pasted, or dragged down over, the other cells. Notice the only item that changes is the number (Row reference) after $A.

1. This is the item to search for and is often a cell reference. The '$' is usually required here for the column letter but rarely for the Row number.
   
2. This is a reference to the range being searched. This is almost always fully referenced with '$'s.
   
   If it is a requirement to refer to ranges in other worksheets or even other workbooks, the format for each of these is illustrated in cells E1 & E3 below respectively. For space reasons, the range in Book2 starts at B3 rather than at E5, but '[Book1]Sheet 2' has the data in the same place as Sheet1 (E5:F9). Basically, a worksheet reference preceeds the cell part with a '!' separating them. Any complicated name (reserved word or containing unusual characters including spaces) requires singe quotes (') around it - Sheet2!$A$1:$C$ 9 but 'Sheet 2'!$A$1:$C$9. A workbook reference preceeds the worksheet reference and is surrounded in [] brackets. If (')s required in sheet name then the workbook reference follows the first (') - '[Book2]Sheet 2'!$A$1:$C$9.
   
3. This is the column to display if a match is found. In the example below, the words (Abacus; Broadcast; ...) are found in column F, but in column 2 from the left of the range. Column C shows the results displayed when the formula in Column B is used.
   
4. This indicates to Excel how it should treat items where a match isn't found :
   True (or non-zero) indicates it expects an ordered table and it will return the next item in the list after the point this item would have been. This is never used for simply finding items in a list. This would be used by accountants sometimes.
   
   False (or zero) will only find exact matches. Any item not found would return a #N/A value (See C4 below) which can be detected by using the ISNA() function.

NB. The quotes in the word document are converted by Word. They should all read (').

[ADezii]

Hello NeoPa, forgive my ignorance, but it is my understanding that VLookup() will find every Value defined in a Single Column of an Array within a Range/List. How can this be applied when searching for every conceivable Match within a Range defined in Sheet1 (A2:F1000) against an identical Range in Sheet2? Checking every Value in Sheet1 against every Value in Sheet2 would involve , (5,994 * 5,994), or 35,928,036 individual checks for equality. Take care.

[NeoPa]

VLookup() will handle checking all the values in a column, so if the OP indeed meant that each value of each column is treated altogether like a single list (which isn't so clear from the explanation), then a formula including VLookups is still likely to be an efficient solution.

If however, the values in column A are the only ones that need checking (and there is nothing explicit to say the columns are independant values), then it's only one.

Obviously we don't have all the facts, but assuming the data is held in up to a thousand rows and all the data is to be treated as similar, then it would be common sense, and really quite easy, to produce a set (or two) of data first where all is contained in a single column. From there it would be as simple as it should be.

[ADezii]

Thanks for the explanation. I've never actually used it, so I was indeed curious.

[NeoPa]

No need for explanations ADezii. It was a perfectly reasonable question.

I would say though, if you are doing any sort of work in Excel using large sets of data, VLookup() is a skill you should certainly gain.