Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Oct 2010
    Thanked 0 Times in 0 Posts
    I have a spreadsheet with product information in the following columns:
    column A - product ID
    column B - Description
    column C - Fabric
    Column D - Size 00
    Column E - Size 0
    Column F - Size 1
    Column G - Size 2
    Column H - Size 3
    Column I - Size 4
    Column J - Size 5
    A product can have different numbers in column C ~ J, depending on what customers ordered. I would like mail merge to generate labels of a product for whatever size breakdowns shown on the Excel chart
    Production ID
    So if Product A have 3 units in SIZE 00, 0 Unit in SIZE 0, and 1 units in SIZE 1,
    mail merge should generates:
    Production A
    White Cotton Jersey
    SIZE 00

    Production A
    White Cotton Jersey
    SIZE 00

    Production A
    White Cotton Jersey
    SIZE 00

    Production A
    White Cotton Jersey
    SIZE 1
    **it skips size 0 since there is no order**

    It should continue to the other sizes until size 5, then goes to the next product entry.
    It should print as many labels as there are products ordered.

    Thank you for all your helps.


  2. #2
    Super Moderator macropod's Avatar
    Join Date
    May 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 470 Times in 387 Posts
    Hi Eddie,

    It would be difficult to code a mailmerge that could accomodate variable column data as you have, especially when some of the columns may be empty whilst others after they might not.

    You really need to re-organise your data so that there is one record (row) per size for each Product ID, Description & Fabric combo. It would then be much easier to use mailmerge with the data. To do this, set up a new worksheet in your existing workbook with five headings on row 1:
    Product ID,Description,Fabric,Size,Quantity
    In A2 of the new sheet, insert the formula:
    and copy this across to column C.
    In D2 of the new sheet, insert the formula:
    In E2 of the new sheet, insert the formula:
    Note: These formulae assume your current worksheet is named 'Sheet1'. If it isn't replace 'Sheet1' in the formulae with the correct name.

    When you've inserted/copied the formulae, copy them down as far as needed - seven rows per Product ID, Description & Fabric combo. Re-order the worksheets, so that the new one is the first in the workbook. You now have a data source on the new worksheet that's suitably laid out for a mailmerge.

    In Word, set up your label merge for whatever label stationery you require, telling Word to filter out all records for which the 'Quantity' field 'is equal to' '0'.

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts