+ Reply to Thread
Results 1 to 4 of 4

Compare Range & show duplicates as blanks

  1. #1
    Registered User
    Join Date
    02-02-2012
    Location
    london
    MS-Off Ver
    Excel 2019
    Posts
    33

    Compare Range & show duplicates as blanks

    I have several rows of numbers:

    7 8 9 10 11 13 14 15 17 18
    7 8 10 11 12 13 14 15 17 18 19
    8 11 13 14 15 16 17 18 19 20
    7 8 9 10 12 13 14 16 17 18 19 20 21

    If i choose numbers from the range above

    8 10
    12 13
    14 15
    19 20

    Then i would like another range that had the selected numbers removed & only showed what was available & looked like:

    7 9 11 17 18
    7 11 17 18
    11 16 17 18
    7 9 16 17 18 21

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

    Re: Compare Range & show duplicates as blanks

    I changed the arrangement of the numbers to be excluded so that they are in one row (7).
    The formula used is: =IFERROR(INDEX($A2:$M2,AGGREGATE(15,6,COLUMN($A2:$M2)/(COUNTIFS($A$7:$H$7,$A2:$M2)=0)/($A2:$M2<>""),COLUMNS($A9:A9))),"")
    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.

  3. #3
    Registered User
    Join Date
    02-02-2012
    Location
    london
    MS-Off Ver
    Excel 2019
    Posts
    33

    Re: Compare Range & show duplicates as blanks

    this works perfectly, thank you

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

    Re: Compare Range & show duplicates as blanks

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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] Count duplicates in a range ignoring blanks
    By bjnockle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-14-2021, 12:37 AM
  2. Replies: 9
    Last Post: 01-13-2020, 10:16 AM
  3. [SOLVED] Copy & Paste Columns to Specific Range while Removing Blanks & Duplicates
    By dondada82 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 04-15-2017, 11:31 AM
  4. Replies: 3
    Last Post: 06-25-2014, 03:28 AM
  5. Dynamic named range, ignoring duplicates and blanks, WITHOUT VBA
    By aimanti in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-23-2013, 06:13 PM
  6. Count Duplicates in a range - skip blanks
    By jhahes in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-08-2006, 04:50 PM
  7. [SOLVED] How do I compare 2 lists and show duplicates as a new list?
    By AnaBannana in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-07-2005, 12:06 PM

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