Solved

Remove duplicates form the same row and other rows

Posted on 2014-10-23
4
128 Views
Last Modified: 2014-10-24
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
0
Comment
Question by:Jagwarman
4 Comments
 
LVL 69

Expert Comment

by:Qlemo
ID: 40399643
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
 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 250 total points
ID: 40399710
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
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 250 total points
ID: 40400021
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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question