Excel Tip

Going back to my favorite data source the UN statistics division I wanted to show how to link two large data sets in excel.

Data set 1 – Total population, both sexes combined (thousands)
Data set 2 – Wages and salaries

First I downloaded each set as a .CSV( Comma separated values) file.  Next I move a copy of each worksheet to the same workbook and save.   I do this because I will want an original copy of the raw data to look at to check what I am doing sometimes and to make the linking easier.   You can link across workbooks, but that is a little more complex.  Also any time you are dealing with large sets of data in excel you should save often.  Excel handles large data like a Vegas juggler handles bowling pins it does it well but sometimes everything crashes and it crashes quickly.  Saving often will keep you from having to try and pick up the pieces.

Next I will remove the duplicates based upon country and year.  I could leave them in but I know from experience that I want a simple end result which means getting rid of the stuff.  In excel under data there is a remove duplicates button.  Now I want to combine the data into one table so that I could pivot it for patterns.  Also I need to clean up the two data columns so the stuff I am comparing in the function match.  In plain speak if I have Albania in one tab I need to have Albania in the other.  Note if I have “Albania” in one tab and “Albania ” in the other it will fail because of the space on the end.   Always clean the data first.  Once you put in a complex function it will recalculate and eat up cpu cycles slowing you down greatly if you are still cleaning data.  So in the Wages tab I will go to the last column and add the following formula.   Column H Row 2

=INDEX(UNdata_Export_population!$D$3:$D$5524, MATCH(1,(UNdata_Export_Wadges!A2=UNdata_Export_population!$A$2:$A$5524)*(UNdata_Export_Wadges!C2=UNdata_Export_population!$B$2:$B$5524),0))

Then hit ctrl+shift+enter  to make it an array function

One advantage of the Excel index and match functions is that the lookup value can be in any column in the array, unlike the VLOOKUP function, in which the lookup value must be in the first column.

Now what this did is match the data in “Wages and salaries” by country and year to the population for that country and year in “Total population, both sexes combined (thousands)”  when there was a match it would copy the population and put it in the cell.  Copying the formula all the way down column H and you will have the population for the country year and occupation.

Advertisements
Posted in Uncategorized | 1 Comment

Gmail

Ever wonder in Gmail how to show all the e-mails from one person? Here is how you do it.

In the Search Mail box type “list: (email address)” hit enter

Example to show all email from jon.smith@HTC.com you would type “list: jon.smith@HTC.com” and hit enter. Then you would see all email from and sent to jon.smith@HTC.com

Posted in Uncategorized | Leave a comment

How the military goes about building a new gun

Flowcharts are like chocolate they are good in small amounts but try and eat to much at one time and you will end up sick. Below is an example end to end process flow from the Department of Defense.

Most people not familiar with flowcharts will see a complete mess. The truth is this level of flow chart lets you know two things. First the Department of Defense has well documented process flows. Second there is no reason to flow chart in this level of detail such a large life cycle. When you do this items that are not as important in the process flow get the same weight or more as items that are critical. Notice how many times and the size of the Initial Product Baseline occurs. In fact multiple process groups or documents are repeated throughout the graphic making it look bigger then it really is. The flash version available at the military website https://ilc.dau.mil/default.aspx (Note: this site requires the installation of a military certificate to your browser) does a better job of putting things in context.

The question that I have to ask is with such well defined process how is it that the military has a history of going over budget? Leave your creative comments below.

Posted in Uncategorized | Leave a comment

PMP study hint

PMPal is an android phone application that is very useful in reviewing the Project Management Framework(5 process groups, 9 knowledge areas, and 42 processes).  The price is right at free and it is a good reminder even if you have passed the PMP to go over the ideal process flow to sharpen your abilities to get your projects as close to that ideal as possible.

QR Code

Posted in Uncategorized | Leave a comment

What a PM can learn from Steve Martin

Just a good short read I think most PMs would enjoy. In short

“Be so good they can’t ignore you.”

http://calnewport.com/blog/2008/02/01/the-steve-martin-method-a-master-comedians-advice-for-becoming-famous/

Posted in Uncategorized | Leave a comment

HR Law

A Project Manager is expected to know HR Law.  The problem is that most PMs rely on word of mouth HR training.  HR law changes almost monthly, because of this a PM should always seek out the most recent HR laws applicable to the situation at hand.  A companies HR department may be a good starting point, but it is not a guaranteed solution.  HR departments can often be overworked and understaffed.  Best course of action is to always verify what the HR department tells you with a quick review of some key sites.

http://www.dol.gov/index.htm – This is a sight I believe PMs should review monthly.  It contains the basis for federal US law.  It is always a good idea to start a review of a HR question with federal regulations.   States may have stricter laws but they all must comply at least with the basics of federal law.  It has happened a few times where state law was not as strict as federal law, but a majority of those cases end up with the courts imposing the federal law as the standard.

http://hr.blr.com/ – This is a great site that will give you a breakdown by state and a summary of the federal law.  I recommend going to the state government HR site only if the issue at this point is not clearly resolved.    State HR sites generally host the actual law with no interpretation.  This means you will need a lawyer often with HR experience to really know what to do.

Having run many international projects there are two pieces of guidance I give when dealing with other countries.  First US federal law is the safest guideline to use as a basis when dealing with HR issues if you are working for a US company.  Always error on the side of caution.  HR issues that are global are difficult and there is no simple place to go for answers.  Personally I use Universal Declaration of Human Rights as a guideline.  If you find that someone is in violation of the Universal Declaration of Human Rights you should take action as a Project Manager to get the situation resolved.

Posted in Uncategorized | Leave a comment

PMI

I am precise and creative, organized and passionate. As a PMI affiliate, I make project management indispensable for business results.

Posted in Uncategorized | Leave a comment