Solved

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

Posted on 2016-10-02
12
66 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 45

Expert Comment

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

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
 

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 80

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 80

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 80

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 80

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now