+ Reply to Thread
Results 1 to 4 of 4

Compare two cells with Text strings separated by comma; return Unique & Duplicate values

Hybrid View

  1. #1
    Registered User
    Join Date
    11-27-2019
    Location
    Abu Dhabi
    MS-Off Ver
    2016
    Posts
    6

    Post Compare two cells with Text strings separated by comma; return Unique & Duplicate values

    Hi,

    I have vast data flowing from Primavera P6. There are numerous Activity IDs' that has to be compared between two schedules. Each cell has several text strings (Activity ID) that are separated by a comma (,). I have recreated the scenario in the below table and require 3 different results as in the table. A simple substitute function is able to produce accurate results. I did try some VBA's but the results were partial. I have attached the file with the same explanation and the Real sample data for understanding. I need some expert advice to help solving the problem.

    Thanks in advance

    Excel Querry.JPG
    Attached Files Attached Files
    Last edited by pvkvimalan; 11-27-2019 at 02:41 AM.

  2. #2
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: Compare two cells with Text strings separated by comma; return Unique & Duplicate valu

    Hi
    pls see the vbs code to compare


    Private Function COMPARE(Rng1, Rng2 As Range, Op As Integer)
    Dim A, B As Variant
    Dim Ans1
    Dim Test As Boolean
    Test = True
    If Op = 1 Or Op = 2 Then
        A = Split(Rng1, ","): B = Split(Rng2, ",")
    ElseIf Op = 3 Then
        A = Split(Rng2, ","): B = Split(Rng1, ",")
    End If
    For Each Cl1 In A
        For Each Cl2 In B
            If Cl1 = Cl2 Then
                Ans1 = Ans1 & Cl1 & ","
                Test = False
            End If
        Next Cl2
        
        If Test Then Ans2 = Ans2 & Cl1 & ","
        Test = True
    Next Cl1
    
    If Op = 1 Then
        COMPARE = Mid(Ans1, 1, Len(Ans1) - 1)
    ElseIf Op = 2 Then
        COMPARE = Mid(Ans2, 1, Len(Ans2) - 1)
    ElseIf Op = 3 Then
        COMPARE = Mid(Ans2, 1, Len(Ans2) - 1)
    End If
    
    End Function

    Syntax
    Compare Column A & B Return Unique values from Column A =COMPARE(B4,C4,2)
    Compare Column A & B Return Unique values from Column B =COMPARE(B5,C5,3)

    Compare Column A & B Return Duplicate Values from both =COMPARE(B6,C6,1)

  3. #3
    Registered User
    Join Date
    11-27-2019
    Location
    Abu Dhabi
    MS-Off Ver
    2016
    Posts
    6

    Re: Compare two cells with Text strings separated by comma; return Unique & Duplicate valu

    Hi,

    Thanks this solved my issue.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,726

    Re: Compare two cells with Text strings separated by comma; return Unique & Duplicate valu

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

    If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ 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. [SOLVED] Compare comma separated values in a cell to a list
    By SMB in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-03-2019, 01:10 AM
  2. count unique comma separated names in range of cells
    By raogm2001 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 10-07-2017, 09:50 AM
  3. Compare comma-separated values with look-up values
    By Niclal in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-10-2016, 08:47 AM
  4. Error in a existing Macro used for compare the Comma Separated Values present in 2 columns
    By Manish_Gupta in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-28-2014, 05:44 AM
  5. [SOLVED] Macro to give the count of unique values after comparing the comma separated values
    By Manish_Gupta in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-03-2014, 12:41 AM
  6. Replies: 7
    Last Post: 02-10-2013, 02:10 AM
  7. Return Y/N after matching from multiple lookup values separated by comma
    By indoglans in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-16-2011, 03:13 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