Split duplicated by the number of reps

Question

I have a sheet need to be split and transpose, the other col will be duplicated by the number of Reps and the other column Only the first record from each rep gets copied, the "repeat" is left blank

Heres the sheet for your reference https://docs.google.com/spreadsheets/d/1IhjE3r6DpOF-tcnzk9ITvLLLv-8KzyMVJ-2BM3_8KRM/edit#gid=2086741331

https://docs.google.com/spreadsheets/d/1IhjE3r6DpOF-tcnzk9ITvLLLv-8KzyMVJ-2BM3_8KRM/edit#gid=2086741331

@rockinfreakshow I have new data sheet, can you try it there? I lost track when there are multiple columns. thank you "new" sheet in the file — Rhenz Idol II San Pedro
– Rhenz Idol II San Pedro, Commented Apr 3, 2024 at 8:18

rockinfreakshow · Accepted Answer · 2024-04-03 08:49:31Z

0

You may try:

=reduce(A1:G1,F2:F6,lambda(a,c,ifna(vstack(a,if(c="",tocol(,1),let(Σ,split(c,"; ",0),a,offset(c,,-5),g,offset(c,,1),Λ,counta(Σ),hstack(wrapcols(a,Λ,a),offset(c,,-4,,4),tocol(Σ),wrapcols(g,Λ,g))))))))

UPDATED FORMULA:

=reduce(A1:J1,B2:B3,lambda(a,c,ifna(vstack(a,if(c="",tocol(,1),let(Σ,split(c,"; ",0),a,offset(c,,-1),cdef,offset(c,,1,,4),ij,offset(c,,7,,2),Λ,counta(Σ),hstack(wrapcols(a,Λ,a),tocol(Σ),chooserows(cdef,sequence(Λ,1,1,0)),offset(c,,5,,2),chooserows(ij,sequence(Λ,1,1,0)))))))))

edited Apr 3, 2024 at 8:49

answered Apr 3, 2024 at 7:57

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rhenz Idol II San Pedro Over a year ago

I have new data sheet, can you try it there? I lost track when there are multiple columns. thank you "new" sheet in the file

rockinfreakshow Over a year ago

try the updated formula added to the answer

Collectives™ on Stack Overflow

Split duplicated by the number of reps

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related