Sort Repeating Pattern into Table

[AppleSource]

So, i have odd problem. im sure its reasonably simple but im not sure of the best way to accomplish it. Ive made a simple example to show what i need to do on a small scale.
I would like names to be placed into the columns of IMAGE 1, according to the pattern defined by IMAGE 2
Image 3 is showing an example of one person placed into the table.
Eg: BOB-apple(1/1/21),orange(2/1/21),none(3/1/21),orange(4/1/21),banana(5/1/21),banana(6/1/21)....... Then it repeats
So the end Result will show each day who gets which fruit, (also there are days which someone may get no fruit, cos they arent there.)
I would like to use VBA to "Push data" into the table in IMAGE 1.

(IMAGE 1)
(IMAGE 2)

(IMAGE 3)

Hope This makes sense, if anything needs clarifying lemme know :s

 

[mrshl9898]

Assuming IMAGE 2 is on "Sheet2" a formula will do the job

B2 =INDEX(Sheet2!$A$1:$G$1,1,MATCH(B$1,Sheet2!A2:G2)) ....copied down and across

 

[AppleSource]

Assuming IMAGE 2 is on "Sheet2" a formula will do the job

B2 =INDEX(Sheet2!$A$1:$G$1,1,MATCH(B$1,Sheet2!A2:G2)) ....copied down and across

.... came up with odd results, unless Ive not entered the formula correctly.
Below is the formulas applied to a few different cells.
D2 should equal Mike, B4 Should be John. B2 Is correct tho.

the other problem is that the formula wouldn't allow for the pattern to loop i would have to re-enter the formula every 6 rows??

 

[mrshl9898]

Apologies, I made a mistake

B2 =INDEX(Sheet2!$A$1:$G$1,1,MATCH(B$1,Sheet2!$A2:$G2,0))

 

[AppleSource]

Apologies, I made a mistake

B2 =INDEX(Sheet2!$A$1:$G$1,1,MATCH(B$1,Sheet2!$A2:$G2,0))

That fixed it, champion,
How would i make it repeat the pattern without manually re-entering the formula every 6 rows ?

 

[Peter_SSs]

Welcome to the MrExcel board!

It looks like both sheets contain formal Excel tables. If that is so and the table on Sheet1 is called Table1 and the table on Sheet2 is called Table2 then see if this helps.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

AppleSource.xlsm
	A	B	C	D	E	F	G
1	BOB	TONY	GARY	TIM	JOHN	HARRY	MIKE
2	APPLE	ORANGE	NONE	PINEAPPLE	NONE	PEAR	BANANA
3	ORANGE	APPLE	BANANA	NONE	PEAR	NONE	PINEAPPLE
4	NONE	BANANA	PEAR	PINEAPPLE	APPLE	ORANGE	NONE
5	ORANGE	PINEAPPLE	NONE	BANANA	PEAR	NONE	APPLE
6	BANANA	PEAR	APPLE	NONE	NONE	PINEAPPLE	ORANGE
7	BANANA	NONE	ORANGE	APPLE	PINEAPPLE	NONE	PEAR
Sheet2

If you put (copy/paste) the B2 formula below into that cell it should spill all the way down the column.
You can then copy (drag) that B2 formula across that first row in the table. They may not immediately spill down but if you go into each of those top row cells and press F2 then Enter they should spill down.

AppleSource.xlsm
	A	B	C	D	E	F
1	Dates	APPLE	ORANGE	BANANA	PEAR	PINEAPPLE
2	1/01/2021	BOB	TONY	MIKE	HARRY	TIM
3	2/01/2021	TONY	BOB	GARY	JOHN	MIKE
4	3/01/2021	JOHN	HARRY	TONY	GARY	TIM
5	4/01/2021	MIKE	BOB	TIM	JOHN	TONY
6	5/01/2021	GARY	MIKE	BOB	TONY	HARRY
7	6/01/2021	TIM	GARY	BOB	MIKE	JOHN
8	7/01/2021	BOB	TONY	MIKE	HARRY	TIM
9	8/01/2021	TONY	BOB	GARY	JOHN	MIKE
10	9/01/2021	JOHN	HARRY	TONY	GARY	TIM
11	10/01/2021	MIKE	BOB	TIM	JOHN	TONY
12	11/01/2021	GARY	MIKE	BOB	TONY	HARRY
13	12/01/2021	TIM	GARY	BOB	MIKE	JOHN
14	13/01/2021	BOB	TONY	MIKE	HARRY	TIM
15	14/01/2021	TONY	BOB	GARY	JOHN	MIKE
16	15/01/2021	JOHN	HARRY	TONY	GARY	TIM
17	16/01/2021	MIKE	BOB	TIM	JOHN	TONY
18	17/01/2021	GARY	MIKE	BOB	TONY	HARRY
19	18/01/2021	TIM	GARY	BOB	MIKE	JOHN
20	19/01/2021	BOB	TONY	MIKE	HARRY	TIM
Sheet1

Cell Formulas
Range	Formula
B2:B20	B2	=INDEX(Table2[#Headers],MATCH(Table1[[#Headers],[APPLE]],INDEX(Table2,MOD(ROW()-ROW(Table1[#Headers])-1,ROWS(Table2))+1,0),0))
C2:C20	C2	=INDEX(Table2[#Headers],MATCH(Table1[[#Headers],[ORANGE]],INDEX(Table2,MOD(ROW()-ROW(Table1[#Headers])-1,ROWS(Table2))+1,0),0))
D2:D20	D2	=INDEX(Table2[#Headers],MATCH(Table1[[#Headers],[BANANA]],INDEX(Table2,MOD(ROW()-ROW(Table1[#Headers])-1,ROWS(Table2))+1,0),0))
E2:E20	E2	=INDEX(Table2[#Headers],MATCH(Table1[[#Headers],[PEAR]],INDEX(Table2,MOD(ROW()-ROW(Table1[#Headers])-1,ROWS(Table2))+1,0),0))
F2:F20	F2	=INDEX(Table2[#Headers],MATCH(Table1[[#Headers],[PINEAPPLE]],INDEX(Table2,MOD(ROW()-ROW(Table1[#Headers])-1,ROWS(Table2))+1,0),0))