by Cloud for Good Blog | Jun 23, 2015 | Blog
There are times, in the life of a database administrator, when you just have to go back to good ol’ Excel. We use it for taking data apart, for finding duplicates and flagging them, for sorting up and down to find anomalies and errors. We use it to replace whole columns of one number with another. Francis Scudellari from the Cloud for Good team started this discussion with this excellent post, Keeping it Clean: The Data Import Basics. Check that out to get started thinking about your data importing. One of the most common uses, however is for putting cells TOGETHER. Usually this happens when I have a FirstName column and a LastName column and we need a Full Name column also. Sometimes we need to put one line of the address together with the second line. Excel calls this function “concatenation,” but I always think of it as “sticking things together.” Let’s see just how easy this is. We start with a name issue. Here we have a list of names. Some are husbands and wives, but for the first example, all we want to do is get full names for everybody. Say I’m addressing envelopes for an invitation and I want to invite each individual. I’ve exported my contacts pages, and I’m looking at split names. I need to find a way to get them all into one cell. Click the formula button above your table. It looks like fx. Notice that I’ve already created the “destination” column. Your concatenation needs someplace to go! Insert function comes up and CONCATENATE is at the top of the list!...
by Cloud for Good Blog | Apr 7, 2015 | Blog
This is a cross post from the Cloud for Good blog. Filters are kinda sorta great… What’s the first thing I do whenever I open a new spreadsheet? I filter it. Why? Because filtering is a great way to quickly analyze your data. Need to see which columns are and aren’t populated? Filtering is for you. Have users who don’t like to enter the same value, the same way twice? Use a filter to locate the inconsistencies. You may have used Sorts to handle similar scenarios, but filters give you more control. Using them, you can also avoid having to constantly rearrange the rows of your data, lowering the odds of making a mistake. What cool things can you do with filters? Say Goodbye to Empty Columns – A column with no data in it is a column you don’t need to import. I like a lean import file, and that means removing the clutter of blank columns. Spot a Bad Date or Phone – Filtering is a great way to quickly spot bad or missing dates, which can cause those pesky import errors. It can also clue you in to phone numbers that are inconsistently formatted. Make Sure It’s the Right List You’re Picking – Picklists in Salesforce are not very forgiving. It may be a small dot, but “Jr” and “Jr.” are not the same suffix. Filters can help you see those data vagaries. Then, a Find and Replace will equal a sparkly clean picklist. Work the Combinations – Filters are also a great way to check consistency across columns, for example, to check the data integrity...
