+ Reply to Thread
Results 1 to 7 of 7

Compare 2 lists and output unmatched items using array

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Compare 2 lists and output unmatched items using array

    Hi all - I have 2 sheets("Price1) and ("Price2"), which contain 3 columns (ColumnA "Date" , columnB "Item#" , columnC "Price"). I need to compare Item and Price in both sheets and output a list of unmatched item and price in column E.

    Sheet("Price1")
    Date Item# Price Unmatched Items
    07/07/13 101 100.00
    07/30/13 102 156.00
    07/12/13 103 157.00
    07/05/13 104 125.00
    07/25/13 105 2,555.00
    07/14/13 106 251.00
    07/13/13 107 2,255.00


    Sheet("Price2")
    Date Item# Price Unmatched Items
    07/07/13 101 100.00 108 252.00
    07/30/13 102 156.00 109 252.00
    07/12/13 103 157.00 110 85.00
    07/05/13 104 125.00 111 26.00
    07/25/13 105 2,555.00 112 245.00
    07/14/13 106 251.00 113 5,625.00
    07/13/13 107 2,255.00 114 25.00
    07/03/13 108 252.00 115 256.00
    07/15/13 109 252.00
    07/05/13 110 85.00
    07/21/13 111 26.00
    07/08/13 112 245.00
    07/12/13 113 5,625.00
    07/19/13 114 25.00
    07/29/13 115 256.00



    Code needs help:

    Sub Compare2Lists()
      Dim i As Long, j As Long, LastRow As Long
      Dim Arr1 As Variant, Arr2 As Variant
      
      Lrow1 = Sheets("Price1").Cells(Rows.Count, "A").End(xlUp).Row
      Lrow2 = Sheets("Price2").Cells(Rows.Count, "A").End(xlUp).Row
      
      Arr1 = Sheets("Price1").Range("B2:C" & Lrow1)
      Arr2 = Sheets("Price2").Range("B2:C" & Lrow2)
      
      Counter = 0
      For i = 1 To UBound(Arr1, 1)
        For j = 1 To UBound(Arr1, 2)
            If Arr1(i, j) <> Arr2(i, j) Then
                NewArr = Arr1(i, j)
                Counter = Counter + 1
            End If
        Next
      Next
      Range("E1").Offset(Counter, 0).Value = NewArr
    End Sub

  2. #2
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Compare 2 lists and output unmatched items using array

    Try this:-
    Sub MG24Jul09
    Dim Rng1        As Range
    Dim Dn          As Range
    Dim Rng2        As Range
    Dim Twn         As String
    Dim nRng        As Range
    Dim S           As Byte
    
    With Sheets("Price1")
    Set Rng1 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
    End With
    With Sheets("Price2")
    Set Rng2 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
    End With
    
    shts = Array(Rng1, Rng2)
        With CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
    For S = 0 To 1
        For Each Dn In shts(S)
            Twn = Dn & Dn.Offset(, 1)
            If Not .Exists(Twn) Then
                Set nRng = Dn.Resize(, 2)
                .Add Twn, nRng
            Else
                .Remove Twn
            End If
    Next
    Next S
       Sheets("Price1").Range("D2").Resize(.Count, 2) = Application.Transpose(Application.Transpose(.items))
        Sheets("Price2").Range("D2").Resize(.Count, 2) = Application.Transpose(Application.Transpose(.items))
    
    End With
    End Sub
    Regards Mick

  3. #3
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: Compare 2 lists and output unmatched items using array

    MickG - Thank you for sharing your code. It works great, but it would be nice if you could kindly comment the steps and logic. Is it possible to tweak my code and make it work. It would be easier for me to follow my own logic.

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Compare 2 lists and output unmatched items using array

    This may help, Your code requires the Cols "B & C" to be combined to enable a comparison Between both sheets.I think it is possible but can gets quite complicated.
    Sub MG25Jul12
    Dim Rng1        As Range
    Dim Dn          As Range
    Dim Rng2        As Range
    Dim Twn         As String
    Dim nRng        As Range
    Dim S           As Byte
    
    With Sheets("Price1")
    Set Rng1 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
    End With
    With Sheets("Price2")
    Set Rng2 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
    End With
    
    'Hold both Ranges in an Array
    shts = Array(Rng1, Rng2)
        With CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
    'Loop through both Ranges from Array sheets
    For S = 0 To 1
        For Each Dn In shts(S)
            
    'Amalgamate columns B & C
            Twn = Dn & Dn.Offset(, 1)
                        
    'Nb:- Twn is the "key" for the dictionary and nRng is the "Item"
    'If String "Twn" found then  (Key) and (Item) Places in Dictionary
            If Not .Exists(Twn) Then
                Set nRng = Dn.Resize(, 2)
                '(Key)-,-(Item)
                .Add Twn, nRng
            Else
    
    'If the same "Twn" is found again it is removed
                .Remove Twn
            End If
    Next
    Next S
    'The "Items" remaining are transposes to a 2-D array and again Transposed to Enable the sheet presentaion.
       Sheets("Price1").Range("D2").Resize(.Count, 2) = Application.Transpose(Application.Transpose(.items))
        Sheets("Price2").Range("D2").Resize(.Count, 2) = Application.Transpose(Application.Transpose(.items))
    
    End With
    End Sub
    Regards Mick

  5. #5
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Compare 2 lists and output unmatched items using array

    Here's an alternative using ranges an arrays.
    Sub MG25Jul03
    Dim Rng1        As Range
    Dim Dn          As Range
    Dim Rng2        As Range
    Dim Twn         As String
    Dim nRng        As Range
    Dim S           As Byte
    Dim n           As Long
    Dim nn          As Long
    Dim p           As Long
    Dim Ray
    With Sheets("Price1")
    Set Rng1 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
    End With
    With Sheets("Price2")
    Set Rng2 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
    End With
    
    'Create Blank Results array
    ReDim Ray(1 To Rng1.Count + Rng2.Count, 1 To 2)
    'Amalgamate both ranges in array
    shts = Array(Rng1, Rng2)
    
    'First Loop "n" to loop through total number of cells in both ranges with Search term
        For n = 1 To Rng1.Count + Rng2.Count
            nn = n: c = 0
    'Second loop to loop through actual ranges looking for search term.
        For S = 0 To 1
            For Each Dn In shts(S)
    ' Change count when second range appears
                If n > Rng1.Count Then nn = n - Rng1.Count
    'Change ranges when n > first range count
                Set R = IIf(n > Rng1.Count, Rng2, Rng1)
    'Count number of matches found
                If Dn & "," & Dn.Offset(, 1) = R(nn) & "," & R(nn).Offset(, 1) Then c = c + 1
             Next Dn
        Next S
    'if only one matche then place in array
        If c = 1 Then
            p = p + 1
            Ray(p, 1) = R(nn)
            Ray(p, 2) = R(nn).Offset(, 1)
        End If
    Next n
    Range("D2").Resize(p, 2) = Ray
    End Sub
    Regards Mick

  6. #6
    Forum Contributor
    Join Date
    10-22-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: Compare 2 lists and output unmatched items using array

    Hi MickG - Thank you very much for your help and being patient with me. Are there any good books, video tutorial that you would recommend in getting over the vba learning curve?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. |Need to compare 2 lists and then show output
    By Philla in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-16-2011, 02:06 PM
  2. Compare two email lists and output non-matching
    By Axle_Max in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-02-2009, 06:18 AM
  3. Compare Lists, Show unduplicated items
    By lil_ern63 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-29-2006, 05:54 PM
  4. [SOLVED] How do I compare two lists and choose the items that are in both?
    By laurabailey8 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-03-2006, 07:35 PM
  5. Compare 2 columns, and create a list of items that are in both lists
    By ruby2sdy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-08-2005, 07:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1