Merging Excel List to Word Document

[Jeffff]

I'm trying to make a word frequency spelling test resource book. I have a list of 36000 words ordered by their frequency of usage in English - they are currently in cells A1 to A36606 in an excel document.

I would like import this list of words into Microsoft Office. I'd like them to be split into groups of 20 words with 6 groups displayed on each page. Each group should have a heading e.g. "Test 1", "Test 2", "Test 3" etc. Below the heading should be the 20 words. So under "Test 1" would be the words from cells A1 to A20; under "Test 2" would be the words from A21 to A40 etc. Ideally each group of 20 words should be in alphabetical order (but this is something I can do without if it is troublesome).

What is the most efficient way of doing this?

I could just paste the list of words into a MS Word document that is already split into 3 collumns; then manually add in the "Test" headings and use the enter key to get 6 tests per page - but that seems like a messy and labour intensive way to do it.

Is there any way to set up a template in Word with a field for the Test number heading and fields for the 20 words per test and then merge the list into the fields?

Any advice would be much appreciated!

Thanks!

 

[Macropod]

Probably the easiest way would be to set up a label merge, using a 6-column by 21-row label format. The first row would be your header row, the remaining cells would be filled with the mergefields and, except for the first cell, a NextRecord field. The sort order for the output would be across, then down.

If you require a down-then across output, you'd need to use a directory/catalogue merge, on a page having a 6-column layout, a 6-column by 1-row table in the page header and a single-cell table with the mergefield in the body of the document, sized so that exactly 20 such cells will fit in each column.

 

[Jeffff]

Thanks for that info. I'm going to do read up a bit on label and directory merges.

What about if I wanted 3 columns like:

Test1 - Test2 - Test3
Test4 - Test5 - Test6

So including headings, I'd have 3 columns of 42 rows with the tests increasing in number from right to left before going down a level.

 

[Macropod]

That's theoretically possible. Easier with a label merge, fairly complicated to achieve otherwise.

 

[Jeffff]

That's theoretically possible. Easier with a label merge, fairly complicated to achieve otherwise.

So would I really be setting up two label merges?

The first needs a list of headings: Test 1, Test 2, Test 3 etc - they are merged into the heading label slots.

The second is the words themselves, merged into the 6 sets of 20 word label slots.

What about sorting each of the 20 into alphabetical order? If it is a phenomenal difficulty (at least for my skill set) then I might just leave it. Its not essential - I just think it would be more aesthetically pleasing to have each group alphanumerical.

Thanks

 

[Macropod]

No, in that case, it would be a 3-column by 42-row label merge, with 'heading' rows on rows 1 & 22. All the other cells would have mergefields and, except for the first of those cells, a NextRecord field.

 

[Jeffff]

I really appreciate the help - I'm facing a steep learning curve, but hopefully it will still be easier than doing this by hand!

I started playing with the mail merge wizard in Word 2010.


What do I select for label vendor?


What about product number (page size?) - I'm going to be using a 6" x 9" page size.


I skipped ahead to the label details page, but I'm unsure how I should be configuring it as I'm confused about what is constituting the "label" and the "record" etc. Is each page a label? Or each group of 20 words? Or each individual word? Or something else entirely?


In the mean time I'm going to try just a basic label merge with some address data just to get my head around the process. As it seems like I'm trying to use the feature for something that it wasn't totally intended for and I'm a bit too lost! Thanks

 

[Jeffff]

Ok, I've had a little play, but I'm still pretty stuck. Hopefully someone can give me an additional "idiot grade" nudge in the right direction

Just to be clear, I have my 36,000 words in an excel document. That is all that is in the document, the words are all in the a column, starting in cell A1. No headings or anything else.

I created a new mail merge with the correct page size initially with a single cell. I selected my data source as my "wordlist.xlsx" file.

I've inserted cells into the new merge so that I have a single page with a table 3x42.

I've experimented with placing merge and nextrecord labels into rows 2 to 21 and then pressing preview.

If I only put "nextrecord" in the first cell, and "merge" in the 9 cells below I get nothing in the first cell, then the same word in all the rest.

If I put merge in the first cell and nextrecord AND merge in the other cells then I get the next word from my list in each cell. This is fine, but when I also populate the other columns, I see that Word is filling the merge tags left to right. So rather than having the words at the top of my list ordered downwards in groups of 20, I have them being filled horizontally.

I've not even tackled the issue of the headings e.g. Test 1, Test 2 etc. Do they need to be in excel document? Or can they be generated somehow within word. Might be easier just to write those in by hand.

Thanks

 

[Macropod]

Your mailmerge main document's table should look like:

|	Test1		|	Test2		|	Test3		|
|«Words»		|«Next Record»«Words»	|«Next Record»«Words»	|
|«Next Record»«Words»	|«Next Record»«Words»	|«Next Record»«Words»	|
|«Next Record»«Words»	|«Next Record»«Words»	|«Next Record»«Words»	|
...
|«Next Record»«Words»	|«Next Record»«Words»	|«Next Record»«Words»	|
|	Test4		|	Test5		|	Test6		|	
|«Next Record»«Words»	|«Next Record»«Words»	|«Next Record»«Words»	|
|«Next Record»«Words»	|«Next Record»«Words»	|«Next Record»«Words»	|
...
|«Next Record»«Words»	|«Next Record»«Words»	|«Next Record»«Words»	|

where your Excel workbook's header row is named 'Words'. The chevrons (ie '« »') - ere part of the fields, which you can insert from the mailmerge ribbon - Insert Mergefield for the 'Words' field and Rules>Next Record for the 'Next Record' field..

 

[Jeffff]

Thanks, I was trying this again today, and it works fine for the first page, but the second page in the merge only moves on one record. So if the first 3 words on page one were ABC, on page 2 they are BCD.

Could it be the way I have the table setup? I'm not really sure the relvance of that. If I was doing it by hand, I'd just split the page into 3 columns. But merge seems to require a table (really the label as it is defined in the merge wizard) - so what is my best practice here?

Am I really creating 42x3 (126) different labels and therefore I need to figure out the size that each label needs to be? Obviously with 42 labels in the space of 20cm or so, it gets pretty tight and difficult to work with.

Thanks for the help