Consultants are in the business of taking messy, unorganized data and turning it into information, and hopefully, some insights. Here is a simple example of excel clean up, and the steps to copy, paste, filter, sort, and cleanse data. For most consultants, the data cleansing would 7-10 minutes (takes some trial and error) and the graphics would be another 10 minutes, if (s)he knew what graph they wanted to make.

Challenge

  • Go to this website which re-sells giftcards. (404 Error, they have since gone out of business)
  • Sort so you can see the 460+ vendors ranked highest to lowest discount.

Solution

Here are the outputs.   Sorting of the 465 retailers from highest discount to lowest.

465 retailers

Unsurprisingly, the retailers with the lowest discounts are the generalist retailers and gas stations. After all, who does not need something from BP or Walmart?

Lowest discounts

Steps #1 – 12

We have lots of consultants on this websites, and other left-brained people so this maybe basic for some. Also, there are likely easier, smarter ways to do this, but this is for education, so don’t be snobby. Some people might learn from this.

Filtering, sorting, copy, pasting.  Analyst basics.  Here we go. . .

http://www.cardcash.com/merchants/

step 1

step 2 and 3step 4-5step 6-7step 8

step 9

step 10-11

step 12

Practice makes perfect

Nothing is worse than a slow consultant, so practice. Hope this was helpful. Note that the website is always updating their inventory of discounted cards, so your results might be different (if you don’t do it on 10/2/2014).

If you want a copy of the starting excel (step #1) and the final clean one (step 12), leave me a message below, and I will send you the excel.

 

Related posts:

Share This