+ Reply to Thread
Results 1 to 2 of 2

Help clearing duplicate values across columns but not deleting rows

  1. #1
    Registered User
    Join Date
    03-03-2021
    Location
    Jacksonville, FL
    MS-Off Ver
    Mac Excel
    Posts
    1

    Help clearing duplicate values across columns but not deleting rows

    Hello,

    I have contacts on a sheet that have multiple phone numbers for each contact going across the columns on each row. Many of the numbers are duplicate and I want to clear the value of the 2nd + instance while leaving the 1st instance.

    I cannot use the remove duplicate button in excel because it doesn't search horizontally and I don't want to remove any rows. Does anyone know the best way to clear the value of the cell after the 1st instance? I need it to search across the range AC2:AT6900

    Thanks so much for your help

    Example of file

    Bold is what needs to be cleared
    First Name Last name Address phone 1 phone 2 phone 3 phone 4
    Mike Smith 1234 St 9999999999 8888888888 9999999999 9999999999
    Peter Pan 231 Ave 6666666666 6666666666 2222222222 3333333333

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,533

    Re: Help clearing duplicate values across columns but not deleting rows

    Hello alliev and Welcome to Excel Forum.
    If you want to put the information into a new table then you could use a formula like: =IF(COUNTIFS($A2:A2,A2)=1,A2,"")
    For future reference you will usually get a quicker response if you attach an .xlsx file (instructions in the banner at the top of the page.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 1
    Last Post: 02-07-2014, 03:14 AM
  2. [SOLVED] Deleting multiple rows with duplicate values in one column
    By djh30 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-01-2013, 04:08 PM
  3. Deleting rows with duplicate cells across multiple columns
    By kaysee10man in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-25-2012, 10:50 PM
  4. Replies: 3
    Last Post: 07-11-2012, 05:13 AM
  5. Deleting rows for duplicate data in few columns
    By deepakborse in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-14-2010, 07:26 AM
  6. Replies: 1
    Last Post: 05-14-2009, 06:37 PM
  7. 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

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