Removing Duplicates from Delimited Content

[anonymous321]

Hi, I have uploaded an excel file as an attachment which contains two sheets named "before" and "after". The "before" sheet data should be converted to "after" sheet data.

Actually, in sheet "before" column A, each comma separated value correponds to other comma separated value in column B as one to one correspondence. Now the duplicate values both in cell A1 and B1 which corresponds to each other should be deleted like b corresponds to 2 and c corresponds to 3 in sheet "before" but both of them (b-->2 and c-->3) repeated themselves and so their corresponding values in cell B1. So the duplicates have been removed and the results shown in "After" sheet. I hope you understand my problem. The whole operation could be performed on the same sheet so it is not compulsory that output should be on the new sheet.

Again Many thanks in advance for the help and your precious time.

Best Regards,

[FlameRetired]

The only way I know to do this in Excel 2007 is with VBA.

In the attached find this code already installed for a User Defined Function called CONCATALL. It is written by tigeravatar.

'tigeravatar ExcelForum 
Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String 
'Created by TigerAvatar at www.excelforum.com, September 2012 
'Purpose is to concatenate many strings into a single string 
'Can be used with arrays, range objects, and collections 
     
    Dim DataIndex As Variant    'Used to loop through arrays, range objects, and collections 
    Dim strResult As String     'Used to build the result string 
     
    'Test if varData is an Array, Range, or Collection 
    If IsArray(varData) _ 
    Or TypeOf varData Is Range _ 
    Or TypeOf varData Is Collection Then 
         
        'Found to be an, array, range object, or collection 
        'Loop through each item in varData 
        For Each DataIndex In varData 
            'Check if the item isn't empty 
            If Len(DataIndex) > 0 Then 
                'Found the item isn't empty, check if user specified bUnique as True 
                If bUnique = True Then 
                    'bUnique is true, check if the item has been included in the result yet 
                    If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then 
                        'Item has not been included in the result, add item to the result 
                        strResult = strResult & "||" & DataIndex 
                    End If 
                Else 
                    'bUnique is not true, add item to the result 
                    strResult = strResult & "||" & DataIndex 
                End If 
            End If 
        Next DataIndex 
         
        'Correct strResult to remove beginning delimiter and convert "||" to the specified sDelimiter 
        strResult = Replace(Mid(strResult, 3), "||", sDelimiter) 
         
    Else 
        'Found not to be an array, range object, or collection 
        'Simply set the result = varData 
        strResult = varData 
    End If 
     
    'Output result 
    ConcatAll = strResult 
     
End Function  

Though already installed in the attached here is how that was done.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the code into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Then this helper formula in C1 that counts the number of elements in the source strings.

Please Login or Register  to view this content.

Then array entered in D1 and filled across

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.