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).
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
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:
On the “Data” tab of Excel 2010, find the “Remove Duplicates” button:
Click the button, and the Remove Duplicates Dialogue will appear:
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 Result
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:
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.