Remove duplicates form the same row and other rows

can an expert provide the answer to this in the form of VBA code?

I need to remove all duplicates from all rows

so I could have 10, 20 30 rows of

AIOF,AIOF,AIOF,AIOF,AIOFPLC,AIOFPLC,AMHH,AMHH,APFT,APFT,BUNP,BUNP,BUNP,BUNP,EGTO,EGTO,EGTO,EGTO
AIOF,AIOF,AIOF,AIOF,AIOFPLC,AIOFPLC,AMHH,AMHH,APFT,APFT,BUNP,BUNP,BUNP,BUNP,EGTO,EGTO,EGTO,EGTO
AIOF,AIOF,AIOF,AIOF,AIOFPLC,AIOFPLC,AMHH,AMHH,APFT,APFT,BUNP,BUNP,BUNP,BUNP,EGTO,EGTO,EGTO,EGTO

and I only want one occurance of each , so if I have 3 rows like the above I want to end up with in row like the below. [this can be put on a new worksheet]

AIOF,,AIOFPLC,AMHH,APFT,APFT,BUNP,EGTO,


thanks
JagwarmanAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
krishnakrkcConnect With a Mentor Commented:
Sub kTest()
    
    Dim k, e, v, i As Long
    
    k = Range("a1").CurrentRegion.Value2
    
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For Each e In k
            v = Split(e, ",")
            For i = 0 To UBound(v)
                If Len(v(i)) Then .Item(Trim(v(i))) = Empty
            Next
        Next
        If .Count Then
            Worksheets.Add
            Range("a1").Value = Join(.keys, ",")
        End If
    End With
    
End Sub

Open in new window


Kris
0
 
QlemoDeveloperCommented:
And what should happen with

AIOF,AIOF,AIOF,AIOF,AIOFPLC,AIOFPLC,AMHH,AMHH,APFT,APFT,BUNP,BUNP,BUNP,BUNP,EGTO,EGTO,EGTO,EGTO
AIOF,AIOF,AIOFPLC,AMHH,AMHH,APFT,APFT,BUNP,BUNP,EGTO,EGTO

are those also duplicates?
And can the original worksheet be modified (i.e. duplicates removed here)?
0
 
ProfessorJimJamConnect With a Mentor Commented:
Hey Jagwrman,

run this code on the selection    make sure you run the macro called remDupstringsComma

Sub remDupstringsComma()
   ' Comma Separator
Dim dic As Object, cell As Range, temp As Variant
Dim i As Long
Set dic = CreateObject("scripting.dictionary")
With dic
    For Each cell In Selection
        .RemoveAll
        If Len(cell.Value) > 0 Then
            temp = Split(" " & cell.Value, ",")
            For i = 0 To UBound(temp)
                If Not .Exists(temp(i)) Then .Add temp(i), temp(i)
            Next i
            cell.Value = Mid(Join(.Keys, ","), 2)
        End If
    Next cell
End With
        Call RemoveDuplicateRows
End Sub

Sub RemoveDuplicateRows()
    Application.ScreenUpdating = False
    Dim ColN As Long
    Dim MyS As Worksheet: Set MyS = ActiveSheet
    Dim MyR As Range: Set MyR = MyS.Cells(1, 1).CurrentRegion
    Dim NumCol As Long: NumCol = MyR.Columns.Count
    Dim MyArray As Variant: ReDim MyArray(0 To NumCol - 1)
    For ColN = 1 To NumCol
        MyArray(ColN - 1) = ColN
    Next
    MyR.RemoveDuplicates Columns:=(MyArray), Header:=xlYes
    Dim rowcount As Long, i As Long, j As Long, k As Boolean
    rowcount = MyR.Rows.Count
    For i = rowcount To 1 Step -1
        k = 0
        For j = 1 To NumCol
            If MyR.Value2(i, j) <> "" Then
                k = 1
                Exit For
            End If
        Next j
        If k = 0 Then
            MyR.Rows(i).Delete Shift:=xlUp
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Open in new window

0
All Courses

From novice to tech pro — start learning today.