Really helpful! I needed to repeat more than one column. I couldn't have done this without your step-by-step: LAMBDA(value,repeat, CHOOSEROWS(value,XMATCH(SEQUENCE(SUM(repeat)), SCAN(, repeat, LAMBDA(a,b, a+b)),1)))
Whoever you are, thank you! You've just fixed a problem I've spent the past 2 weeks on. The formula in the video doesn't work if you have any blank cells but yours does!
For some reason I don't have Vstack... so the following worked for me: (Note : I didn't put it into a named lambda function) =XLOOKUP(SEQUENCE(sum(num_repeat),1,1,1),SCAN(0,num_repeat,lambda(a,b,a+b)),values,"error",1)
Hello. Interesting procedure. This could be done also with the following formula: =LAMBDA(value, repeat, TEXTSPLIT(TEXTJOIN("/", TRUE, MAP(value, repeat, LAMBDA(a, b, REPT(a & "/", b)))),, "/", TRUE))
this is really great job. but I have a problem when the repeat count is 0. While the value with 0 next to it is repeated as the number of repetitions of the lower value, and the lower value does not appear.
@heecan , you can remedy that with the inclusion of several 'Filter' function calls Here's what Karina provided: =LAMBDA(values,num_repeat, XLOOKUP(SEQUENCE(SUM(num_repeat)),VSTACK(1,SCAN(1,num_repeat,LAMBDA(a,b,a+b))),VSTACK(values,""),,-1)) Here's a version that will also account for values less than 1 (i.e. 0 & negative numbers) =LAMBDA(values,num_repeat, XLOOKUP(SEQUENCE(SUM(FILTER(num_repeat,num_repeat>0))),VSTACK(1,SCAN(1,FILTER(num_repeat,num_repeat>0),LAMBDA(a,b,a+b))),VSTACK(FILTER(values,num_repeat>0),""),,-1))
Hey! How do you include ''calling number'' in the formula? By exemple: Terry: (repeat 30 times the name but must have number 7 and 11 at least) Bob: call number, 1 and 10 (repeat 10 times the name but must have number 1 and 10 at least) ? Those ''calling number, could be chosen from another cell: 7,11) by name. Thanks you.
I'm trying to use this custom formula in Google Sheets. However, error that says "Function LAMBDA should be followed by a call containing the actual values." although I followede every step shown. Maybe I missed something?
How to count a Specific repeated word array in excel Example PPPPPPWWPPPPPP = 2 means P is repeated 2 times with an array of 6. I need formula for the same.
SEQUENCE, or XLOOKUP, etc. are definitely not formulas, they are Excel functions. I simply do not understand why so many Excel experts make this confusion.
When i try you menthod, i see 1 problem. That's is the Source for the XLOOKUP must be exact to the value. In Order to apply automatic on dynamic range, i change your fomula a bit: - Put the Fomula in E5 - Value in B5:B10000 - Repeat time in C5:C10000 =XLOOKUP(SEQUENCE(SUM($C$5:$C$10000)), VSTACK(1,SCAN(1,FILTER($C$5:$C$10000,($C$5:$C$10000"")*($C$5:$C$100000)),LAMBDA(a,b,a+b))), IF(ISERROR(MATCH(0,$C$5:$C$10000,0))=TRUE, VSTACK(FILTER($B$5:$B$10000,$B$5:$B$10000""),0), VSTACK(FILTER($B$5:$B$10000,($C$5:$C$10000"")*($C$5:$C$100000)),0)),,-1) I assume the Number of Row can extend much much more, in exchange of slower calculation. Anyway, this still the fastest method ever to repeat a value x times. Thank you so much!
![Summer Walker - Heart Of A Woman [Official Lyric Video]](http://i.ytimg.com/vi/SFddctbZzw8/mqdefault.jpg)
Really helpful! I needed to repeat more than one column. I couldn't have done this without your step-by-step:
LAMBDA(value,repeat,
CHOOSEROWS(value,XMATCH(SEQUENCE(SUM(repeat)), SCAN(, repeat, LAMBDA(a,b, a+b)),1)))
Whoever you are, thank you! You've just fixed a problem I've spent the past 2 weeks on. The formula in the video doesn't work if you have any blank cells but yours does!
This video should get an award! Thank you for helping me out!
Wow.. this went beyond what I expected and needed. Thank you for making this video.
This was incredibly useful and well put together. You are a true wizard. :D
Ma'am, you have made an amazing video, really really helpful: saved my life at 5 in the morning!
You just blew my mind when you when you made this it's own formula 🤯 bravo
For some reason I don't have Vstack... so the following worked for me:
(Note : I didn't put it into a named lambda function)
=XLOOKUP(SEQUENCE(sum(num_repeat),1,1,1),SCAN(0,num_repeat,lambda(a,b,a+b)),values,"error",1)
Astounding mastery of excel! Thank you! Was looking for exactly this! But received a lot more!
Thank you, exactly what I needed. Brilliant tutorial.
This is awesome! Thank you Karina for creating/sharing this video!
Thank you so much for this fantastic video you single handedly saved my sanity!
Exactly what I needed, great explanation!
really simple, just what I'm looking for.
Excellent explanation, thank you very much!
Hello. Interesting procedure. This could be done also with the following formula:
=LAMBDA(value, repeat, TEXTSPLIT(TEXTJOIN("/", TRUE, MAP(value, repeat, LAMBDA(a, b, REPT(a & "/", b)))),, "/", TRUE))
Wow! This is brilliant! 👏👏👏
Really helpful Video !!!
Keep Going ✨
Wow, that is crazy, saves tons of my work!
You rock! this worked perfectly for me working with dates! subscribed!
WOW..... you are the best
Simply amazing!!
Hi there! Great video. But my "=repeat" formula doesn't show. Seems like I don't have it. What could be the problem.
this is really great job. but I have a problem when the repeat count is 0. While the value with 0 next to it is repeated as the number of repetitions of the lower value, and the lower value does not appear.
@heecan , you can remedy that with the inclusion of several 'Filter' function calls
Here's what Karina provided:
=LAMBDA(values,num_repeat,
XLOOKUP(SEQUENCE(SUM(num_repeat)),VSTACK(1,SCAN(1,num_repeat,LAMBDA(a,b,a+b))),VSTACK(values,""),,-1))
Here's a version that will also account for values less than 1 (i.e. 0 & negative numbers)
=LAMBDA(values,num_repeat,
XLOOKUP(SEQUENCE(SUM(FILTER(num_repeat,num_repeat>0))),VSTACK(1,SCAN(1,FILTER(num_repeat,num_repeat>0),LAMBDA(a,b,a+b))),VSTACK(FILTER(values,num_repeat>0),""),,-1))
@@branmoxley Just what I was looking for, thank you!
@@qwaaackrs You're welcome!
Simply brilliant!
Great! You should have 272.000 subscribers instead of 27.200.
Hey! How do you include ''calling number'' in the formula? By exemple:
Terry: (repeat 30 times the name but must have number 7 and 11 at least)
Bob: call number, 1 and 10 (repeat 10 times the name but must have number 1 and 10 at least) ?
Those ''calling number, could be chosen from another cell: 7,11) by name.
Thanks you.
Is there a blanket way of getting a range of values to repeat a set number of times?
Wow! I needed this! :D thanks!
Very helpful! Thanks for the video!!!!
Thank you!!! Very helpfull!!!
Thanks !!!! This helped me a lot !!
Absolutely genius!
Awesome 🎉
hi thats great!! Thankyou so much. can anyone suggest how to do it in power query
How could we solve this problem for Google sheet , Please help
Can you do this in reverse? Condense a full array of words and add how many times it is set? Without knowing the words in advance
Great video
Your explanation nailded it
Very clever solution
This is WOW!!!
I'm trying to use this custom formula in Google Sheets. However, error that says "Function LAMBDA should be followed by a call containing the actual values." although I followede every step shown. Maybe I missed something?
=LAMBDA(values,num_repeat,
XLOOKUP(SEQUENCE(SUM(num_repeat)),VSTACK(1,SCAN(1,num_repeat,LAMBDA(a,b,a+b))),VSTACK(values,""),,-1))(values,num_repeat
)
facing same problem , could you help me to solve this problem actually I am in a project ,where I've daily go through with this problem , help me
Brilliant!
How to count a Specific repeated word array in excel Example
PPPPPPWWPPPPPP = 2 means P is repeated 2 times with an array of 6.
I need formula for the same.
Many thanks
Impressive
How can we do this when data is filtered
Vstack formula not working in my excel :(
It is not processing bulk data
god bless you
Thank you!
супер )
not working
SEQUENCE, or XLOOKUP, etc. are definitely not formulas, they are Excel functions. I simply do not understand why so many Excel experts make this confusion.
Relax, excel nazi
OMG
When i try you menthod, i see 1 problem. That's is the Source for the XLOOKUP must be exact to the value. In Order to apply automatic on dynamic range, i change your fomula a bit:
- Put the Fomula in E5
- Value in B5:B10000
- Repeat time in C5:C10000
=XLOOKUP(SEQUENCE(SUM($C$5:$C$10000)),
VSTACK(1,SCAN(1,FILTER($C$5:$C$10000,($C$5:$C$10000"")*($C$5:$C$100000)),LAMBDA(a,b,a+b))),
IF(ISERROR(MATCH(0,$C$5:$C$10000,0))=TRUE,
VSTACK(FILTER($B$5:$B$10000,$B$5:$B$10000""),0),
VSTACK(FILTER($B$5:$B$10000,($C$5:$C$10000"")*($C$5:$C$100000)),0)),,-1)
I assume the Number of Row can extend much much more, in exchange of slower calculation. Anyway, this still the fastest method ever to repeat a value x times. Thank you so much!