Repeating rows

[sharshra]

I have to repeat few rows in a table. I´m using REPT function in the formula, but it is throwing an error. Can the experts please advise?

Source table has multiple dept for each name & role. I have to split dept & create one line item for each dept. Result should be as shown in the desired output table below. But the REPT seems to be throwing error as seen in the actual output. Seems to be something wrong in this line. Can this be fixed? Or any suggestions for a better solution to get the desired output?

b, DROP(REDUCE("",Table9,LAMBDA(x,y,VSTACK(x,REPT(CHOOSECOLS(y,1,2),COLUMNS(TEXTSPLIT(CHOOSECOLS(y,3),",")))))),1),

Source table:

excel problems.xlsx
	A	B	C
1	name	role	dept
2	dsfdf	a12	221, 67, 434
3	fdhg	h56, i89	709, 4, 74, 491
4	mczn	j	a75, 221, 74, 4, 709, t55
5	qeknb	8w	67
Sheet7

Actual output:

excel problems.xlsx
	K
2	#VALUE!
Sheet7

Cell Formulas
Range	Formula
K2	K2	=LET( n,Table9[name],r,Table9[role],d,Table9[dept], a,DROP(REDUCE("",d,LAMBDA(x,y,VSTACK(x,TOCOL(TRIM(TEXTSPLIT(y,",")),3)))),1), b, DROP(REDUCE("",Table9,LAMBDA(x,y,VSTACK(x,REPT(CHOOSECOLS(y,1,2),COLUMNS(TEXTSPLIT(CHOOSECOLS(y,3),",")))))),1), HSTACK(b,a))

Desired output:

excel problems.xlsx
	E	F	G
1	name	role	dept
2	dsfdf	a12	221
3	dsfdf	a12	67
4	dsfdf	a12	434
5	fdhg	h56, i89	709
6	fdhg	h56, i89	4
7	fdhg	h56, i89	74
8	fdhg	h56, i89	491
9	mczn	j	a75
10	mczn	j	221
11	mczn	j	74
12	mczn	j	4
13	mczn	j	709
14	mczn	j	t55
15	qeknb	8w	67
Sheet7

 

[djclements]

Try the following:

=LET(
    d, ",",
    n, LEN(Table9[dept])-LEN(SUBSTITUTE(Table9[dept], d, ))+1,
    c, SEQUENCE(, MAX(n)),
    f, LAMBDA(a, TOCOL(IFS(n>=c, a), 2)),
    v, TOCOL(TRIM(TEXTBEFORE(TEXTAFTER(d&Table9[dept]&d, d, c), d)), 2),
    VSTACK(Table9[#Headers], HSTACK(f(Table9[name]), f(Table9[role]), IFERROR(VALUE(v), v)))
)

 

[sharshra]

Brilliant!!! Thanks, @djclements