+ Reply to Thread
Results 1 to 8 of 8

Deleting duplicated lines

  1. #1
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    328

    Deleting duplicated lines

    Hi All

    I have a puzzle. In my documents I sometimes have to paste variable text where there are some duplicated lines.

    The amount of duplicated lines varies too , but they do all follow the same pattern and each is followed by a reference number.

    Each time these lines are inversions of the lines I need to keep.

    I'm trying to delete the first of any duplicated lines.

    for example

    JOYCE SIMS - COME INTO MY LIFE
    97757BX93

    HELP - BEATLES THE
    THE BEATLES - HELP
    111322BX35

    MANDY - BARRY MANILOW
    BARRY MANILOW - MANDY
    113720BX89


    Would become


    JOYCE SIMS - COME INTO MY LIFE
    97757BX93

    THE BEATLES - HELP
    111322BX35

    BARRY MANILOW - MANDY
    113720BX89


    Can someone see a way to do this , and suggest some VBA?

    Grateful for any help.


    Attached Files Attached Files
    Last edited by CDandVinyl; 04-30-2022 at 10:31 PM.
    Using Excel , Word and Access 2003 - March 2022

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,581

    Re: Deleting duplicated lines

    Hi
    are you still using Office 2003 ? If not please amend your profile accordingly to let us know as recent Word versions have many more tools. Thanks

  3. #3
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    328

    Re: Deleting duplicated lines

    Yes , I have 2003 , as stated in my signature.


  4. #4
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,646

    Re: Deleting duplicated lines

    For data such as you have posted, you can do this without VBA using a wildcard Find/Replace with:
    Find = ^13([A-Z ]@) - *^13([A-Z ]@) - \1^13
    Replace = ^p\2 - \1^p
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    328

    Re: Deleting duplicated lines

    Many thanks for this elegant solution. It works fine when I cut and past plain text into a document. When I run it in my existing document , it gives 'not found' errors. Clearly something interfering. If you run it in the sample document above it should show the issue I mention.

    Alternatively , I was also trying to find a way to identify and delete offending lines using the 'bx' which in every number. Essentially , starting at the top of the document if a line has 'bx' in it , then delete the phrase which is two lines below. Repeat down until the end of the document is reached and stop. That would do the trick too.

    Last edited by CDandVinyl; 05-01-2022 at 02:43 PM.

  6. #6
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,646

    Re: Deleting duplicated lines

    Try:
    Find = ^13([A-Z ]@) - *[^l^13]([A-Z ]@) - \1*[^l^13]
    Replace = ^p\2 - \1^p

  7. #7
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    328

    Re: Deleting duplicated lines

    That's perfect. It works seamlessly. I'm very grateful for your time and expertise. For my own education , I'm going to see if I can work through the logic of the RegEx formula. Wish me luck!

    Last edited by CDandVinyl; 05-01-2022 at 07:27 PM.

  8. #8
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,646

    Re: Deleting duplicated lines


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] One line of Data duplicated into multiple lines changing values of some lines
    By benjy99 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-27-2020, 12:20 PM
  2. Replies: 1
    Last Post: 10-09-2015, 09:19 AM
  3. Deleting duplicated rows
    By alonsoler in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-15-2014, 07:13 AM
  4. [SOLVED] Deleting duplicated data.....
    By hello its dean in forum Excel General
    Replies: 7
    Last Post: 07-25-2013, 01:21 PM
  5. Deleting Duplicated Rows
    By LoveCandle in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-12-2007, 01:26 AM
  6. Duplicated lines in a data table
    By kosciosco in forum Excel General
    Replies: 0
    Last Post: 06-08-2006, 12:29 PM
  7. Deleting duplicated values
    By mariomaf in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-05-2006, 10:50 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1