excel sample data

Hi, 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.

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 Value1 - Enter this in A10 and drag it over one column to B10:

=INDEX(A$2:A$3,CEILING((ROW()-9)/12,1))

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:

=IF(MOD(ROW()-9,12)=0,"Dec",INDEX($D$1:$O$1,1,MOD(ROW()-9,12)))

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

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

=INDEX($B$2:$O$3,MATCH(B10,$B$2:$B$3,0),MATCH(C10,$B$1:$O$1,0))

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.

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History