rgb192
asked on
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.)
Can you post a sample workbook that shows the problem?
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).
oftentimes this has to do with spaces, tabs, or some other non-printing characters
ASKER
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
I am also unable to Create Sparklines for I suspect the same reason as not being able to filter our duplicates.
Time-Card1.xlsx
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.
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.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
cool trick, byundt ~
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
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
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
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
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
Great approach...
Jerry