Tables Part 6: Removing duplicates from tables (and ranges) of data

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

Harlan Grove — Sat, 05 Nov 2005 02:10:53 GMT

In current versions when one uses advanced filters to remove duplicates, all fields in the result (so all fields in the source range if filtered in place) are included when checking for duplicates. Will XL12 default to all fields selected in the new dialog?

Also, using advanced filters, one has the option of copying the filtered data to a different location. Will that be an option in XL12 using the new UI?

As for removing duplicates, presumably this is top-to-bottom, so the topmost record of possibly many duplicates remains and subsequent duplicate records deleted.

One thing that screws up unique record filtering is visually duplicate records that have different amounts of leading or trailing spaces in various fields. It's not always practical simply to remove all spaces, and Excel currently provides no means other than the TRIM function and macros to remove only leading/trailing spaces. It'd be VERY USEFUL if this new feature included a checkbox to ignore (or not) leading and trailing spaces (and define space liberally as either characters with decimal codes 32 or 160).

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

David Gainer — Sat, 05 Nov 2005 02:53:51 GMT

Hi - yes, all fields selected by default. The feature removes data in place. Good feedback on the spaces - as the feature currently sits, you would need to use the extra column.

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

Rob van Gelder — Sat, 05 Nov 2005 03:10:55 GMT

Nice work on making this quicker.
Unique Advanced Filter is something I use a lot - and, you're right - there are a lot of clicks to get there.

The ability to select just some columns seems, at first, like a useful feature. Once I started thinking, I dont think I'll use it much.
The problem is that some of your checkout times are lost.
I cant think of a time where I've wanted to destroy remaining values but just keep the first.

It would be more useful if there were options for what to do with the remaining columns.
Such as:
Use first, use last, sum, min, max, average, join with comma separator, join with <user defined separator>

In your example, I might have wanted to keep the min(checkout) and max(checkin).

Regarding Harlan's comments (are you renaming to the David and Harlan blog yet? ;)

Agree there should be a "copy filtered to" feature.

On the autotrim checkbox:
I would not like to see that feature. In my experience, the users who would use that feature are the very same who let untrimmed strings slide in their raw data - which leads to all sorts of integrity issues later.
Better to have those features in a 'data cleaning' window which concentrates such features. eg. trim spaces, Proper Case, alphanum only, text stored numbers to real numbers, etc...

Nice work - I cant wait to try the beta...

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

Jean Martineau — Sat, 05 Nov 2005 03:25:43 GMT

Thank’s for the remove duplicates improvement.

I agree with Harlan that we need to keep
|the option of copying the filtered data to a
|different location.
In this case, I also need to keep an option to specify a Criteria range.

Jean

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

Dave Solimini — Sat, 05 Nov 2005 04:51:30 GMT

wow. it would be difficult for me to express how incredibly happy i am to see this feature improved! You folks look to be doing some amazing work. Hats-off!

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

Ken Clifton — Sat, 05 Nov 2005 05:40:29 GMT

I too would like to see options to move the data to a different location, sheet, file, etc.

Also have an option for each field you could pick keep first, last, Nth position.

Also be able to pick what each field will do like MIN, MAX, AVG, COUNT, SUM, OLDEST, NEWEST, etc.

That way if you had an employee with data you could average some columns, sum others, get the MIN, MAX what ever you might need with this one feature. This would be a GREAT selling point.

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

Biff — Sat, 05 Nov 2005 08:04:04 GMT

I'll echo what many have already noted:

Copying filtered data to another location is very useful, especially the unique values.

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

Orion Adrian — Sat, 05 Nov 2005 08:06:59 GMT

I love the data cleaning idea. Stuff like trim, change case, reformat, remove charactes (like ()$.-_, etc.) would be very useful ot me.

Orion Adrian

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

Roy — Sat, 05 Nov 2005 16:27:29 GMT

All the tables in your screenshots look great! I hope there's a new easy way to format them like that.

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

Harlan Grove — Sun, 06 Nov 2005 20:17:40 GMT

Rob van Gelder...
...
|Better to have those features in a 'data
|cleaning' window which concentrates such
|features. eg. trim spaces, Proper Case, alphanum
|only, text stored numbers to real numbers,
|etc...
...

I agree. If the goal of all these changes is the greatest good for the greatest number of users for the fewest keystrokes or mouse clicks, then someone on the development team need to realize that a very great deal of the data stored and manipulated in spreadsheets is text rather than numbers, and Excel provides very few tools for manipulating text that don't require using formulas in worksheet cells.

With respect to autotrim, it kinda exists already in Data > Text to Columns, which can be run using fixed width fields but only one field. Excel will strip off leading and trailing ACSII spaces (decimal character code 32) but not nonbreaking HTML spaces (decimal character code 160). This is pretty obscure.

There are also frequent questions about converting ranges to all upper case or proper case. The only practical approach currently is writing UPPER or PROPER formulas elsewhere, pasting their values on top of the original range, and clearing the range of formulas. A lot of work for most users.

If it's a good idea to relieve users of the complexity of using formulas in conditional formatting, wouldn't it also be a good idea to relieve them ot the complexity of using formulas in data cleansing?

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

Arun Philip — Mon, 07 Nov 2005 09:11:45 GMT

It would be nice if there's some way of highlighting the values and then getting a user approval prior to removing the duplicates. Basically, it would combine the functionality of Conditional Formatting's Highlight Duplicate Values, with the Remove Duplicates Feature.

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

headtoadie — Mon, 07 Nov 2005 21:38:55 GMT

Sometimes it is the duplicates that we *really* want and not the uniques.

For example, if I have a customer history file and I want to identify the repeat customers then it is the duplicates that I'm really after.

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

David Gainer — Tue, 08 Nov 2005 00:32:46 GMT

Hello folks - again, thanks for the comments and suggestions from everybody.

headtoadie - you can use conditional formatting (in Excel 12) to highlight dupes and then filter on the conditional formatting, for a total of about 3-4 mouse clicks.

everyone -

...
|Better to have those features in a 'data
|cleaning' window which concentrates such
|features. eg. trim spaces, Proper Case, alphanum
|only, text stored numbers to real numbers,
|etc...
...
|I agree. If the goal of all these changes is
|the greatest good for the greatest number of
|users for the fewest keystrokes or mouse
|clicks, then someone on the development team
|need to realize that a very great deal of the
|data stored and manipulated in spreadsheets
|is text rather than numbers, and Excel
|provides very few tools for manipulating text
|that don't require using formulas in |worksheet cells.
...

What are the most common 'data clensing' tasks you find yourself doing?

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

Harlan Grove — Tue, 08 Nov 2005 01:53:32 GMT

David Gainer...
...
|What are the most common 'data clensing' tasks
|you find yourself doing?

Myself? I don't use Excel for data cleansing.

My impression of users asking about this in newsgroups or stating problems that would be resolved by data cleansing, deleting leading and trailing ASCII spaces, deleting nonbreaking spaces, changing letter case and converting text that looks numeric into numbers are the most frequent data cleansing/conditioning needs. All except changing letter case could be accomplished using Data > Text to Columns.

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

XL-Dennis — Tue, 08 Nov 2005 12:10:49 GMT

David,

I fully agree with Harlan Grove. Per se the data cleansing should be less in view of the XML-era and general development but it tend to be the opposite.

The keyissue is that many corporate central systems in use can create, as output, so called Excel-files (core textfiles with the xls-extension) which looks great on it's face but create a demand for cleaning up before anything can be done with the files. It also involve the issue with dot and comma as separators for values.

Of course we can argue that this is something that the system vendors should solve but usually this is not a keyissue for central IT-departments in their dialogs with the vendors. In the end it's up to to the end-users to solve it in Excel which is reflected in newsgroups and public forums.

Kind regards,
Dennis

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

Nigel Harper — Tue, 08 Nov 2005 13:52:18 GMT

Dave,

I think it is more important to ask why we end up with duplicates in the first place. XL-Dennis rightly points out that many text files created by exporting data from other apps and renaming with an xls extension often contain extra characters and confuse numeric text and numbers.

Additionally gadgets containing contacts to be imported into Outlook are a major bugbear. When I worked at a large investment bank, part of my duties were to be the in-house gadget guru. Almost every new banker arrived with his/her gizmo. This generally contained around 2500 contacts made up of generations of contacts from the various preceding gadget of the day. Each one, sometimes even each model from the same manufacturer, has different ways of storing 'multi-field' fields such as address, the synchronising software is not much better. Assistants would tear their hair out trying to filter and sort for duplicates that had built up over the years sometimes because of a linefeed or tab character etc. Inevitably it would come to me in the form of a spreadsheet to use text function formulas to sort things out.

Harlan is correct that much can be done with text to columns but there is more that it could do. To be able to evaluate rows by accounting for leading, trailing and instances of strings, automatic trimming and cleaning, numeric evaluation and case sensitivity WITHOUT actually having to change the contents would be very useful.

'Duplicates' is actually a bit of a misnomer, most of the time the reason there are duplicates is because the similar rows are not actually duplicates. Hope this helps,

Nigel

re: Tables Part 6: Removing duplicates from tables (and ranges) of data

headtoadie — Tue, 08 Nov 2005 18:38:51 GMT

On data cleaning...one area where we experience problems is with text strings that contain a single "E" (ex. 72E0050). Excel tends automatically convert these to scientific notation. Once it is converted, the only way to get it back to original format is to retype it.

Revealed - Thousands of leads in 6 minutes and 37 seconds

Revealed - Thousands of leads in 6 minutes and 37 seconds — Tue, 22 Jul 2008 08:23:54 GMT

PingBack from http://crewealthtraining.com/members/?p=52

Microsoft Excel Tables Part 6 Removing duplicates from tables and | Outdoor Ceiling Fans

Sun, 31 May 2009 19:39:48 GMT

PingBack from http://outdoorceilingfansite.info/story.php?id=5628

Microsoft Excel Tables Part 6 Removing duplicates from tables and | Outdoor Ceiling Fans

Sun, 31 May 2009 21:45:24 GMT

PingBack from http://outdoorceilingfansite.info/story.php?id=23264

Microsoft Excel Tables Part 6 Removing duplicates from tables and | Hair Growth Products

Wed, 10 Jun 2009 05:44:25 GMT

PingBack from http://hairgrowthproducts.info/story.php?id=7467

Microsoft Excel Tables Part 6 Removing duplicates from tables and | adirondack chairs

Fri, 19 Jun 2009 12:17:52 GMT

PingBack from http://adirondackchairshub.info/story.php?id=3737

Microsoft Excel Tables Part 6 Removing duplicates from tables and | patio set

Microsoft Excel Tables Part 6 Removing duplicates from tables and | patio set — Fri, 19 Jun 2009 13:14:51 GMT

PingBack from http://patiosetsite.info/story.php?id=663