Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: remove duplicate lines in text file
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default remove duplicate lines in text file

    how can i use a macro to remove duplicate lines in my text file such that the last one will remain, for instance
    the boy is good
    great thinking
    great thinking
    he lives
    the boy is good
    what's up
    great thinking

    it should now be like this
    he lives
    the boy is good
    what's up
    great thinking
    Last edited by yinkajewole; May 7th, 2019 at 05:32 PM.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,881
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    5 Thread(s)

    Default Re: remove duplicate lines in text file

    Since duplicate lines are going to be deleted, it shouldn't matter which instance should remain. Or am I missing something?

  3. #3
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,242
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: remove duplicate lines in text file

    Maybe....
    Code assumes that your data is in column A and you have a header in A1.

    Code:
    Sub DelDupsKeepLast()
    
        Dim myRng As Range, myCell As Range
        Dim RngDel As Range, LstRw As Long
    
        Set myRng = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        LstRw = Range("A" & Rows.Count).End(xlUp).Row
    
        For Each myCell In myRng
    
            If myCell.Row < LstRw Then
                If Not myCell.Offset(1, 0).Resize(LstRw - myCell.Row).Find(What:=myCell.Value, Lookat:=xlWhole) Is Nothing Then
                    If RngDel Is Nothing Then
                        Set RngDel = myCell
                    Else
                        Set RngDel = Application.Union(RngDel, myCell)
                    End If
                End If
            End If
    
        Next myCell
    
        If Not RngDel Is Nothing Then RngDel.EntireRow.Delete
    
    End Sub
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  4. #4
    Board Regular
    Join Date
    Nov 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: remove duplicate lines in text file

    Quote Originally Posted by MARK858 View Post
    Maybe....
    Code assumes that your data is in column A and you have a header in A1.

    Code:
    Sub DelDupsKeepLast()
    
        Dim myRng As Range, myCell As Range
        Dim RngDel As Range, LstRw As Long
    
        Set myRng = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        LstRw = Range("A" & Rows.Count).End(xlUp).Row
    
        For Each myCell In myRng
    
            If myCell.Row < LstRw Then
                If Not myCell.Offset(1, 0).Resize(LstRw - myCell.Row).Find(What:=myCell.Value, Lookat:=xlWhole) Is Nothing Then
                    If RngDel Is Nothing Then
                        Set RngDel = myCell
                    Else
                        Set RngDel = Application.Union(RngDel, myCell)
                    End If
                End If
            End If
    
        Next myCell
    
        If Not RngDel Is Nothing Then RngDel.EntireRow.Delete
    
    End Sub
    the data is not in excel, it's in a text file (.txt)

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,242
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: remove duplicate lines in text file

    You do realize this is an Excel site? Import the data into Excel run the code and save as a text file.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    Board Regular
    Join Date
    Nov 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: remove duplicate lines in text file

    Quote Originally Posted by MARK858 View Post
    You do realize this is an Excel site? Import the data into Excel run the code and save as a text file.
    or is it possible to write lines in a text file while it will remove its duplicate?

  7. #7
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,242
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: remove duplicate lines in text file

    No, you can't remove duplicates while still in a text file, what is so difficult about importing it into Excel and then saving it?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  8. #8
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,726
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: remove duplicate lines in text file

    Try this on a COPY of your text file. It asks for the file name, opens it, reads it, processes the data, and writes back the results to the same file.

    Code:
    Sub RemoveDups()
    Dim FileName As String, MyData As String, MyResult As String, i As Long, MyLines As Variant
    Dim Kept As Long, Lost As Long
    
        FileName = Application.GetOpenFilename
        
        Open FileName For Input As #1 
        MyData = Input(LOF(1), 1)
        Close #1 
        
        MyResult = vbCrLf
        MyLines = Split(MyData, vbCrLf)
        For i = UBound(MyLines) To 0 Step -1
            If InStr(1, MyResult, vbCrLf & MyLines(i) & vbCrLf, vbTextCompare) = 0 Then
                MyResult = vbCrLf & MyLines(i) & MyResult
                Kept = Kept + 1
            Else
                Lost = Lost + 1
            End If
        Next i
        
        Open FileName For Output As #1 
        Print #1 , Mid(MyResult, 2)
        Close #1 
        
        MsgBox Kept & " lines were kept" & vbCrLf & Lost & " lines were removed"
        
    End Sub
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  9. #9
    Board Regular
    Join Date
    Nov 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: remove duplicate lines in text file

    Quote Originally Posted by Eric W View Post
    Try this on a COPY of your text file. It asks for the file name, opens it, reads it, processes the data, and writes back the results to the same file.

    Code:
    Sub RemoveDups()
    Dim FileName As String, MyData As String, MyResult As String, i As Long, MyLines As Variant
    Dim Kept As Long, Lost As Long
    
        FileName = Application.GetOpenFilename
        
        Open FileName For Input As #1 
        MyData = Input(LOF(1), 1)
        Close #1 
        
        MyResult = vbCrLf
        MyLines = Split(MyData, vbCrLf)
        For i = UBound(MyLines) To 0 Step -1
            If InStr(1, MyResult, vbCrLf & MyLines(i) & vbCrLf, vbTextCompare) = 0 Then
                MyResult = vbCrLf & MyLines(i) & MyResult
                Kept = Kept + 1
            Else
                Lost = Lost + 1
            End If
        Next i
        
        Open FileName For Output As #1 
        Print #1 , Mid(MyResult, 2)
        Close #1 
        
        MsgBox Kept & " lines were kept" & vbCrLf & Lost & " lines were removed"
        
    End Sub
    this is too real to be true... Exactly what I was looking for. Many thanks you all

  10. #10
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,726
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: remove duplicate lines in text file

    Glad to help.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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