List of values assigned to list of names

Hi all,

I have a column that contains 16 values (sometimes the same) in consecutive rows in sheet 1
In sheet 2 i have a list of names usually occuring 5 times each consecutively in teh same column. I would like the following to happen, The first number that appears in sheet 1 i would like assigned to the first 6 names in sheet 2 (that is to say unique names) As they appear like so...

MICK
MICK
MICK
JANE
JANE
STEVE
DANIEL
PETER
IAN
IAN
so the first value for every occurrence of the first 6 names, the second value for every occurrence of the next 6 (7-12) etc until the end. If i'm not asking too much, the ranges are variable as sometimes there will only 12 or 14 values open ended range references would be good. The number of unique names will always be 6 times the number of values
Thanks in advance for your help.
Regards Dan

It would help tremendously if you posted a workbook with sample data and the results you want to see (based on that sample data)

If I understand you correctly, you want to assign the Sheet1 values to
the Sheet2 values in groups of 6 Sheet2 values. For instance, the first 6
Sheet2 values will each be assigned the 1st Sheet1 value. The next 6
sheet2 values will each be assigned the 2nd Sheet1 value. etc.

If that's true, using your posted workbook, this regular formula will do that
On Sheet2
B2: =INDEX(Sheet1!$A$1:$A$14,FLOOR((ROW()-2)/6,1)+1)
Copy that cell down through B85.

Is that something you can work with?

I reopened this post because I stupidly posted the wrong example workbook and when Ron kindly sent me a solution for that sample data i marked it as solved as it worked well for that particular example. I have reposted a new example workbook.As you can see there are duplicates of each name. This example looks fairly uniform as there are five duplicates of each name though it is often the case that it is not so uniform (as seen in my simple example in the original post). What I would like is code that will assign the first value to the first block of six unique names, the next number to the second block of unique names and so on till the end. I would like all duplicates to be assigned the value as well. So in this example range B1:B30 will have the value 470 in each cell in that range. Please don't hesistate to contact me if this post is confusing. written expression has never been my strong point.

Regards
Dan

Thanks for posting the updated sample workbook...AND...the results you want to see.

With that new information, this regular formula on Sheet2 will
sequentially assign the Sheet1 list items, incrementing for each group of 6 unique names
B1: =INDEX(Sheet1!$A$4:$A$17,FLOOR((SUMPRODUCT((A$1:A1<>"")/COUNTIF(A$1:A1,A$1:A1&""))-1)/6,1)+1)

Copy that formula down as far as you need.

Is that something you can work with?