Carriage Return in Microsoft Excel

Today, a colleague asked me to help him manipulate a spreadsheet to make it easier and faster for him to turn into an HTML table.

He had a spread sheet consisting of 10 or so columns, one of which housed a list of product codes, separated by semi-colons, in a single cell. What he wanted to do was keep the product codes in a single cell, but instead of having them separated by semi-colons, he wanted them each on their own line.

After some initial head scratching, and a little Googling, I came across the ALT command for a carriage return – Bingo! Once I had this, all that was required was a find replace to replace against the column to replace semi-colon with a carriage return (ALT + 010).

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Changing the colour scheme in Excel 2010

Not a technical post from me today, but not a lot of people seem to know that Microsoft Office has several colour schemes available – Blue, Silver, and my personal favorite, Black.

To choose the colour option that you’d like, open Microsoft Excel > Click File > Options (Near the bottom in the menu on the left hand side) > On the General tab you can select your colour scheme:

Excel Colour Theme

Excel Colour Theme

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Finding Distinct Values in Excel 2010

This is something that I’ve just found myself, and I wish I would have known about it a few days ago when I was asked to extract a distinct list of values from a column.

Start off by highlighting the range of data that you would like to find the distinct values in, I’ll be using the below simple sample data for the purpose of this post:

Example Data

Example Data

On the “Data” tab of Excel 2010, find the “Remove Duplicates” button:

Remove Duplicates Button

Click the button, and the Remove Duplicates Dialogue will appear:

Remove Duplicates Dialogue

Remove Duplicates Dialogue

On the top right of the Remove Duplicates Dialogue box, you’ll see a check box – “My data has headers” – you can toggle this check box depending on whether or not you have headers in your data. I’m going to leave it checked for the purpose of this example. The check box “Column” is the name of the column I’m removing duplicates from, if multiple columns are selected in the first step – they can be managed from here.

Once you’re ready, click OK, and the below message will appear – giving you a few details about what has occurred:

Remove Duplicates Message Box

Remove Duplicates Message Box

The result:

Remove Duplicates Result

Remove Duplicates Result

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Excel VLOOKUP with Wildcard

Someone asked me to give them a hand with a VLOOKUP yesterday, but they wanted to do a wildcard search on a field to find the best match in a field on another work sheet.

They essentially wanted to do the equivilent of a SQL LIKE clause, but using Microsoft Excel.

The way I did this was by using the Lookup_value as a wildcard by wrapping it in asterisks:

=VLOOKUP("*"&A3&"*",'Full Names'!A2:A228,1,FALSE)

As I’m using a cell reference (A3) as my Lookup_value, I have to concatenate the wildcard symbol (*) to the value by adding “*”& to the left, and &”*” to the right of the reference.

Setting Range_lookup to TRUE wouldn’t have worked, because we didn’t have a range of values that the Lookup_value could fit into. We had a string that would have been part of another sting in the Table_array.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)