Solved Copy some column data & append in new rows wth existing data

May 8, 2013 at 23:21:47
Specs: Windows XP
excel sample data

I need a serious help from you guys, i have a data where i have emp number, emp name and emp dob, along with sales values month on month. however i have the first three columns data in row and months i have in columns... i need to have those months along with their figures on the rows adjacent to the existing emp name and emp number.

Have attached a sample data in excel which can clarify my issue more.

See More: Copy some column data & append in new rows wth existing data

May 9, 2013 at 08:09:28
✔ Best Answer
These formulas may need some modifications to match your spreadhsheet, but the concept should be valid. The main thing you will probably need to deal with is the offset values for the ROW() function. The formulas below are based on your example, so that's why the offsets that I used work.

Let's say you start with this:

      A        B         C        D      E  ...  O
1  Emp_No   Emp_Name    DOB      Jan    Feb ... Dec
2  200012   Rajesh    1-Jan-99   300    200 ... 111
3  200112   Vijay     1-Feb-98   222    333 ... 222
9  Emp_No   Emp_Name   Months   Value      

1 - Enter this in A10 and drag it over one column to B10:


Now drag both of those formulas down to Row 33. You should have 12 rows for each of the 2 employees.

2 - Enter this in C10 and drag it down to C33:


You should get a set of 12 months (Jan - Dec) for each employee.

3 - Enter this in D10 and drag it down to D33:


This should return the monthly values for each employee/each month.

Let us know how that works out for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question