Can I recreate this VBA Function with only using Excel Built-in Functions?

[VBE313]

Hello, I have a formula that will extract and sort the unique teams in Column A (Cell D2). For each team, there needs to be a row for 4 operations. "LASER CUT", "PRESS BRAKE", "WELD INSP", "WELD". I created a VBA UDF that will spill two columns into exactly what I need. However, I am wondering if there is a way to do this in Office 365 using native Excel functions. Can I convert this VBA code into a formula based Excel function?

Function test(uniqueTeams)
Dim i, num, X, z
num = uniqueTeams.Rows.Count
z = 1
X = 0
ReDim A((num * 4) - 1, 1)
    For i = 0 To (num * 4) - 2
        A(i + X, 0) = uniqueTeams(z).Value
        A(i + X, 1) = "LASER CUT"
        A((i + X) + 1, 0) = uniqueTeams(z).Value
        A((i + X) + 1, 1) = "PRESS BRAKE"
        A((i + X) + 2, 0) = uniqueTeams(z).Value
        A((i + X) + 2, 1) = "WELD INSP"
        A((i + X) + 3, 0) = uniqueTeams(z).Value
        A((i + X) + 3, 1) = "WELD"
        i = (i + X) + 2
        X = 1
        z = z + 1
    Next i
    test = A
End Function

 

[jasonb75]

Using a single formula to fill both columns, this is the simplest I've found so far.

Book1 (version 1)19.2.21.xlsx
	A	B	C	D	E	F
1	Team 1	Team 1	Laser Cut
2	Team 2	Team 2	Brake Press		Team 1	Laser Cut
3	Team 3	Team 3	Weld Insp		Team 1	Brake Press
4	Team 4	Team 4	Weld		Team 1	Weld Insp
5	Team 1				Team 1	Weld
6	Team 2				Team 2	Laser Cut
7	Team 3				Team 2	Brake Press
8	Team 4				Team 2	Weld Insp
9	Team 1				Team 2	Weld
10	Team 2				Team 3	Laser Cut
11	Team 3				Team 3	Brake Press
12	Team 4				Team 3	Weld Insp
13					Team 3	Weld
14					Team 4	Laser Cut
15					Team 4	Brake Press
16					Team 4	Weld Insp
17					Team 4	Weld
Sheet9

Cell Formulas
Range	Formula
B1:B4	B1	=SORT(UNIQUE(A1:A12))
E2:F17	E2	=CHOOSE({1,2},INDEX(B1#,INT(SEQUENCE(COUNTA(B1#)*COUNTA(C1:C4),1,1,1/COUNTA(C1:C4)))),INDEX(C1:C4,MOD(SEQUENCE(COUNTA(B1#)*COUNTA(C1:C4),1,0),COUNTA(B1#))+1))
Dynamic array formulas.

 

[Fluff]

Another option, if you have the LET function.

+Fluff 1.xlsm
	A	B	C	D	E
1
2	Team1	LASER CUT		Team1	LASER CUT
3	Team2	PRESS BRAKE		Team1	PRESS BRAKE
4	Team3	WELD INSP		Team1	WELD INSP
5	Team4	WELD		Team1	WELD
6	Team1			Team2	LASER CUT
7	Team2			Team2	PRESS BRAKE
8	Team3			Team2	WELD INSP
9	Team4			Team2	WELD
10	Team1			Team3	LASER CUT
11	Team2			Team3	PRESS BRAKE
12	Team3			Team3	WELD INSP
13	Team4			Team3	WELD
14	Team5			Team4	LASER CUT
15				Team4	PRESS BRAKE
16				Team4	WELD INSP
17				Team4	WELD
18				Team5	LASER CUT
19				Team5	PRESS BRAKE
20				Team5	WELD INSP
21				Team5	WELD
Main

Cell Formulas
Range	Formula
D2:E21	D2	=LET(Uni,UNIQUE(A2:A14),Cu,COUNTA(Uni),Jobs,B2:B5,Cj,COUNTA(Jobs),CHOOSE({1,2},INDEX(Uni,(SEQUENCE(Cu*Cj)-1)/Cj+1),INDEX(Jobs,MOD(SEQUENCE(Cu*Cj,,0),Cj)+1)))
Dynamic array formulas.

 

[mattcocks]

Using a single formula to fill both columns, this is the simplest I've found so far.

Book1 (version 1)19.2.21.xlsx
	A	B	C	D	E	F
1	Team 1	Team 1	Laser Cut
2	Team 2	Team 2	Brake Press		Team 1	Laser Cut
3	Team 3	Team 3	Weld Insp		Team 1	Brake Press
4	Team 4	Team 4	Weld		Team 1	Weld Insp
5	Team 1				Team 1	Weld
6	Team 2				Team 2	Laser Cut
7	Team 3				Team 2	Brake Press
8	Team 4				Team 2	Weld Insp
9	Team 1				Team 2	Weld
10	Team 2				Team 3	Laser Cut
11	Team 3				Team 3	Brake Press
12	Team 4				Team 3	Weld Insp
13					Team 3	Weld
14					Team 4	Laser Cut
15					Team 4	Brake Press
16					Team 4	Weld Insp
17					Team 4	Weld
Sheet9

Cell Formulas
Range	Formula
B1:B4	B1	=SORT(UNIQUE(A1:A12))
E2:F17	E2	=CHOOSE({1,2},INDEX(B1#,INT(SEQUENCE(COUNTA(B1#)*COUNTA(C1:C4),1,1,1/COUNTA(C1:C4)))),INDEX(C1:C4,MOD(SEQUENCE(COUNTA(B1#)*COUNTA(C1:C4),1,0),COUNTA(B1#))+1))
Dynamic array formulas.

Hi Jason, do you have the solution if there is a third data column involved?

 

[Fluff]

As this is a different question, please start a thread of your own & post some sample data showing what you need. Thanks