Results 1 to 10 of 18

Thread: Can I remove 100 characters that are duplicate and found in other cells?

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Seeker sorovince's Avatar
    Join Date
    Nov 2020
    Posts
    9
    Articles
    0
    Excel Version
    2019

    Can I remove 100 characters that are duplicate and found in other cells?

    I apologize if this has been asked before but I couldn't find it.


    I want to be able to tell excel to remove at least 100 duplicate characters that are found in other cells. I uploaded my excel here so you can see what I mean:


    https://www.dropbox.com/s/e5i96f58o3...test.xlsx?dl=0


    So, these 100 characters...


    .navheader { display: block; width: 120px; font-family: Georgia; font-size: 16px; font-weight: bold;


    ...are found in B2, B3, and B4. Is there a way to click on a button or execute a macro so that each time excel finds 100+ characters that are duplicate, to delete them, leaving only one copy of it?


    Let me know in case I'm doing a bad job of explaining it.


    Thank you so much,
    Trajko

  2. #2
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    277
    Articles
    0
    Excel Version
    2007
    .
    This macro will search the entire workbook and delete any term / terms you designate. In this example the macro
    is seeking a double forward slash and replacing it with a single forward slash.

    Code:
    Sub Removediag()    Dim sht As Worksheet
        For Each sht In ActiveWorkbook.Worksheets
            sht.UsedRange.Replace "//", "/", xlPart
        Next
    End Sub

  3. #3
    Seeker sorovince's Avatar
    Join Date
    Nov 2020
    Posts
    9
    Articles
    0
    Excel Version
    2019
    Hi Logit,

    Thank you for replying. While that is certainly a handy tip, it doesn't solve the issue that I have. I need a macro that searches for a set of characters (let's say 100 characters) that are appearing multiple times in different cells. I apologize if i haven't been clear in my original description

  4. #4
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    277
    Articles
    0
    Excel Version
    2007
    If you specify in the macro, the "hundred characters" you want removed, the macro will do that for you.

    Replace the FORWARD SLASH symbols with the hundred characters. Replace the SINGLE FORWARD SLASH symbol with "" (means you don't want the characters
    replaced with anything ... you simply want them deleted.

    Example : sht.UsedRange.Replace "abcdefghijk etc etc", "", xlPart

  5. #5
    Seeker sorovince's Avatar
    Join Date
    Nov 2020
    Posts
    9
    Articles
    0
    Excel Version
    2019
    Yes, that would be a great solution if I had a specific 100+ characters that I want to be removed. But I want any set of characters. For for example if...

    asdfkljasdf;lkjasasdklfja;sldkjfasdl;fkjasdlkfjaslkdfj;alsdf;lakjsdfl;kasjdflkajsdf;aljsdfafsdlk;jasdlkfjasdlfkjasdlfkj

    and....

    32498324987329487324098723049872394872309872409817309821730981273098127389127301928370912873

    are both found multiple times in multiple cells, they should both be deleted, because they are both duplicates. In other words, I don't to specify a specific set of characters but rather tell excel to delete everything that it finds that is a duplicate. And what is a duplicate? It's a string of at least 100 characters that is found more than once across all cells.

    Again, feel free to let me know Logit if I'm doing a bad job explaining this, as I'm fairly new to this.

    Thanks again for your help.

  6. #6
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    277
    Articles
    0
    Excel Version
    2007
    .
    My fault ... your explanation is accurate.

    Try this and let me know :

    Code:
    Option Explicit
    
    
    Sub DeleteDupsInRows()
         
         
        Dim DataRow As Variant
        Dim Dict    As Object
        Dim j       As Long
        Dim k       As Long
        Dim Key     As String
        Dim lastCol As Long
        Dim lastRow As Long
        Dim Rng     As Range
        Dim Wks     As Worksheet
         
        For Each sht In ActiveWorkbook.Worksheets
        
            Set Wks = ActiveSheet
             
            Set Rng = Wks.Range("A1")
             
            lastCol = Wks.Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False, False, False).Column
            lastRow = Wks.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False).Row
             
            If lastRow < Rng.Row Then Exit Sub
             
            Set Rng = Rng.Resize(lastRow - Rng.Row + 1, lastCol - Rng.Column + 1)
             
            Set Dict = CreateObject("Scripting.Dictionary")
            Dict.CompareMode = vbTextCompare
             
            For j = 1 To Rng.Rows.Count
                DataRow = Rng.Rows(j).Value
                For k = 1 To UBound(DataRow, 2)
                    Key = Trim(DataRow(1, k))
                    If Key <> "" Then
                        If Not Dict.Exists(Key) Then
                            Dict.Add Key, 1
                        End If
                    End If
                Next k
                Rng.Rows(j).Value = Empty
                Rng.Rows(j).Resize(1, Dict.Count).Value = Dict.Keys
                Dict.RemoveAll
            Next j
        Next
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •