I work in insurance. I am building a template that can work with our customers who have a random number of health plan options (possibly more than one each of: health plan, dental plan, vision plan). The goal is to do a total plan design cost. There are 4 options with each potential plan choice: Employee Only (EO), Employee Plus Spouse (ESP), Employee Plus Children (ECH), Employee Plus Family (FAM).
I have a dynamic named range that concatenates all health plans, followed by all dental plans, followed by all vision plans, into a single column. This data set can be a minimum of 3 (1 health, 1 dental, 1 vision) to a maximum of who knows (as each type of policy can have many options).
What I need is to create a dynamic table, that has the first column be the plan name (ie, health1,health2,health3,dental1,dental2,vision1,vision2,vision3, etc...) in the first column. The second column would have EO, ESP, ECH, FAM (for the 4 options I listed earlier) for each item in the 1st column, then subsequent columns would have premium data that I can deal with.
The problem I'm having is coming up with a way to create this dynamic table where the health1,health2, etc. stuff is inserted in every 4th cell, so the 2nd column gives the 4 different options EO, ESP, ECH, FAM for each of the entries health1,health2, etc.
Anybody have a suggestion how to expand a dynamic named range so I can have it populate the first column of a dynamic length table every 4th row? This is a sample:
The original range on the left is dynamic. The size definition of the name already automatically enlarges to encompass the entire range no matter how large it is (even thousands of rows). I simply need each entry in the original dynamic named range to be repeated 4 times, so EO/ESP/ECH/FAM can be placed in the next column next to it.
@BillY2305 REDUCE() function may give you desired output. See the attached file.
=DROP(REDUCE("",InsurancePlan,LAMBDA(a,x,VSTACK(a,HSTACK(EXPAND(x,4,1,x),VSTACK("EO","ESP","ECH","FAM"))))),1)
@BillY2305 Another option for performing a basic cross join of two vectors is as follows:
=LET(
arr, LAMBDA(n, TOCOL(CHOOSE(n, Plans, TOROW(Options)))),
HSTACK(arr({1}), arr({2}))
)
See attached, if needed...