Creating multiple copies of same data...

**pauleon** · 07-16-2012, 10:34 AM

Hi all,
If this question has already been answered, please let me know. I'm super busy and don't have time right now to read all past posts. Please forgive my need for instant gratification, and if a link within the forums answers this question, I would appreciate you providing it.

I'm trying to create labels for samples in my lab. This is how the data page is arranged...

Sheet 1.jpg

I have created a table for mail merge on sheet two that looks like this:

Sheet 2.jpg

Here comes the tricky part. I am trying to use this data to make labels, but I need multiples for each record. In the first column of sheet 1, labelled "#sets", there is a multiplier that I would like to use to create that number of PAIRS of labels. E.g.: In the first row, I have 2 sets of case # 11 1501. I would like to create on sheet two 4 copies (two pairs) of that data in separate cells that I can then use to mail merge into my label template.

The label sheet is eventually going to look like this...
28 labels with JUST the 4-digit part of the data:

1501

1502

1503

...

Then the rest of the labels should be repeats of the case year and number plus the date based upon # sets:

11-1501-DATE	11-1501-DATE	11-1501-DATE	11-1501-DATE	11-1502-DATE	11-1502-DATE	11-1502-DATE	11-1502-DATE	11-1502-DATE	11-1502-DATE	11-1502-DATE	11-1502-DATE
11-1503-DATE	11-1503-DATE	11-1503-DATE	11-1503-DATE	11-1503-DATE	11-1503-DATE	11-1503-DATE	11-1503-DATE

...Of course, I would need this data VERTICALLY so that I can make use of the headers in a mail merge, but for the simplicity of this post, I left it horizontal.

If need be, I can use mail merge to pull multiple columns in sheet two, but the goal is to get the labels IN SEQUENCE. These labels are being used to ID sample tubes in separate trays in a 1:1 fashion so that the sets are identical, so if I need two tubes for case 1501, I have two labels for it in each tray.

So, the simplified questions is:
Is there a way to use the multiplier in "#sets" to make that number of copies of the data from the "year" and the "case#" columns into separate but adjacent cells on sheet 2?