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

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
681
Office Version
  1. 365
Platform
  1. Windows
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?

Snag_16619909.png



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
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,405
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top