Data Manipulation in Excel
$30-250 USD
Paid on delivery
I have a very simple job of data transformation. Master Data file in Excel contains all data. From this, you need to create 2 separate transformations into First Job Data and Second Job Data as described in the Job Description:
All data is contained in the Master Data file.
From this data, you should create 2 separate files. Each of these files will have a transformation of the Master Data.
The Master Data has 4 excel worksheets numbered by years -2007, 2006, 2005 and 2004. Each worksheet contains one year of data for several hundred internet companies. However, the number of companies is different in each year. For example, 2007 (latest year) and 2006 has data on 500 companies, one in each row. But 2005 has data on 400 companies while 2004 has only data for 300 companies. This means that the latest year (2007) contains data on companies not listed in the 2004 worksheet. Therefore between worksheets, some companies are common and some are unique.
Each worksheet has the same number of variables.
FIRST JOB
1. Start with the latest year 2007. Create 4 rows for each company where each row indicates a specific year – 2003, 2004, 2005 and 2006 (see Sample First Job Data).
2. Then go to previous year’s worksheets and match the company name, and fill the data for each variable (that is, each column) for each of the years for this company. So, for example, [login to view URL] had sales of $10,710,000,000.00 in 2006, in 2005 it was $8,490,000,000.00, in 2004 it was $6,921,124,000.00, and so on.
3. Do this for each variable or column.
4. Leave blank for missing data.
SECOND JOB
1. Start with the latest year 2007.
2. Create 4 years for each variable – each year in one column. For example, 2006 Sales, 2005 Sales, 2004 Sales, 2003 Sales. Go to other worksheets and fill these numbers (see Sample Second Job Data).
3. Then create 3 columns of DIFFERENCES. For example, Sales Difference 2006-2005, Sales Difference 2005-2004, Sales Difference 2004-2003.
4. Then go to other worksheets and match the company name.
5. Fill them is as follows: Subtract 2005 data from 2006 data for the variable Sales Difference 2006-2005; Subtract 2004 data from 2005 data for the variable Sales Difference 2005-2004; and so on.
6. Leave blank for missing data.
This is a very straightforward job. I can pay only $50.
Do NOT bid if you want more than $50.
Project ID: #233990