Help with dragging

Occasional Visitor

I have data arranged in the following way

   A             B           C            D         E
1  Mins1  Text1      Mins2   Text2   Week 1

2  Mins3  Text3     Mins4   Text4   Week 2

3  Mins5  Text5      Mins6   Text6   Week 3

But in 50 Rows

And I need to give it this format:

 

Week 1  Text 1  Min 1

Week 1  Text 2  Min 2

Week 2  Text 3  Min 3

Week 2  Text 3  Min 3

Week 2  Text 4  Min 4

 

Is there any way to do this through a function or a different way of dragging? In summary i would need to drag values in B and D every 2 rows so that is goes as:

B1

D1

B2

1 Reply

@wogoos 

Let's say your data are on Sheet 1.

On another sheet, enter the following formula in A1:

=INDEX('Sheet 1'!E:E, QUOTIENT(ROW(1:100)+1, 2))

In B1:

=INDEX('Sheet 1'!A1:D50, QUOTIENT(ROW(B1:B100)+1, 2), 2*MOD(ROW(B1:B100)+1, 2)+COLUMN(B1:B100))

And in C1:

=INDEX('Sheet 1'!A1:D50, QUOTIENT(ROW(B1:B100)+1, 2), 2*MOD(ROW(B1:B100)+1, 2)+COLUMN(B1:B100)-1)