Manipulating and Massaging Data in Excel

re: Manipulating and Massaging Data in Excel

TechieBird — Wed, 12 Dec 2007 12:48:56 GMT

David/Chad

Great post - these are techniques I use all the time, and I frequently get asked to help colleagues 'clean' their data. Nice to see that someone has had time to put all this information together so now I have a place I can send people when I don't have time to help!

-TB 8>

re: Manipulating and Massaging Data in Excel

Chad Rothschiller — Wed, 05 Dec 2007 23:01:28 GMT

Charles - could you send an example of each type in a workbook? Use blogs.msdn.com/.../contact.aspx to start an email conversation and I can take a look.

--Chad

re: Manipulating and Massaging Data in Excel

Charles — Wed, 05 Dec 2007 22:41:02 GMT

I have a spreadsheet of email addresses that all appear to have the @ sign in them - but when some of them reach their destination they @ sign has been replaced with the HTML code %40. There is no visual difference between those that appear this way and those that don't.

Is there any way to clean this?

Thanks!

Charles

re: Manipulating and Massaging Data in Excel

David Gainer — Wed, 05 Dec 2007 19:30:19 GMT

msn999 - this is a known issue that we are hoping to fix in the upcoming SP1.

re: Manipulating and Massaging Data in Excel

msn999 — Tue, 04 Dec 2007 19:05:34 GMT

Sorry, newbees and don't know where to blog.

when I plot my data as an exponential curve in Excel, the Excel 2007 gave a wrong equation (y=18159e0.0236x)

while Excel 2003 (y=181596e0.0236x) give correct equation

Raw data:

x y

0 200000

25 302500

49 535833

74 1035000

96 1863333

re: Manipulating and Massaging Data in Excel

Chad Rothschiller — Thu, 29 Nov 2007 19:59:12 GMT

Excel Novice - I'd just copy the CLEAN function down the whole column. To cover multiple columns, just keep adding new columns of formulas off to the right, for as many as you need to clean.

Robert LaSalle - You're essentially talking about Data Quality, Cleansing, Standardization, and Deduplication. I have seen companies spend tens of thousands of dollars to clean up their marketing databases / address lists. It is just plain irritating to send someone a piece of mail 3 different times. It gives the impression you don't know who they are. You have a similar problem with names: is S. Thorpe the same as Steven Thorpe? I would say it depends. If the address is different, then certainly they’re not the same. If the address is the same, then, well, what if Steven has a wife Shelly? But at least you know it's the same household.

The real way to solve this problem is to first normalize the address (N becomes North, St becomes Street (in most cases), and so on) and parse it out into sub fields (like house number, direction, street name, street type). Then you can use matching rules to decide if they're the same. You can even weight certain fields as stronger indications of a match than others. e.g. it isn't that big of a deal if the direction is missing on 1 of the records you're comparing, but it certainly isn't a match if the street name doesn't match. To do all this, you'll need specialized software that might cost you upwards of $100K (see <a href="www.trilliumsoftware.com/" target="_new" rel="nofollow">http://www.trilliumsoftware.com</a> for an example of such software).

To do a one-off job in Excel, you're going to have to play with the data and eyeball it. You might consider making a copy of your data, then removing all the directional pieces from the address (N/S/E/W/NE/NW/SE/SW/North/South/East/West/etc), and maybe even the suffix data as well (Ave/Avenue/St/Street/Drive/Dr/etc). Then sort by those fields and look for duplicates, looking at first name/last name to help, and so on. Then go back to the original data to find and remove possible duplicates. You could also try sorting by last name then first name, and looking for duplicates there.

To quickly flag duplicates in a list, here's what I sometimes do. Say there are sorted addresses in A1:A10. In B2 I'd put this formula: =IF(A2=A1,"X",""), and then copy it down. The X will flag any duplicates and you can quickly work through them.

OK, enough on that topic!

re: Manipulating and Massaging Data in Excel

Robert LaSalle — Mon, 26 Nov 2007 15:36:37 GMT

I have an excel spreadsheet with 2000 lines of data. Some of the data is the same address. One may be writtes as 301 N Howeth St, one may be written 301 Howeth St, one may be written 301 Howeth. I want to have a way to run a script and find these similar rows and ask me if I want to delete any so I can get rid of the dups. Any help on this? rlasalle@mfgtx.com

re: Manipulating and Massaging Data in Excel

Excel Novice — Tue, 20 Nov 2007 16:21:45 GMT

Any way to use CLEAN to clean-up non-printable characters in the whole cloumn or from multiple columns?

re: Manipulating and Massaging Data in Excel

Excel Novice — Tue, 20 Nov 2007 16:16:21 GMT

Any way to use CLEAN to clean-up non-printable characters in the whole cloumn or from multiple columns?

re: Manipulating and Massaging Data in Excel

Charlie Ellis — Sat, 17 Nov 2007 02:21:37 GMT

Thanks for bringing the issue with Data Tables to our attention. This is a known issue and we're working on getting a fix out as a part of a future release. We'll update you on when the fix will be available when we have more information to share.