+ Reply to Thread
Results 1 to 4 of 4

Delete all duplicate rows which have identical text in 3 columns

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Delete all duplicate rows which have identical text in 3 columns

    [Edited to include a revised spreadsheet illustrating duplicate rows]

    Hi I'm working on a spreadsheet which has multiple columns of text, and about 5,000 rows.

    In the three columns of text, some of the rows will contain identical text. (that is, the text in the first column is identical to the text in the next two columns). I want to delete any rows which are duplicates. The columns are A, C and G.

    Since the text in these columns might begin with the same letters, or even words, I can't use the SORT feature to find duplicates alphabetically.

    Nor will the LEN() function (which I tried). Many of the cells with different content have an identical number of characters.

    Any suggestions on how to safely remove duplicates?

    An example worksheet is attached.

    (I've replaced the actual words with nonsense words, so enjoy!)

    Thanks!
    Attached Files Attached Files
    Last edited by jrtaylor; 10-28-2020 at 06:57 PM.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Delete all duplicate rows which have identical text in 3 columns

    can you give an example of such duplication in your file?

  3. #3
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: Delete all duplicate rows which have identical text in 3 columns

    Hi Belinda, sure. I have a meeting in 5 min but will come back and give an example. If it helps, if there are two rows, each having "food, food, food" then we delete the second row. But if its "food, food, apple" and "food, food, sky" then those are not duplicates.

  4. #4
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: Delete all duplicate rows which have identical text in 3 columns

    OK I've attached a second workbook to illustrate some duplicate rows. They are highlighted. After the sorting/deleting, there should only be one row highlighted
    of each color.

    (If you see two yellow rows, the second yellow should be deleted).

    (I also replaced the workbook in my original post with this new version)

    Thanks!
    Attached Files Attached Files
    Last edited by jrtaylor; 10-28-2020 at 06:57 PM.

+ 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] Move data on duplicate rows (different columns) to one row and delete extra rows?
    By Sagwa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-01-2014, 08:25 AM
  2. Replies: 6
    Last Post: 03-04-2013, 12:03 AM
  3. Delete duplicate rows with same data in certain columns for million rows
    By amlan009 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-08-2012, 12:53 AM
  4. Delete Duplicate Rows Based only 2 columns.
    By ebbo in forum Excel General
    Replies: 2
    Last Post: 02-18-2010, 11:27 AM
  5. Deleting duplicate rows when ALL columns are identical?
    By Sean Anderson in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-10-2007, 11:28 AM
  6. [SOLVED] REQ - Help with merge/delete of duplicate/identical cells
    By Mark in forum Excel General
    Replies: 1
    Last Post: 05-04-2005, 08:06 AM

Tags for this Thread

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