99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Sorting Birthdates Into Their Respective Months In Excel
Mon 24th October 2011
To coincide with the launch of their new product, a soft toy called Benny the Big Orange Bear, they intend to email each of their clients a free horoscope reading. The mail begins,
'As you will be celebrating a birthday this month, we are pleased to offer you a free horoscope reading by our resident astrologer, Charlie Cosmos'. As this is a scam, the horoscopes are identical for each client, and these are emailed in batches at the start of each month to everyone on the list with a forthcoming birthday.
The horoscope informs the clients that their lives are about to change for the better. After the waffle, the following good luck signals are listed:
Lucky Letter: B
Lucky Colour: Orange
Lucky Animal: Bear
The marketing boys hope that this blatant manipulation will have their more superstitious clients queueing up to buy a product that ticks all of their good fortune boxes. In order to get the scam under way, they must first separate the client base into their respective birth months. With over a thousand clients on their books, all stored in an Excel worksheet, sorting them by the month of their birth will be a long, arduous task. At least it would have been were Excel not able to do it for them.
There are several ways to do this but the one I will demonstrate here is a straightforward procedure that involves formatting cells and then sorting data. Again you might like to enter a few random birthdates into a column to try this.
Select the column containing birthdates and click on Format. Choose Cells from the list to open up the Format Cells dialog box. Select the Number tab and choose Custom from the Category box. In the type box, type 'mmmm'. This is not an indication that your coffee tastes nice, but an instruction that you want your list to be sorted by month only. These letters should be lower case. Click OK and the contents of the column will now show only the month of birth of each client. You may be alarmed that the original birthdates have disappeared, but they are still there.
To sort the months into groups, select the entire list and click on Data and select Sort from the list. In the Sort By box, specify that you want to sort the data contained within column C, and then click the Options button. This will open up the Sort Options dialog box. From the First Key Sort Order drop-down list, specify that you want to sort by months by choosing January. February etc. Click OK on both the Sort Options and Sort dialog boxes. The list will now be sorted into months so that groups of clients with birthdays in the same month are grouped together.
After sorting the list into month of birth, the scammers above would make a copy to be used specifically for the horoscope scam. To restore the birthdates on the original sheet, select the column again and go to Format and select Cells. Select the Number tab and choose Custom from the Category box. In the type box, click the arrow and select dd/mm/yyyy from the list. Click OK and the birthdates will reappear.
Excel is an amazing application that seems to have a solution to every problem that is thrown at it. Getting to grips with its vast capacity for manipulating data, perhaps through a training course, would be adding a considerable string to your bow. Oh, and it's written in the stars that a scam involving fake horoscopes is doomed to fail.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Sony/ATV Music Publishing
Brilliant. Covered all the basics and some additional areas which were extremely helpful. It will make tasks at work so much easier now I can find my way around Excel 2007.
No suggestions- this course was great. I learnt a lot and was able to apply what I had learnt to my work straight away.
PowerPoint Intermediate Advanced
Great presentation by trainer on how to use ppt effectively.