?
Solved

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

Posted on 2016-10-02
12
Medium Priority
?
986 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 49

Expert Comment

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

Expert Comment

by:Experienced Member
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 21
ID: 41825757
oftentimes this has to do with spaces, tabs, or some other non-printing characters
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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 21

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
0
 
LVL 81

Accepted Solution

by:
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.
2
 
LVL 21
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

771 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