+ Reply to Thread
Results 1 to 8 of 8

"Remove Duplicates" not working despite "Conditional Formatting" finding the dupes

  1. #1
    Registered User
    Join Date
    07-21-2019
    Location
    Florida
    MS-Off Ver
    2016
    Posts
    9

    "Remove Duplicates" not working despite "Conditional Formatting" finding the dupes

    Using Office 2016
    Columns A/B are using Conditional Formatting and displaying the Duplicates.
    Columns D/E are the exact same paste from the exact same source, pasted in as "Values"
    I've pasted from Excel to Excel. Notepad to Excel. Wordpad to Excel. Word to Excel. Always doing the paste as Values.
    A/B works and highlights the Dupes, but when I try "Remove Duplicates" on D/E (or A/B) it always says that there are "No duplicate values found"
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,950

    Re: "Remove Duplicates" not working despite "Conditional Formatting" finding the dupes

    The "Remove Duplicates" feature only removes the duplicates within the same column.

    Conditional Formatting looks at the entire selected range (which in this case is $A$1:$B$137).

    If you want to remove duplicates, you can put everything into one column, then run the remove duplicates feature.

  3. #3
    Registered User
    Join Date
    07-21-2019
    Location
    Florida
    MS-Off Ver
    2016
    Posts
    9

    Re: "Remove Duplicates" not working despite "Conditional Formatting" finding the dupes

    Huh...I thought that it looked at both side since it shows both sides on the selector.
    The issue is that I want to keep the data in A, but remove the dupes that are in A in B.
    Like say I have
    athert:99.0 hunter:99.3
    raiderrose:99.0 shaman:99.2
    alordkirk:99.0 raiderrose:99.0
    lili:76.0 carl:99.2
    hosokawa:99.2 nimue:72.0

    I want B to be

    athert:99.0 hunter:99.3
    raiderrose:99.0 shaman:99.2
    alordkirk:99.0 carl:99.2
    lili:76.0 nimue:72.0
    hosokawa:99.2

    I'd found these directions when I searched for "excel remove specific duplicates"

    Remove duplicate values
    Select the range of cells that has duplicate values you want to remove. Tip: Remove any outlines or subtotals from your data before trying to remove duplicates.
    Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.
    Click OK.

    BUT I guess that meant to "not search those columns." I took it to mean what I wanted it to do.

  4. #4
    Registered User
    Join Date
    07-21-2019
    Location
    Florida
    MS-Off Ver
    2016
    Posts
    9

    Re: "Remove Duplicates" not working despite "Conditional Formatting" finding the dupes

    Ok, found another post via Google with an inelegant work around that pretty much says the same as the suggestion 63falcondude gave.
    Take the data from B, post it directly below A and do the remove Duplicates.
    Then just take from where A was supposed to end and Cut/Paste back to B. Remove Dupes works sequentially so it won't toss something higher up 1st.
    Like I said, inelegant. But at least I can work that into a Macro and have it do it that way.
    This sure wouldn't work with more than 2 Columns.
    Kinda pointless of Remove Dupes to not work across all highlighted Rows/Columns.
    Last edited by Vitae; 10-21-2019 at 02:05 PM.

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,950

    Re: "Remove Duplicates" not working despite "Conditional Formatting" finding the dupes

    If you are okay with a VBA solution (I can't help here), I'm sure there is one that can remove duplicates in an unstructured range like you have here without putting everything into one column.

    The remove duplicates feature does work across multiple rows/columns but not how you are thinking.
    It is built to remove duplicate lines of data in a structured data layout (1 header row, then each row of data beneath it containing multiple "linked" columns).

    Having your data structured like this (where adjacent columns of each row belong together) will make it easier to work with Excel.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,739

    Re: "Remove Duplicates" not working despite "Conditional Formatting" finding the dupes

    If you want to do it by formula, rather than manually copy/pasting etc., you can first of all insert a new blank row at the top of your file, so that your data now starts on row 2, then you can use these formulae in the cells stated:

    F2: =IF(COUNTIF(A:A,B2),"-",MAX(F$1:F1)+1)

    G2: =IFERROR(INDEX(B:B,MATCH(ROWS($1:1),F:F,0)),"")

    Copy both formulae down to the bottom of your data. You can fix the values in column G, then delete the other columns (easy to include in a macro).

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    07-21-2019
    Location
    Florida
    MS-Off Ver
    2016
    Posts
    9

    Re: "Remove Duplicates" not working despite "Conditional Formatting" finding the dupes

    Wrote up a quick Macro
    Please Login or Register  to view this content.
    What's this doing:
    It's going to the 1st cell of your duplicates (In my case N2 and assuming of course that the ones you want to delete are on the right side). Selecting and cutting them.
    Then, again, assuming that you have some extra data in L, it's going to the bottom of it, moving 1 more down and to the right and pasting in the previously cut data.
    Performing the Remove Duplicates function.
    Again, moving to L and 1 down/right
    Selecting and cutting from there to the end and pasting it back into the 1st cell of the column that shouldn't have any dupes.

    The reasoning for the L column is cause I have a "Top 60 Heroes" list and the #'s 1 thru 60 in L. So, basically it goes to L and then like I said, goes down then 1 more down and then takes a left to the open cell.
    You could alter this if you don't have any data in the left most cell by changing the 1st one to
    Cells(Rows.Count, "L").End(xlUp).Offset(1, 0).Select
    That will just go down and down 1 more to the open cell.
    And then replace the 2nd one with
    Range("M62").Select
    Where M62 is the 1st cell after the ones you wanted to keep.
    I have a header on M1 so that's the reason for the 62 and not 61. And for some reason, my mind, when I see that I'm on row 61, immediately thinks that I have 61 heroes listed even tho I KNOW I only have 60. SO, I did the #'s to keep me centered.

    I'm sure I've kinda explained this badly because what goes on in my head doesn't always translate to my fingers
    Attached Files Attached Files
    Last edited by Vitae; 10-21-2019 at 03:20 PM.

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,950

    Re: "Remove Duplicates" not working despite "Conditional Formatting" finding the dupes

    I'm glad that you found a solution that works for you.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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: 13
    Last Post: 10-22-2019, 02:14 AM
  2. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. [SOLVED] VBA help needed to remove all "/" then replace with "-" from cell "B3"and "B5"
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-17-2014, 02:11 PM
  5. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  6. Replies: 2
    Last Post: 08-17-2012, 05:10 AM
  7. Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" not working
    By redders in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2011, 03:52 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