Formula to help mail merge

I have a sheet of 2000 rows. What I want to do is merge the information into a word document with 8 rows of information on each sheet. These will then be printed and guillotined. what I want is to have the information for the second row to appear under the first one and so in position 9 and the third under that in position 17 and so on so I can just stack the piles up and they will be in order rather than having to sort them all into order. I do not know if there is anyway of tell word what oder to put the records in or if I will have to use a formula in excel.

If I have to do it using excel I need to be able to have a separate column which i can sort and it will then be in the right order for the merge. The first row will be 1 then 9 then 17 (adding 8 each time) up to row 50. Then row 51 is 2, 52 is 10, 52 is 11 up to 100. Then row 101 is 3, 102 is 11 and so on and so on.

Word will do this without difficulty if the information in Excel is in a single row per document.

Yes all the information for each record is in one row. Could you tell me how please?

What version of Word? (I only have 2007 installed, and the interface is different; I don't think I could walk you through an earlier version of Word by memory.)

Have you ever done any mailmerge before?

Yes have done mail merges before. I have 2003 but if you could talk me through it on 2007 I can adapt it or know someone who has 2007

Many thanks

So in the Word document, you do Insert > Field (in 2007, it's Insert > Quick Parts, Field, Mail Merge.) Then enter the column header in the Excel file, e.g., "Name" (sans quotes) in the dialog box. It will show up like this: «Name». Repeat for all of the fields to be inserted. Then fumble your way to select the merge file and answer the questions that follow, and run the merge. (In 2007, you do Select Recipients > Use Existing List, and browse to the file; there's a wizard in 2007, and one in 2003 also as I recall.)

have found the wizard. Where do you tell it what order you want the labels in?

sorry to be nuisance

That I don't know -- I've never used the wizard. I just insert the fields manually. Did you do that?

done all the fields but cant do the order you have any idea how to do the excel formula method?

I don't understand. You insert the fields in which you want them to appear. Here is a Word doc ready to be merged with the Excel doc.

Is not the order of the fields is is the order of the records. Using your example I could have 2000 addresses and want to merge them so there are 8 on each page. If I print them and then want to order them I have to guilotine them and then stack them in order. This would take hours. What I want to be able to do is have the first record (address) in the top left of page 1 and the second under it in top left of page 2 (position 9). This then goes through to page 250. The 251st record would go in top right and then 25nd in top right of page two and so on. This means that when they are guilotined they are are all in order and one pile can go on top of the other.

Therefore I need to have a formula in a hidden column which puts row 1 as 1, 2 as 9 3 as 17 down to row 250 as 2000, then 251 as 2, 252, 10 and so on.

It does however need to be linked to how many rows there are as may not be exactly 2000.

Hope this makes it a bit clearer.

Many thanks for your help so far

How about posting a workbook that shows your data as you have it now, and a portion of it as you need it -- after confirming that if you get it as shown, it does indeed merge properly.

I have attached part of the workbook. The first sheet is the original order and the first column contains the numbers I want the formula to calulate so it can be sorted as it is in the second sheet.

You can see that seat 1 for Chemistry (SS) is no 1 and seat two is 9 and so it will appear in the same place but on page two as there are 8 records for sheet.

Is sheet Sorted ready to merge? I'm assuming not, and that mutiple rows need to be brought side by side.

The orted sheet is the original isnt

Can you post the Word doc that you use for merging?

here it is

OK, got it.

So you manually entered the numbers in column A, then copied the data to the Sorted sheet, and the sorted by col A, correct?

Yes but would like a formula to be able to calculate the number column for me and be connected to the number of records

... and be connected to the number of records

You mean the number of labels on the sheet?

So I see you start at 1 count by 8s for 50 rows, then start at 2 and count by 8s for 50 rows, then start at 3 and ...

Is that all there is to it, or is there more complex underlying logic?

No thats about it but if there were 800 records then the first 100 would be adding 8 from 1 and then 101 would be 2 and then add 8 up to 200 and so on so it does also depend on the number of records

OK.

Define nRec Refers to: =8

In A2 and copy down,

= nRec * MOD(ROW() - ROW($A$2), 50) + INT( (ROW() -ROW($A$2)) / 50) + 1

Thank you sorry it took so long

Many thanks

... but if there were 800 records then the first 100 would be adding 8 from 1 and then 101 would be 2 and then add 8 up to 200 and so on ...

My formula doesn't do that -- it's hard-coded to 50.

How many per cycle if there were 427 records? If there were 723? If there were any arbitrary number?

Will always be a factor of 8. Can you add the count function into the formula.

So it counts the seat no. column to find out how many rows their are and then divide it by 8?

OK, skip all prior.

Define nPerPage Refers to: =8

Define cycle Refers to: =INT( (MATCH("zzzz", Original!$B:$B, 1) - ROW(Original!$B$1) ) / nPerPage)

In A2 and down, = nPerPage * MOD(ROW() - ROW($A$2), cycle) + 1 + INT( (ROW() -ROW($A$2)) / cycle)