Survey Loading Process

It's getting to be that time of year when many survey publishers release the results from their fall and winter data collection.

Here's a diagram of the overall process of data loading in NextComp. I've labeled the various steps with the primary area of responsibility in a typical setup. This varies by organization as some subscribers do all of their data loading without involvement from NextComp.

You can also download a PDF version of this file.


Click to view larger.

You may also be interested in these documents:

• Aging and factoring methodology - version 3 (download PDF)
• Compensation glossary (download PDF)

FAQ: How to convert values to text in Excel using the TEXT function.

Here's a tip for Excel to allow you to load organizational or survey job codes that are a mix of number codes and letter codes. For example, some surveys have job numbers similar to the following:

• 1000
• 1000R

or the format may look like this

• 10.10
• 10.11
• 10.12

What can happen in Excel is that the trailing zero is removed and the list ends up looking like this:

• 10.1
• 10.11
• 10.12

In the above examples the job codes with an R at the end are considered text data by Excel. The job codes without the R are considered numeric data by Excel. Similarly, the job codes with the decimal point are all considered values by Excel, when in reality you want them to load into NextComp as text.

NextComp will read only the numeric OR text depending on how the data is sorted in the import template. You’ll need to convert all of the job codes to text data in Excel. The image below gives a quick visual summary of the process. Click the image for a larger version.


Here's a step-by-step description for Excel 2003 and prior versions. The same techniques work in Excel 2007, although the menu names may be slightly different.

Step 1: Select all of column B by clicking in column B’s header

Step 2: Select the Insert menu in Excel, then select Columns

Step 3: Select the Format menu item in Excel and then select the Cells menu item

Step 6: Select cell B2

Step 7: Enter the formula shown below into cell B2. Here's a description of the TEXT function on the Microsoft Office website.

Step 8: Copy cell B2 down all the way to the last row of job code data

Step 9: Select data in column B from cell B2 through to the last row of data in the column

Step 10: Select the Edit menu item in Excel and then select the Copy menu item

Step 11: Select cell A2

Step 12: Select the Edit menu item in Excel and then select the Paste Special... menu item

Step 13: Choose “Values” as the Paste type

Step 14: Click the OK button

Step 15: Select all of column B by clicking on the column header for B

Step 16: Select the Edit menu in Excel and then select the Delete menu item

Step 17: Select the File menu item in Excel

Step 18: Select the Save item in the File menu

User's Group Meeting, Market Pricing, Salary Planning and Twittering

It's been a busy week and I'm having difficulty believing it's Friday already. The week started off kind of slow with the kids home from school and then the excitement built as Tuesday morning approached. Wow! What an incredible day for America. I had to work that day, but I caught up on all the events later that night on cspan. It was also fun reading all the tweets and blog posts from around the world. So many people were excited for the new President to take office. I'm also very pleased that my kids were all able to watch the inauguration at school. They watched it online projected on the screens in their classes. Very cool! I know that Tuesday will be a day I remember for a long time to come.

Closer to home, it's been a busy week here at NextComp. Here's a summary of what we've been up to this week.

User's Group Meeting

I've been quietly planning a user's group meeting in the Seattle area, all the while working feverishly to implement Version 3 of NextComp before the meeting. Well it looks like I'll have Version 3 completed and ready by the end of February. So I'm working on the details to have the User's Group meeting in Issaquah sometime the first two weeks of March. I'm still nailing down the space availability. I'll have that finalized early next week, so the blog post next week will have more details.

Salary Planning

This past week I was working extensively on an update to the NextComp salary planning application. I re-wrote the database engine to enhance performance and scalability. This tool was originally designed to run on smaller networks and in smaller to medium sized companies. One client is running it on a larger network with over 500 managers having access the system. Of course, before the re-write, the database crumpled under the load. I think smoke might have been coming out of the server! :-) After a series of late night programming sessions, I was able to redesign the database to effectively scale up to over 10,000 employees and over 500 managers. Whew, that was some serious work. It's done now, and I'm moving on to create a cool Quicktime training video.

Here are some screenshots...








Of course that's not the only project that's going on. Never a dull moment here in Issaquah.

Version 3 Update

I've been working on NextComp Version 3 with the goal of having it completed by the end of February in time for several of our clients to transition to the new application before the WorldatWork Conference being held in Seattle in June. I've been getting really helpful feedback from a couple of larger subscribers that have volunteered to transition early to Version 3, while still using Version 2 primarily. Thank you to them! More updates next week on Version 3.



The Tweet In Review

Continuing on the post from last week, I've been trying to tweet on any interesting articles that I run across during my blog reading. I use NetNewsWire on the Mac to consolidate the blog posts into one list of updated information. I have over 100 blogs that I read on a regular basis, and the list keeps growing. I enjoy everything from compensation related blogs, to listserve type blogs, to art and photography blogs. Here are some highlights from this last week.

Sitting on top of my roof taking Xmas lights down http://twitpic.com/13z8v. Nice view from up here! 1:20 PM Jan 17th from twitterrific

AMD cutting 1,100 jobs and cutting salaries by 5% to 15%. http://tinyurl.com/7r4g2g 10:01 AM Jan 19th from TweetDeck

@annemareemoore Here's a blog with a copy of the President's speech in PDF format... http://leifbrostrom.com/?p=153 1:58 PM Jan 20th from web in reply to annemareemoore

Over 7 Million Workers to See Lowest Pay Raise in 30+ Years http://tinyurl.com/9ch9ub 9:44 AM Jan 21st from TweetDeck

iPhone development webinars, five total for $97 each. Good deal! http://tinyurl.com/885fc8 10:16 AM Jan 21st from TweetDeck

Just received an e-mail: There are just 130 days, 2 hours and 50 minutes until the 2009 WorldatWork Total Rewards Conference and Exhibition. 12:40 PM Jan 21st from TweetDeck

Check out the resolution of the Canon EOS 5D Mark II http://tinyurl.com/bhhn8c What an awesome place for skiing too! 10:44 AM yesterday from TweetDeck

Inauguration in Tweets: http://projects.flowingdata.com/inauguration/ about 18 hours ago from TweetDeck

New ski related blog post http://photomatto.typepad.com about 16 hours ago from twitterrific

President Obama is a good Dad too... http://tinyurl.com/8q3yp9 about 5 hours ago from TweetDeck

"Four sure fire ways to get laid off" http://tinyurl.com/bte4zc about 1 hour ago from TweetDeck

Links to information on the Leadbetter Fair Pay Act that looks to pass Congress soon. http://tinyurl.com/d4xhz4 http://tinyurl.com/bbzstn about 1 hour ago from TweetDeck

"Washington’s three largest insurers were down a combined $194M in October" http://tinyurl.com/dms7jv 43 minutes ago from TweetDeck

Thanks to "The Industry Radar" http://www.theindustryradar... for all the great links.
42 minutes ago from TweetDeck



Well that wraps up another week here in the cold, damp and foggy Northwest. We're supposed to get some snow over the weekend. Oh boy! Well at least that will help the ski resorts.

Until next week, have a great weekend!

Excel Tip: Paste Special | Multiply

Here's another trick that I've been using in preparing data files for loading into NextComp.

Many surveys display the survey result amounts in 1,000's, for example, "$54,500" is displayed as "54.5" in the Excel report from the publisher.

NextComp expects the survey data to be in hourly or annual amounts. If we load the data as it is displayed, NextComp will assume that the data represents an hourly amount of $54.50.

You can use the Copy, Paste Special | Multiply function to convert all the data in a column or a group of columns to an annual figure.

Here's how...

The image below shows a column of data before being converted to annual figures.



I entered the number 1000 in the empty cell at the top of the column, as shown below.



I copied the cell with the 1000 in it. I selected all the data in column AK. And I right clicked over the selected cells to bring up the menu shown below.



I chose the Paste Special... command from the menu. In the Paste Special dialog box, I selected the "Multiply" operation.



After clicking the OK button, the data in the selected cells is multiplied by the copied value, in this case 1000.



You can use this same method to divide data as well. Just choose the "Divide" operation in the Paste Special dialog box. This can be useful when loading data that NextComp expects to be in a percent format. Some surveys display 100% as the number 100 rather than 1 with formatting applied.