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

(Using Excel 2010 if it matters, but I did not import this data from elsewhere so compatibility issue is unlikely.)
LVL 1
rgb192Asked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
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
 
Martin LissOlder than dirtCommented:
Can you post a sample workbook that shows the problem?
0
 
JohnBusiness Consultant (Owner)Commented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
oftentimes this has to do with spaces, tabs, or some other non-printing characters
0
 
rgb192Author Commented:
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
 
byundtCommented:
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
 
byundtCommented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
cool trick, byundt ~
0
 
Jerry PaladinoCommented:
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
 
byundtCommented:
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
 
Jerry PaladinoCommented:
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
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.

All Courses

From novice to tech pro — start learning today.