+ Reply to Thread
Results 1 to 6 of 6

Eliminating Duplicates of 3 digit numbers.

  1. #1
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Eliminating Duplicates of 3 digit numbers.

    Hello All,

    I have 300 - 3 Digit numbers that I need to eliminate the Duplicates.

    Keep in mind the 3 Digit numbers are considered Duplicate if a number like 356 is 365, 536, 563, 635, 653.

    A 3 digit number has 6 options to rearrange the number, so I need the formula to look at all scenarios of the 3 digit number so it can eliminate the duplicates.


    Thanks,

    Brian
    Attached Files Attached Files
    Brian

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Eliminating Duplicates of 3 digit numbers.

    Hi,

    is TEXTJOIN function available on your Excel?

    A not very elegant solution

    In L1

    =TEXTJOIN("",1,AGGREGATE(15,6,MID(B1,ROW($1:$3),1)+0,INDEX(ROW($1:$3),)))

    to be copied across in L1:U30


    If you could not exploit TEXTJOIN to concatenate the three digits in ascending order:

    =AGGREGATE(15,6,MID(B1,ROW($1:$3),1)+0,1)&AGGREGATE(15,6,MID(B1,ROW($1:$3),1)+0,2)&AGGREGATE(15,6,MID(B1,ROW($1:$3),1)+0,3)

    In V1:AD30 to check duplicates among text strings in L1:U30:

    =--(COUNTIF($L$1:$U$30,L1)>1)

    Conditional formatting in A1:K30 can read "1" in order to highlight duplicates.

    Regards
    Attached Files Attached Files
    Last edited by canapone; 04-27-2019 at 03:10 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Re: Eliminating Duplicates of 3 digit numbers.

    I was able to use Textjoin,

    Is there a way to only highlight the 2nd, 3rd, etc. Duplicates and keep the 1st one without being highlighted?

    It does not necessarily have to be a highlight or not, it can be anything that can show me anything after the 1st number.

    What im trying to do is see any of the 2nd, 3rd, etc. duplicates without making a mistake on deleting them. I need only one of them to show and the others highlighted or deleted.

    Thanks

  4. #4
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Eliminating Duplicates of 3 digit numbers.

    Hi,

    for convenience I've inserted a service blank row (row one) and now formulas start from column M (TEXTJOINs)

    In X2:AF30 countif are controlling duplicates on "the upper floors" and their left

    =--(COUNTIF($M$1:$V1,M2)+COUNTIF($L2:M2,M2)>1)

    Hope it could work: please triple-check results
    Attached Files Attached Files
    Last edited by canapone; 04-27-2019 at 04:59 AM.

  5. #5
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Eliminating Duplicates of 3 digit numbers.

    Hi again

    maybe you get more "visibility" from

    =COUNTIF($M$1:$V1,M2)+COUNTIF($L2:M2,M2)

    You can apply different formats to 2,3,4

    Or

    =COUNTIF($M$1:$V1,M2)+COUNTIF($L2:M2,M2)-1

    returns zero to first number and one to second occurence of the number, two to the third occurence...

    Forgive my poor English.
    Attached Files Attached Files
    Last edited by canapone; 04-27-2019 at 04:39 AM.

  6. #6
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Re: Eliminating Duplicates of 3 digit numbers.

    Thanks canapone , works perfect

+ 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: 04-18-2019, 01:35 AM
  2. [SOLVED] Macro to convert WBS single digit numbers to double digit numbers
    By LeanAccountant in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 06-09-2018, 05:25 AM
  3. Eliminating Duplicates
    By Trojnfn in forum Excel General
    Replies: 1
    Last Post: 05-09-2017, 02:41 AM
  4. Eliminating Duplicates
    By jb2235 in forum Excel General
    Replies: 1
    Last Post: 06-17-2015, 12:35 AM
  5. [SOLVED] Eliminating duplicates
    By thilag in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-27-2014, 02:59 AM
  6. [SOLVED] macro to extract 9 digit numbers as well as alpha numberic 9 digit numbers from txt file
    By Raju Radhakrishnan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2012, 10:15 AM
  7. Eliminating duplicates
    By maximpinto in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-25-2009, 06:07 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