Help with Index formula - potentially combined with a count formula

[RichiePay]

Hi There,

This is my first post to the forum, I've searched the forums and I believe that the Index formula is the solution I am after, however I'm stuck with how to repeat the formula automatically with the correct results.

Below is the source data:

Emp Num Emp Name Check Pay 1 $ Pay 2 $ Pay 3 $ Pay 4 $
001234 Employee 1 Correct 29-Feb-16 1445.40 31-Mar-16 3022.20 30-Apr-16 2759.40 31-May-16 657.00
003456 Employee 2 Correct 31-Jan-16 262.80 29-Feb-16 2759.40 31-Mar-16 3022.20 30-Apr-16 1839.60
008976 Employee 3 Correct 29-Feb-16 2102.40 31-Mar-16 3022.20 30-Apr-16 2759.40 0-Jan-00 0.00

The results I want is below (repeat employee number 4 times, once for each pay period and have the pay end and $ figure):

Emp Num Pay Period $
001234 29-Feb-16 1445.40
001234 31-Mar-16 3022.20
001234 30-Apr-16 2759.40
001234 31-May-16 657.00
003456 31-Jan-16 262.80
003456 29-Feb-16 2759.40
003456 31-Mar-16 3022.20
003456 30-Apr-16 1839.60
008976 29-Feb-16 2102.40
008976 31-Mar-16 3022.20
008976 30-Apr-16 2759.40
008976 0-Jan-00 0.00

I can get the index formula to bring back the first result, however when I move to the next line it does also, but I want it to move every 4th line for the employee number.

The formulas I've attempted are below:

Emp Num
=INDEX($A2:$K2,1,1)
Pay Period
=INDEX($A2:$K2,1,4)
$
=INDEX($A2:$K2,1,5)

Which gives the below results:

Emp Num Pay Period $
001234 29-Feb-16 1445.40
003456 31-Jan-16 262.80
008976 29-Feb-16 2102.40
000000 0-Jan-00 0.00

I'm thinking a count needs to be used somehow, but I'm stuck as to how to implement. I've attached the sample document.

Can anyone help?

Thanks,

Richie

[Pete_UK]

Put these formulae in the cells stated:

A8: =INDEX($A$2:$K$4,INT((ROWS($1:1)-1)/4)+1,1)

B8: =INDEX($A$2:$K$4,INT((ROWS($1:1)-1)/4)+1,MOD(ROWS($1:1)-1,4)*2+4)

C8: =INDEX($A$2:$K$4,INT((ROWS($1:1)-1)/4)+1,MOD(ROWS($1:1)-1,4)*2+5)

Then copy down to row 19.

Hope this helps.

Pete

[RichiePay]

Perfect! Thanks Pete_UK this is exactly what I was after.

Thanks for the quick response.

Richie

[Pete_UK]

You're welcome, Richie - thanks for feeding back.

Pete