Automatically create a table based on two lists

[marklu]

Hi all,

I wanted to see if there's a quick way to combine List 1 and List 2 below so that a new table can be created with the format shown below. Hope my example below helps. Please let me know if you have any questions.

Thanks in advance!
Mark

List 1	List 2
Apples	Center 1
Bananas	Center 2
Oranges	Center 3
Lemons	Center 4
Grapes	Center 5
Total	Center 6
	Center 7
	Center 8
	Center 9
	Center 10
	Center 11
	Center 12
	Center 13
	Center 14
	Center 15
	Center 16
Table
Center 1	Apples
Center 1	Bananas
Center 1	Oranges
Center 1	Lemons
Center 1	Grapes
Center 1	Total
Center 2	Apples
Center 2	Bananas
Center 2	Oranges
Center 2	Lemons
Center 2	Grapes
Center 2	Total
Center 3	Apples
Center 3	Bananas
Center 3	Oranges
Center 3	Lemons
Center 3	Grapes
Center 3	Total
……………………. and on

 

[Fluff]

Hi & welcome to MrExcel.

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[marklu]

Thanks! I have Windows Excel 2016. I've just updated my user profile to reflect that.

 

[Fluff]

Ok, thanks for that.
How about

+Fluff 1.xlsm
	A	B	C	D	E
1	List 1	List 2
2	Apples	Center 1		Center 1	Apples
3	Bananas	Center 2		Center 1	Bananas
4	Oranges	Center 3		Center 1	Oranges
5	Lemons	Center 4		Center 1	Lemons
6	Grapes	Center 5		Center 1	Grapes
7	Total	Center 6		Center 1	Total
8		Center 7		Center 2	Apples
9		Center 8		Center 2	Bananas
10		Center 9		Center 2	Oranges
11		Center 10		Center 2	Lemons
12		Center 11		Center 2	Grapes
13		Center 12		Center 2	Total
14		Center 13		Center 3	Apples
15		Center 14		Center 3	Bananas
16		Center 15		Center 3	Oranges
17		Center 16		Center 3	Lemons
18				Center 3	Grapes
19				Center 3	Total
20				Center 4	Apples
21				Center 4	Bananas
22				Center 4	Oranges
23				Center 4	Lemons
24				Center 4	Grapes
25				Center 4	Total
26				Center 5	Apples
27
Main

Cell Formulas
Range	Formula
D2:D26	D2	=INDEX($B$2:$B$17,INT((ROWS(D$2:D2)-1)/ROWS($A$2:$A$7))+1)
E2:E26	E2	=INDEX($A$2:$A$7,MOD((ROWS(D$2:D2)-1),ROWS($A$2:$A$7))+1)

 

[marklu]

This is perfect. Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.