Results 1 to 19 of 19

Looking for info on updating a macro to compare multiple columns and displaying matches

Threaded View

  1. #1
    Registered User
    Join Date
    11-20-2017
    Location
    CT
    MS-Off Ver
    Office 365
    Posts
    13

    Looking for info on updating a macro to compare multiple columns and displaying matches

    Hello,

    I am attempting to update a macro that compares two separate sheets of contact info and matches on Last Name. I started working with a macro I found that solved part of my problem and am looking to update it further. Right now, the macro is matching the last name column from sheet 1 to last names in sheet 2 and displaying the matching rows onto sheet 3. I would like to add a comparison to also do a fuzzy match on addresses in addition to matching the last name. I believe this needs to happen within the AutoFilter block below but don’t quite understand how it’s working currently. Any help in explaining it and making the necessary adjustments to add the address match would be very helpful. Thank you!



    Sub ExtractCommonDetails()
    Dim sws1 As Worksheet, sws2 As Worksheet, dws As Worksheet
    Dim slr1 As Long, slr2 As Long
    Dim x
    
    Application.ScreenUpdating = False
    
    Set sws1 = Sheets("Sheet1")
    Set sws2 = Sheets("Sheet2")
    Set dws = Sheets("Sheet3")
    
    slr2 = sws2.Cells(Rows.Count, 1).End(xlUp).Row
    
    dws.Cells.Clear
    
    If slr2 < 2 Then
       MsgBox "No records on Sheet2", vbExclamation, "Records Not Found!"
       Exit Sub
    Else
       x = sws2.Range("A2:A" & slr2).Value
    End If
    
    With sws1.Range("A1").CurrentRegion
       .AutoFilter field:=1, Criteria1:=Application.Transpose(x), Operator:=xlFilterValues
       Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy dws.Range("A1")
       .AutoFilter
    End With
    dws.Activate
    dws.Columns.AutoFit
    dws.Range("A1").CurrentRegion.Borders.Color = vbBlack
    Application.ScreenUpdating = True
    MsgBox "Task Completed.", vbInformation, "Done!"
    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)

Similar Threads

  1. Compare two columns for partial matches in the same row
    By AlexNorman100 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-17-2017, 07:05 AM
  2. [SOLVED] Returning multiple matches and and displaying in a single cell with using UDF
    By achimbos in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-12-2015, 10:38 AM
  3. Replies: 1
    Last Post: 09-26-2013, 12:16 PM
  4. Compare 2 other columns if first one matches
    By dclive in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2011, 05:56 AM
  5. How to compare 6 columns and identify matches
    By KINNEY0201 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2010, 08:40 PM
  6. Compare columns & remove matches
    By x1372 in forum Excel General
    Replies: 2
    Last Post: 08-14-2009, 09:35 AM
  7. I need to compare to columns and indicate the matches in another
    By IFIXPCS in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-22-2006, 01:10 PM

Tags for this Thread

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