Formula to spill an array a certain # of times?

[DobeJR]

Wondering if there is a Formula to spill an array a certain # of times?  Any formulas to make this process easier would be awesome!

I'm creating a lookup template file, and I need to have every combination of two arrays for my rows.  My process is very tedious and wondering if some formulas would help.  If I could spill Array1 a certain number of times, that'd be nice.  Then I need to have each unique value of Array2 copied down to each set of Array1.  

Example:

	Array1	Array2
	Color	Size	Count Color:	3
	red	small	Count Size:	3
	blue	medium	Total combinations	9
	green	large
			Lookup based on Color and Size
#	Color	Size
1	red	small
2	blue	small
3	green	small
4	red	medium
5	blue	medium
6	green	medium
7	red	large
8	blue	large
9	green	large

[#2 · Accepted solution]

Hi @DobeJR 

Please see the attached workbook with your desired results

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

[#3]

@Faraz Shaikh Thanks for this.  This is hurting my brain trying to understand how it's working with the MOD formula, but I think I get it.... lol 

CHEERS!