Insert blank row after name change in Excel - Dynamic Formula

This video shows how to insert a blank row after every name change using Excel dynamic array formulas and Lambdas

Link to workbook:

[JoseAntonioMorato]

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(b<>OFFSET(b, 1, 0), EXPAND(b, 2, , ""), b)))), -1) 🤗

[novellaalessi]

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!

[mariebergstrm]

Hi! I cannot find the video for inserting rows in Two dimentional arrays. Where can I find it? 😊

[slystallone]

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(", 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.

[ziggle]

One question -- you described the offset function as the function that is not to be used. Why is that?🤔

[ankitdosi]

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 "

[wayneedmondson]

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(b<>OFFSET(b, 1, , ), EXPAND(b, 2, , ""), b)))) Thumbs up!!

[sunnybaggu]

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)

[pro]

I'm ….in!!!
Waiting for the magic by Excel.

[williamarthur]

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.

[ziggle]

Well, I have always wondered how I would use the EXPAND function. You have answered that question!😊

[IvanCortinas_ES]

As always a very logical solution with very clear processes. Very good use of OFFSET.
Thanks for the explanation Victor.

[waitplanwp]

What can I say. It has worked brilliantly. Thank you so much my beloved master.