0

I've a google sheet formula which I am using to repeat values n number of times, formula is this:-

=ArrayFormula(LAMBDA(values,num_repeat,XLOOKUP(SEQUENCE(SUM(num_repeat)),VSTACK(1,SCAN(1,num_repeat,LAMBDA(init,repeat,init+repeat))),VSTACK(values,""),,-1))(A1:A,B1:B))

Here A1:A contains Text values which I want to repeat number of times in range B1:B, formula works absolutely fine, but it is very slow, I have large dataset on which I am applying this formula, can anyone help me to improve the formula getting the same result. I think sequence is causing issue, although I am not sure, or there any other alternative without making the formula too big.

Thanks

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
vector
  • 1,032
  • 1
  • 4
  • 16

2 Answers2

1

You may try the below formula-

=REDUCE("Repeat Texts",B2:INDEX(B2:B,COUNTA(B2:B)),
LAMBDA(a,x,VSTACK(a,MAKEARRAY(x,1,LAMBDA(r,c,INDEX(A:A,ROW(x)))))))

LAMBDA() formula would be-

=LAMBDA(Input1,Input2,REDUCE("Repeat Texts",Input2,
LAMBDA(a,x,VSTACK(a,MAKEARRAY(x,1,LAMBDA(r,c,INDEX(Input1,ROW(x))))))))(A:A,B2:INDEX(B2:B,COUNTA(B2:B)))

Another easy solution can be-

=TOCOL((INDEX(SPLIT(REPT(TOCOL(A2:A,1)&"|",TOCOL(B2:B,1)),"|"))),1)

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Thanks Harun it worked, can you point out what was causing my formula was working slow, please if you can? – vector Apr 05 '23 at 06:22
  • @vector Can you please try this one `=TOCOL((INDEX(SPLIT(REPT(TOCOL(A2:A,1)&"|",TOCOL(B2:B,1)),"|"))),1)`. – Harun24hr Apr 05 '23 at 06:23
  • @vector You formula was calculating whole column and inner loop (nested iterative function like SCAN) made the formula slow. ArrayFormula calculate for full column. – Harun24hr Apr 05 '23 at 06:26
  • `=TOCOL((INDEX(SPLIT(REPT(TOCOL(A2:A,1)&"|",TOCOL(B2:B,1)),"|"))),1)` this didn;t worked as expected, it is creating another column in my dataset – vector Apr 05 '23 at 06:28
  • can you please explain why you used `B2:INDEX(B2:B,COUNTA(B2:B))` – vector Apr 05 '23 at 06:47
  • 1
    @vector Here `B2:INDEX(B2:B,COUNTA(B2:B))` will return a array of values as well cell reference from **B2** to last non empty cell in column B (Assume you do not have any blank rows inside data). If you have blank row, then you have to use different approach. See this [post](https://stackoverflow.com/a/46884012/5514747) by @TheMaster – Harun24hr Apr 05 '23 at 06:50
  • I only have blank rows at the bottom of dataset – vector Apr 05 '23 at 06:52
  • Hope above explanation make it clear to understand. – Harun24hr Apr 05 '23 at 07:12
  • I am having issue with this formula, it is not repeating values like ideally it should be , on my dataset it is repeating other value more and removing count of other, you'll understand this more on this sample sheet :- https://docs.google.com/spreadsheets/d/1sYZf0CAKr1NvgGBV8rJwTt3rwdBhKeYrwp1UGMGL6GA/edit#gid=0 – vector Apr 06 '23 at 10:27
  • While your data starts from `B1` then the lambda input will be `B1:INDEX(B1:B,COUNTA(B1:B))`. – Harun24hr Apr 07 '23 at 01:53
1

You may try:

=tocol(map(A:A,B:B,lambda(a,b,if(a="",,wraprows(a,b,a)))),3)

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19