Insert blank row after name change in Excel - Dynamic Formula
HTML-код
- Опубликовано: 4 апр 2023
- This video shows how to insert a blank row after every name change using Excel dynamic array formulas and Lambdas
Link to workbook:
docs.google.com/spreadsheets/...
Well, I have always wondered how I would use the EXPAND function. You have answered that question!😊
Another great one Victor! If you don't define anything for the initial_value of REDUCE, you can skip the first DROP, as in: =REDUCE(,rng[Names],LAMBDA(a,b,VSTACK(a,IF(bOFFSET(b,1,,),EXPAND(b,2,,""),b)))) Thumbs up!!
Thanks Wayne, very much appreciated. That argument has always had , at least to me very unpredictable results, depending on the scenario. You watch some of my videos and see me go in and out of it
What can I say. It has worked brilliantly. Thank you so much my beloved master.
You are welcome, wp
I'm ….in!!!
Waiting for the magic by Excel.
I am eagerly waiting 🤣
As always a very logical solution with very clear processes. Very good use of OFFSET.
Thanks for the explanation Victor.
You are welcome Ivan. Just one of many ways to approach the problem
That was brilliant, I have not used expand before, I shall have to break this all down and go through it,
I spend so much time in p query it's kinda nice to just do some excel stuff.
That's the whole idea, just pushing yourself using Excel formulas
nice and amazing
Thanks for the feedback
😊
Hi Momoh. I really appreciate these videos and also to turning me onto Excel BI as I am learning many things. Not to Hijack this thread; but there was a recent challenge on Excel BI, Challenge 151. I would love for you to do a video on this as I can't figure this one out; especially the use of the OR statement in the following formula: =MAP(A2:A10,LAMBDA(a,TEXTJOIN(", ",,MAP(SEQUENCE(10)-1,LAMBDA(n,IF(OR(ISERR(FIND(n,TEXTSPLIT(a,",")))),,n)))))) as there is only one argument in the OR statement. I can kind of see what is accomplishing; but I just can't figure out why/how it is working. Thanks again for all that you do.
Hello SLy, been off Videos/Excel for a bit. I will sure take a look and make a video when i do have some time. i have some outstanding recordings, which should be of value too
Dear Momoh,
By including the initial value in the REDUCE function as "Names", a DROP is eliminated in the formula:
=DROP(REDUCE("Names",rng[Names],LAMBDA(a,b,VSTACK(a,SE(bOFFSET(b,1,0),EXPAND(b,2,,""),b)))),-1) 🤗
Great Alternative. Love it
Thank you sir, that's really a different approach,
I would also like to share one alternative for the same below:
=DROP(DROP(REDUCE("",UNIQUE(rng[Names]),LAMBDA(a,v,VSTACK(a,VSTACK(FILTER(rng[Names],rng[Names]=v),"")))),1),-1)
Nice one Sunny, I guess the only challenge is when a name is repeated somewhere down the line, then this may not yield the right result
@@ExcelMoments true sir, thank you for sharing the insights .
Amazing! I am looking for the video you mentioned at the end of this one, to add an empty row in table of data with multiple lines and columns. I wasn't able to find it. Could you please help me?
Thanks!
Here is the new video(for 2-D):
ruclips.net/video/a7dZnBBb5Yg/видео.htmlsi=To_BqSDnNGwvBqRB
Dear sir can make a video" insert row on base cell value greater than 0 " i want formula as data enter in cell then click enter than itself insert new row below "
One question -- you described the offset function as the function that is not to be used. Why is that?🤔
Hahahahah, I wasn't speaking for myself ,,,,🤣🤣 Most people say that because of volatility , meaning potential to slow down calculation time
Hi! I cannot find the video for inserting rows in Two dimentional arrays. Where can I find it? 😊
Do you want to insert rows when a particular column changes, but Yes, a 2-D array, multiple columns but based on a single column?
You can find it here now ruclips.net/video/a7dZnBBb5Yg/видео.htmlsi=To_BqSDnNGwvBqRB