SOLVED

Remove Duplicates - Weird Behavior

%3CLINGO-SUB%20id%3D%22lingo-sub-1648904%22%20slang%3D%22en-US%22%3ERemove%20Duplicates%20-%20Weird%20Behavior%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1648904%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20one%20column%20with%20200%20lines%20of%20data%20in%20it.%26nbsp%3B%20Let's%20call%20it%20range%20P33%3AP232.%26nbsp%3B%20I%20want%20to%20remove%20all%20the%20duplicates%20in%20that%20data%20but%20keep%20the%20original%20data%20where%20it%20is%2C%20so%20I%20copy%20that%20range%20and%20paste%20it%20(as%20values)%20to%20cell%20DD18%2C%20then%20remove%20duplicates%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20problem.%26nbsp%3B%20I%20verified%20using%20a%20column%20filter%20on%20the%20original%20data%20that%20there%20are%20twelve%20unique%20values%20in%20the%20data.%26nbsp%3B%20When%20I%20run%20the%20remove%20duplicates%20function%2C%20it%20says%20that%20twelve%20unique%20values%20remain%2C%20but%20I%20see%20only%26nbsp%3B%3CEM%3Efive%20of%20them%3C%2FEM%3E%2C%20with%20no%20sign%20of%20the%20other%20seven%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20if%20I%20copy%20that%20same%20data%20range%20to%20CQ1%2C%20the%20remove%20duplicates%20command%20works%20properly%20and%20I%20see%20all%20twelve%20unique%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20tell%20me%20what's%20happening%20here%3F%26nbsp%3B%20It%20makes%20no%20difference%20whether%20I%20paste%20with%20Ctrl%2Bv%20or%20right-click-paste%20as%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sorry%20I%20can't%20provide%20a%20workbook%20example%20of%20this%20right%20now%20-%20needs%20some%20scrubbing%20before%20I%20can%20post%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1648904%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1648961%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20Duplicates%20-%20Weird%20Behavior%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1648961%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F786314%22%20target%3D%22_blank%22%3E%40HN_Energy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20HN%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20first%20inclination%20is%20to%20ask%20if%20there%20were%20any%20hidden%20rows%20where%20you%20are%20pasting%20to%20or%20from.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have one column with 200 lines of data in it.  Let's call it range P33:P232.  I want to remove all the duplicates in that data but keep the original data where it is, so I copy that range and paste it (as values) to cell DD18, then remove duplicates there.

 

Here's the problem.  I verified using a column filter on the original data that there are twelve unique values in the data.  When I run the remove duplicates function, it says that twelve unique values remain, but I see only five of them, with no sign of the other seven values.

 

Now if I copy that same data range to CQ1, the remove duplicates command works properly and I see all twelve unique values.

 

Can anyone tell me what's happening here?  It makes no difference whether I paste with Ctrl+v or right-click-paste as values.

 

I'm sorry I can't provide a workbook example of this right now - needs some scrubbing before I can post it.

2 Replies
Highlighted
Best Response confirmed by HN_Energy (New Contributor)
Solution

@HN_Energy 

Hi HN,

 

My first inclination is to ask if there were any hidden rows where you are pasting to or from.

Highlighted

I was thinking someone was going to ask that. I checked for it initially and thought there we no hidden rows. After checking again, there were hidden rows >_< Wow what a duh answer. Thank you for your help!