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

#### VBE313

##### Well-known Member
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?

VBA Code:
``````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``````

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### jasonb75

##### Well-known Member
Using a single formula to fill both columns, this is the simplest I've found so far.
Book1 (version 1)19.2.21.xlsx
ABCDEF
1Team 1Team 1Laser Cut
2Team 2Team 2Brake PressTeam 1Laser Cut
3Team 3Team 3Weld InspTeam 1Brake Press
4Team 4Team 4WeldTeam 1Weld Insp
5Team 1Team 1Weld
6Team 2Team 2Laser Cut
7Team 3Team 2Brake Press
8Team 4Team 2Weld Insp
9Team 1Team 2Weld
10Team 2Team 3Laser Cut
11Team 3Team 3Brake Press
12Team 4Team 3Weld Insp
13Team 3Weld
14Team 4Laser Cut
15Team 4Brake Press
16Team 4Weld Insp
17Team 4Weld
Sheet9
Cell Formulas
RangeFormula
B1:B4B1=SORT(UNIQUE(A1:A12))
E2:F17E2=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

##### MrExcel MVP, Moderator
Another option, if you have the LET function.
+Fluff 1.xlsm
ABCDE
1
2Team1LASER CUTTeam1LASER CUT
3Team2PRESS BRAKETeam1PRESS BRAKE
4Team3WELD INSPTeam1WELD INSP
5Team4WELDTeam1WELD
6Team1Team2LASER CUT
7Team2Team2PRESS BRAKE
8Team3Team2WELD INSP
9Team4Team2WELD
10Team1Team3LASER CUT
11Team2Team3PRESS BRAKE
12Team3Team3WELD INSP
13Team4Team3WELD
14Team5Team4LASER CUT
15Team4PRESS BRAKE
16Team4WELD INSP
17Team4WELD
18Team5LASER CUT
19Team5PRESS BRAKE
20Team5WELD INSP
21Team5WELD
Main
Cell Formulas
RangeFormula
D2:E21D2=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.

Replies
2
Views
189
Replies
3
Views
163
Replies
1
Views
50
Replies
1
Views
107
Replies
3
Views
177

1,127,008
Messages
5,622,140
Members
415,880
Latest member
Bruce0203

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back