How to compare 2 cells with delimited items in each and output the difference in items

0 votes

What is the most effective technique to compare two Excel cells that each include delimited items?

As an illustration, I want to make sure that every hue in Set A is present in Set B and vice versa. The ideal solution would be to report which item from Set A is missing in Set B in a distinct column, say Result Set A. similarly, the output which item from Set B is missing in Set A in the Result Set B column.

Example

Colors Set A Colors Set B
red;blue;green yellow;purple;red
white;green yellow;red

Goal

Colors Set A Colors Set B Result Set A Result Set B
red;blue;green yellow;purple;red blue;green yellow;purple
white;green;red yellow;red white;green yellow

Note: As of right now, I do this very manually: Using excel Text to Columns with a delimiter, then doing COUNTIF to check if each cell exists in the opposite range - very complicated for larger data sets.

Feb 23, 2023 in Others by narikkadan
• 63,700 points
391 views

1 answer to this question.

0 votes

The following function would do this for you.

Function compare(setA As String, setB As String)

    Const delim As String = ";"
    
    For Each strPartA In Split(setA, delim)
        found = False
        For Each strPartB In Split(setB, delim)
            If Trim(strPartA) = Trim(strPartB) Then found = True
        Next
        If found = False Then compare = compare & strPartA & delim
    Next
    
    If Len(compare) > 0 Then compare = Left(compare, Len(compare) - 1)
    
End Function

You can use this as a UDF - i.e. enter it as a formula, e.g. if A1 contains "red;blue;green" and B1 contains "yellow;purple;red" then you can enter this formula in C1:

=compare(A1,B1)             to return "blue;green"

And this in D1

=compare(B1,A1)             to return "yellow;purple"

Or alternatively, you could write this into a routine:

Sub test()
    Dim c As Range
    With ActiveSheet
        For Each c In Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Cells
            c.Offset(, 2) = compare(c.Value, c.Offset(, 1).Value)
            c.Offset(, 3) = compare(c.Offset(, 1).Value, c.Value)
        Next
    End With
End Sub
answered Feb 23, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,700 points
2,073 views
0 votes
1 answer
+2 votes
1 answer

How to compare 2 comna separated strings in informatica?

A java transformation can be used to ...READ MORE

answered Aug 18, 2019 in Others by Rojalin
• 200 points
1,278 views
–2 votes
0 answers
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
797 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
1,613 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
1,447 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,700 points
1,098 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
1,334 views
0 votes
1 answer

How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
375 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP