Solved

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

Posted on 2016-10-02
12
299 Views
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.)
0
Comment
Question by:rgb192
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 46

Expert Comment

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

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).
0
 
LVL 19
ID: 41825757
oftentimes this has to do with spaces, tabs, or some other non-printing characters
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:rgb192
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.
Time-Card1.xlsx
0
 
LVL 81

Expert Comment

by:byundt
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.
0
 
LVL 81

Expert Comment

by:byundt
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.
0
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 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
0
 
LVL 81

Accepted Solution

by:
byundt earned 250 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.
2
 
LVL 19
ID: 41825800
cool trick, byundt ~
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 41826059
Brad,
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
0
 
LVL 81

Expert Comment

by:byundt
ID: 41826223
Jerry,
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.

Brad
1
 
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...  
Jerry
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

820 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