Need help with a drag down

Hi, I have a problem with a drag down as shown in the picture. (I removed "=" to make it easy for everyone to see my problem)

excel question.JPG

As you can see, I have a pattern to drag down the data and the only things changed are the red text.
However, when i tried to drag down, it did not work as it's supposed to be. (I did not drag down and get the 2nd set of data, I wrote it to explain the pattern)
I googled the solution but I could not find it.

Could you help me solve this problem
Thank you in advance.

Applebus welcome to the forum.

It would appear that some nested INDEX/CEILING/MOD kind of formula would do the job. Without a representative sample file upload (an Excel file) I can't say for sure. Uploading saves everyone from a having to retype all your data and we can see results of our formulas in context. You will also get more and faster response if you do so.

To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

Merged cells (column A) often cause havoc with formulas. Generally they should be avoided. Although this one is not so bad.

In the attached there are two solutions. One (column F) uses a helper column (column E) to manage the merged cells. The other (column G) does not.

In column E.

Formula:

=IF(A2="",E1,A2)

In column F.

Formula:

=--(INDEX('Form Responses 1'!$A$2:$H$70,CEILING(ROWS($2:2)/16,1),E2)=

CHOOSE(MOD(ROWS($2:2)-1,2)+1,"1st","2nd")&" Service")

In column G.

Formula:

=--(INDEX('Form Responses 1'!$A$2:$H$70,CEILING(ROWS($2:2)/16,1),LOOKUP(1E+306,$A$2:A2))=

CHOOSE(MOD(ROWS($2:2)-1,2)+1,"1st","2nd")&" Service")

You are welcome. Thank you for the feedback.

However, I need to learn how all of these functions work to solve the problem in the future.

Music to our ears. Then come back post some solutions. Have fun while learning.