Why am I getting "No Duplicate Values Found" on this spreadsheet when there very clearly ARE duplicates?

Posted on 2016-10-02
Medium Priority
Last Modified: 2016-10-12
(Using Excel 2010 if it matters, but I did not import this data from elsewhere so compatibility issue is unlikely.)
Question by:rgb192
  • 4
  • 3
  • 2
  • +3
LVL 52

Expert Comment

by:Martin Liss
ID: 41825750
Can you post a sample workbook that shows the problem?
LVL 101

Expert Comment

by:John Hurst
ID: 41825755
While you are posting a sample, this happens when two data elements are visually equal but very different data types (e.g. text and general number).
LVL 24
ID: 41825757
oftentimes this has to do with spaces, tabs, or some other non-printing characters
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.


Author Comment

ID: 41825765
I suspected it had something to do with the values in our table being the wrong data type. Should I Format the cells to something besides Number?

I am also unable to Create Sparklines for I suspect the same reason as not being able to filter our duplicates.
LVL 81

Expert Comment

ID: 41825769
Were you selecting your entire table A1:G10 and then using the Data...Remove Duplicates menu item? If so, Excel is looking row by row for duplicates, and not finding any instance where one entire row matches another entire row. You can prove this by copying one entire row and pasting it at the bottom of the table. The Remove Duplicates menu item will now remove that newly added row.

If you want Excel to search each column individually for duplicates, without care for the other columns, then you need to perform that search one column at a time.
LVL 81

Expert Comment

ID: 41825781
To create sparklines, I did the following:

1. Select A2:G10
2. Use the Insert...(Sparklines group) Line menu item
3. In the resulting dialog, select J2:J10 as the destination
These sparklines show the variation in each row from one day to the next

In step 3, if I had selected A15:G15 for the destination, the sparklines would have shown the variation in each column from one row's value to the next.
LVL 24

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 1000 total points
ID: 41825790
If data in that column was ever formatted as text, you may need to do this:

1. select column
2. DATA ribbon
3. Text to Columns, Next, Next, and Finish
LVL 81

Accepted Solution

byundt earned 1000 total points
ID: 41825796
If your data is text, it will not change appearance when you change the number format to show a different number of decimal places. Conversely, if the number of decimal places changes, you can be sure that the data are stored as numbers. Changing the format of a cell after the data have been entered does not change the value that is stored--it will remain text or numeric, the same as it was before the format change.

If you have a mix of text and numbers, you can convert text that looks like numbers into real numbers. The text to columns method described by Crystal works great on a single column. If you want to convert the entire table at once, however, that method returns an error message. Instead, do this:
1. Copy a blank cell
2. Select your data
3. Use the Paste Special menu item, click the arrow at bottom right of the icon, choose the Values option and also the Add option

The above method will convert a left-aligned text value like '1.5 into a right-aligned number like 1.5 that you can reformat into 1.5000. Blank cells and non-numeric text like "dog" will be completely unaffected. Existing numbers are unaffected.
LVL 24
ID: 41825800
cool trick, byundt ~
LVL 16

Expert Comment

by:Jerry Paladino
ID: 41826059
Does the Copy Blank cell; Paste Special Values & Add have any advantage over Copy a value of 1 and Paste Special Multiply?   I use the latter but your method is interesting.
Thanks - Jerry
LVL 81

Expert Comment

ID: 41826223
When you copy a blank cell and add, you don't need to put a value in a cell and then clear it. This saves two steps.

And when you do it programmatically, you can fearlessly use XFD1048576 as the blank cell. Copying it won't change the used range of the worksheet.

LVL 16

Expert Comment

by:Jerry Paladino
ID: 41826373
Thanks Brad!  I have a VBA routine on my QAT that uses the multiply 1 technique but I like your variation.  Think I will rewrite it.

Great approach...  

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This is an article on how to answer questions, earn points and become an expert.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question