Results 1 to 3 of 3

Copy Numbers based on compare names in 2 Sheets

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Copy Numbers based on compare names in 2 Sheets

    HI I would like to have some help with this problem.
    In sheet "Final Match" i need to compare the names in column C
    with names in sheet "New Cars input" column B
    It need to find the matching payments in column M Sheet "New Cars input"
    and display them in new column J Sheet "Final Match".
    So i guess its a kind of vlookup. But i need a macro for this.

    In Below testsheet you can see how it have to be.

    I have also this below code, which i think is a jindon code, which maybe can be changed so it works here.
    I have tried but cant make that work

    But please have a look.

    Sincerely

    Abjac

    The code which maybe can be changed.

    Option Explicit
    
    Sub FIndmatches()
        Dim a, e, s, i As Long, n As Long
        a = Sheets("Findsheet").Cells(1).CurrentRegion.Value
        With CreateObject("Scripting.Dictionary")
            .comparemode = 1
            For i = 2 To UBound(a, 1)
                Set .Item(a(i, 1)) = CreateObject("Scripting.Dictionary")
                .Item(a(i, 1)).comparemode = 1
            Next
            a = Sheets("Lookupsheet").Cells(1).CurrentRegion.Value
            For i = 2 To UBound(a, 1)
                If .Exists(a(i, 1)) Then .Item(a(i, 1))(a(i, 2)) = Empty
            Next
            ReDim a(1 To UBound(a, 1), 1 To 2)
            For Each e In .Keys
                n = n + 1
                a(n, 1) = e
                a(n, 2) = ""
                If .Item(e).Count > 0 Then
                    For Each s In .Item(e).Keys
                        a(n, 2) = s
                        n = n + 1
                    Next
                    n = n - 1
                End If
            Next
          End With
        With Sheets("Match").UsedRange
            '.CurrentRegion.ClearContents
         .Range("A1:B1") = Array("Names.", "Customer Numbers.")
           With .Offset(.Rows.Count + 1).Resize(n)
           .Value = a
          End With
        End With
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

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