Results 1 to 27 of 27

Exclude data conflicts and then match remaining Data

Threaded View

Lee_of_Excel Exclude data conflicts and... 09-01-2020, 09:03 PM
Lee_of_Excel Re: Exclude data conflicts... 09-02-2020, 09:31 PM
jindon Re: Exclude data conflicts... 09-02-2020, 10:44 PM
Lee_of_Excel Re: Exclude data conflicts... 09-02-2020, 10:59 PM
jindon Re: Exclude data conflicts... 09-02-2020, 11:15 PM
Lee_of_Excel Re: Exclude data conflicts... 09-02-2020, 11:24 PM
jindon Re: Exclude data conflicts... 09-02-2020, 11:28 PM
Lee_of_Excel Re: Exclude data conflicts... 09-02-2020, 11:41 PM
Lee_of_Excel Re: Exclude data conflicts... 09-02-2020, 11:32 PM
jindon Re: Exclude data conflicts... 09-02-2020, 11:39 PM
Lee_of_Excel Re: Exclude data conflicts... 09-02-2020, 11:46 PM
jindon Re: Exclude data conflicts... 09-03-2020, 05:33 AM
Lee_of_Excel Re: Exclude data conflicts... 09-03-2020, 05:59 AM
jindon Re: Exclude data conflicts... 09-03-2020, 06:03 AM
Lee_of_Excel Re: Exclude data conflicts... 09-03-2020, 07:09 AM
jindon Re: Exclude data conflicts... 09-03-2020, 07:30 AM
Lee_of_Excel Re: Exclude data conflicts... 09-03-2020, 07:44 AM
Lee_of_Excel Re: Exclude data conflicts... 09-03-2020, 08:28 PM
jindon Re: Exclude data conflicts... 09-03-2020, 08:51 PM
Lee_of_Excel Re: Exclude data conflicts... 09-03-2020, 10:01 PM
jindon Re: Exclude data conflicts... 09-03-2020, 10:06 PM
Lee_of_Excel Re: Exclude data conflicts... 09-03-2020, 10:12 PM
jindon Re: Exclude data conflicts... 09-03-2020, 10:22 PM
Lee_of_Excel Re: Exclude data conflicts... 09-03-2020, 10:38 PM
jindon Re: Exclude data conflicts... 09-03-2020, 10:45 PM
Lee_of_Excel Re: Exclude data conflicts... 09-03-2020, 11:00 PM
Lee_of_Excel Re: Exclude data conflicts... 09-04-2020, 02:39 AM
  1. #2
    Registered User
    Join Date
    11-07-2018
    Location
    Sydney Australia
    MS-Off Ver
    2016 Pro
    Posts
    74

    Re: Exclude data conflicts and then match remaining Data

    The previous matching code works in the same way, except it is matching USER PREFERENCES OF SITE against SITE PREFERENCES OF USER,

    I have tried to modify this but my coding skills are currently at the level to properly code the scripting dictionaries.

    I had a previous Unprotect, Protect code in here also which I will keep for this one too.

    
    Option Explicit
    
    Sub Matching_Tool()
        Dim a, b, i As Long, ii As Long, n As Long, txt As String, x, dic As Object
        Set dic = CreateObject("Scripting.Dictionary")
        dic.CompareMode = 1
        With Sheets("matching_tool")
            a = .ListObjects("table1").DataBodyRange
            x = .ListObjects("table1").HeaderRowRange
            For ii = 2 To UBound(a, 2)
                For i = 1 To UBound(a, 1)
                    If a(i, ii) <> "" Then
                        txt = Join(Array(a(i, 1), CStr(a(i, ii))), Chr(2))
                        dic(txt) = x(1, ii)
                    End If
                Next
            Next
            ReDim b(1 To dic.Count, 1 To 3)
            a = .ListObjects("Table2").DataBodyRange
            x = .ListObjects("table2").HeaderRowRange
            For ii = 2 To UBound(a, 2)
                For i = 1 To UBound(a, 1)
                    If a(i, ii) <> "" Then
                        txt = Join(Array(a(i, ii), CStr(a(i, 1))), Chr(2))
                        If dic.exists(txt) Then
                            n = n + 1: b(n, 1) = a(i, ii)
                            b(n, 2) = CStr(a(i, 1)): b(n, 3) = dic(txt)
                            If dic(txt) <> x(1, ii) Then b(n, 3) = b(n, 3) & "/" & x(1, ii)
                        End If
                    End If
                Next
            Next
         'Unprotect a worksheet with a password
    Sheets("Matching_Tool").Unprotect Password:="BLANK"
            With .ListObjects("table3")
                .DataBodyRange.ClearContents
                If n > 0 Then
                    .ListRows(1).Range.Resize(n).Value = b
                Else
                    MsgBox "No matches"
                End If
            End With
        End With
    Call Matrix_Rank
        'Protect worksheet with a password
    Sheets("Matching_Tool").Protect Password:="BLANK"
    End Sub
    Last edited by Lee_of_Excel; 09-02-2020 at 09:34 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. how to eliminate certain data from the worksheet and then sort the remaining data
    By sumesh56 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-20-2018, 12:13 PM
  2. Macro to clear row data then shift remaining data up to empty rows.
    By clarmech in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-15-2014, 04:35 PM
  3. Exclude items from Data Validation List based on another columns data
    By amartin575 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-19-2013, 11:53 AM
  4. Index/match or Vlookup to exclude data
    By MZing81 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-21-2012, 03:32 PM
  5. How to find match data and exclude with condition
    By buvan in forum Excel General
    Replies: 2
    Last Post: 11-17-2011, 06:51 AM
  6. exclude a match condition between two data fields
    By rhiekel in forum Excel General
    Replies: 6
    Last Post: 04-22-2011, 10:53 AM
  7. [SOLVED] Adding data to celss but keeping the remaining data intact.
    By TP in forum Excel General
    Replies: 4
    Last Post: 04-12-2006, 09:20 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