Yvonne from HR came with this problem for our excel course “Hidden Secrets in Data Analysis with Excel 2007/2010”.

She has a template consisting more than 500 rows. Each row contains the details of a staff and his employment history for the last 7 jobs. Employer 1 records the job history of his earlier job while employer 7 records the details of his latest job. If the person worked for at least 7 companies before, all the columns in that row would be filled up. If the person has less than 7 employers in the past, e.g. 3 employers only, the the employer records will end at employer 3.

Now, there is a change in needs and the employer records have to be reversed, with the latest employer presented as employer 1 and the second oldest employer presented as employer 2, etc.

Can the data be re-organised quickly and accurately?

Most people would probably resigned to using copy and paste to solve the problem. Try copying and pasting for those few rows listed and you will know that it is not a task for anybody because the chance of copying and pasting wrongly is extremely high. Yes, a simple macro could have done the job but writing the macro will take time. Is it possible to use formula(s) for the re-organisation? The answer is yes.

So how do we do it?

First, we make use of the COUNT formula to identify the number of employers the staff worked for before joining the company. If he has only one employer, he would have taken up only 4 cells. If he has 2 employers, 8 cells, etc. if he has 7 employers, all the 7 x 4, 28 cells would have have been filled up. By knowing the number of employers, we will know where is the last employer located, in the last 4 cells, 2nd set of the last 4 cells, etc. and will be able to swop the positions of the employers. By using multiple nested if, we will be able to refer to the right column for the most recent employee.

Download the file by clicking the link below and try completing the task with just formulas.


Leave a Reply