+ Reply to Thread
Results 1 to 4 of 4

Remove duplicates across multiple columns

  1. #1
    Registered User
    Join Date
    Teaneck, NJ
    MS-Off Ver

    Question Remove duplicates across multiple columns

    I have 7 columns that contain phone numbers.

    I want to remove duplicates across all columns, so that only one occurrence of a phone number will appear across all cells. I don't care which one of the duplicates is removed.

    I don't want to remove any rows.

    How can I do this?

    I could not find a VBA macro that worked.


  2. #2
    Forum Contributor
    Join Date
    Omaha, Nebraska USA
    MS-Off Ver
    Excel 2010

    Re: Remove duplicates across multiple columns

    Hi moishier,

    The attached workbook has an example of some code that will check columns in the same row and delete any duplicates. The code is given below in case the workbook is in a version you can't read.

    Please Login or Register  to view this content.
    Hope that helps,

    Attached Files Attached Files

  3. #3
    Forum Expert mike7952's Avatar
    Join Date
    MS-Off Ver
    Excel 2007, Excel 2016

    Re: Remove duplicates across multiple columns

    This should do it

    Please Login or Register  to view this content.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  4. #4
    Registered User
    Join Date
    MS-Off Ver
    Excel 2013

    Re: Remove duplicates across multiple columns

    Dear fellow Excellers and Readers,

    Please note the code differences above.

    Both will remove duplicates - BUT - will offer different results.

    Note 1: djbomaha Code only removes duplicates from any ONE column, i.e. despite the number of columns in your 'range', the code will look down each columns (despite referring to 'Rows') and will remove duplicates in each column....then moves onto the next column and so on and so forth. This code set is rigid insofar as the user must change values for i and j according to their individual worksheet or problem. This is EXCELLENT code should you wish to control a set within a smaller area of your worksheet.

    Note 2: mike7952 Code removes ALL duplicates from the entire range. So.....If you have say, a number 12 located in "A1", and "B3", and "AZ23"....it will remove all but one of those entries.

    Personally, I prefer Mikes code purely because it appears to have answered the question a little more thoroughly. Having said that, both Code Examples should be excellent examples for learning the differences between results when using For-Next loops.

    The only thing missing from each of the code sets offered above are code execution speed enhancements....but there are thousands of posts across the web on just that topic.....so, move on.

    Mike - simple, nice, clean - well done!

    --If You Can't Measure It, You Can't Manage It--

+ 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] Merge .csv columns and remove duplicates
    By Catman Begins in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-30-2015, 07:13 AM
  2. [SOLVED] Macro to copy data from multiple columns to new location and remove duplicates
    By excel151515 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-23-2015, 04:21 PM
  3. Find or Remove Duplicates in Columns
    By dwc in forum Excel General
    Replies: 2
    Last Post: 05-11-2014, 10:38 PM
  4. Remove Duplicates in columns
    By naveenmarapaka in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-31-2014, 11:10 AM
  5. Replies: 2
    Last Post: 01-13-2014, 10:52 AM
  6. Compare 2 columns and remove duplicates in both columns when found
    By 1dtms in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-11-2013, 02:19 AM
  7. Remove duplicates from multiple columns using VBA
    By rbs123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-03-2013, 03:48 AM


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