Combination of data based on loop

[GA3]

Hello, I have searched the boards for what seems like hours and I can not find anything to help me.

I need to group 3 columns of data. The first 2 columns (group number and store number) must stay paired (they are a required combination) but I need to create the 3rd column to loop through a list of Item numbers and create a 3rd column of code numbers paired with the first 2 columns and then go back to the list of group numbers and store numbers and repeat (loop) the process through the entire list of Group & Store numbers.

Here is an example of what I am needing. The output needs to be in 3 separate columns.

TIA for any help! It is greatly appreciated!

 

[Eric W]

Are you looking for a formula solution, or a VBA solution? If formula, what version of Excel do you have? Please update your member profile to reflect that. If you have Excel 365 for example, this might work for you:

Book1
	A	B	C	D	E	F	G	H
1						Output
2	Group	Store		Item #		Group	Store	Item
3	1000	2000		500001		1000	2000	500001
4	1001	2001		500002		1000	2000	500002
5	1002	2002		500003		1000	2000	500003
6				500004		1000	2000	500004
7						1001	2001	500001
8						1001	2001	500002
9						1001	2001	500003
10						1001	2001	500004
11						1002	2002	500001
12						1002	2002	500002
13						1002	2002	500003
14						1002	2002	500004
15
Sheet21

Cell Formulas
Range	Formula
F3:H14	F3	=CHOOSE({1,2,3},INDEX($A$3:$A$10,SEQUENCE(COUNTA($A$3:$A$10)*COUNTA($D$3:$D$10),,,1/COUNTA($D$3:$D$10))),INDEX($B$3:$B$10,SEQUENCE(COUNTA($A$3:$A$10)*COUNTA($D$3:$D$10),,,1/COUNTA($D$3:$D$10))),INDEX($D$3:$D$10,MOD(SEQUENCE(COUNTA($A$3:$A$10)*COUNTA($D$3:$D$10),,0),COUNTA($D$3:$D$10))+1))
Dynamic array formulas.