Insert blank row at every value change dynamically

[@ClemenDK]

I have been sitting with multiple functions and lists, helper columns etc., and chatGPT as aid to no avail, and you basically solved the entirety of my project in one formula requireing only minor tweaks. Amazing, thank you so frikkin much dude

[@spilledgraphics]

"This is not hype, this is a fact." = ❤ !!! a quote Momehnt.

[@ExcelMoments]

Hahahahaha, Carlos, it was actually a fact 😁

[@markpodesta4605]

Thank you Victor. I like your "I'm out!" at the end.

[@ExcelMoments]

Hahahaha..I guess I like it as well.

[@olukunlebabajide6089]

Well done Prof! I can relate cos I did same thing for a client and another one with subtotal added.
LET(data,A5:D256,c,CHOOSECOLS(data,1),s,VSTACK(c,UNIQUE(c)),d,IFERROR(HSTACK(s,data),""),DROP(SORT(d,1,1),,1))

[@ExcelMoments]

Very Clean. I love your approach. very straight to the point

[@stevereed5776]

Thanks Victor, a nice little trick and good use of the Expand function

[@ExcelMoments]

The fundamental idea is simple, the execution is a little more involving though. Thanks for your feedback

[@IvanCortinas_ES]

This is very useful in the presentation of certain reports. Thank you very much Victor. Great video!!!

[@ExcelMoments]

Thanks Ivan, you are absolutely right. Appreciate your feedback always

[@petercompton538]

Another brilliant video Victor. Great to see the use of EXPAND

[@ExcelMoments]

Thanks for your feedback. Yea, EXPAND is very useful in this regard

[@davidakomolafe3609]

This is an amazing one. So much respect for you Sir

[@ExcelMoments]

Thank you, Prof. Hope to do more

[@williamarthur4801]

Just as below WOW, your ability to visualize when using reduce in amazing, yes I know what it does, but once away from a basic S & C or S * C etc I cant imagine what's happening, and it's difficult to test, I bet you're a good chess player.

[@ExcelMoments]

Hahahahah, Chess and Reduce 😁😁 I guess I would be a good chess player if I chose to learn it

[@waitplanwp4129]

JUST WOW! LOVE U SO MUCH VICTOR FOR ALL THE YEARS!!!

[@BillSzysz1]

Hi Victor, interesting issue and neat solution 👍😁
A slightly different approach using UNIQUE and FILTER instead of IFERROR and SEQUENCE
=LAMBDA(rng, ind,
LET( s, SORT(rng, ind,1),
col, INDEX(s,, ind),
unq, UNIQUE(col),
end, REDUCE("", unq, LAMBDA(a, i, VSTACK(a, VSTACK(FILTER(s, col=i),EXPAND({""},,COLUMNS(s),""))))),
DROP(DROP(end, 1),-1)))(B4:G13,3)
regards 😁

[@ExcelMoments]

Thanks Bill, the "Lambda Master",. I love this alternative; I would play around your solution. Good to see that REDUCE/EXPAND still find a way to feature in most of the alternatives

[@BillSzysz1]

@@ExcelMoments I'm only junior lambda master 🤣

[@JoseAntonioMorato]

Dear Momoh.
I loved your solution, but I made a small modification.
I eliminated the DROP function and included a header:
=LAMBDA(header,oldrng,ind,
LET(rng,SORT(oldrng,ind,1),
REDUCE(header,SEQUENCE(ROWS(rng)),
LAMBDA(a,b,VSTACK(a,IFERROR(IF(INDEX(rng,b,ind)=INDEX(rng,b+1,ind),INDEX(rng,b,0),
EXPAND(INDEX(rng,b,0),2,,"")),INDEX(rng,b,0)))))))(B3:G3,B4:G13,2) 🤗

[@ExcelMoments]

I love your modification. The REDUCE starts with the header as the initial value, which is really good

[@surekantbangalore3499]

Super Trick. Hatsup,
Dear We are request to make video on excel data to send Watsapp each groupwise where mention at excel column. we are hope that u can do that video waiting_ _ ______________________

[@JasonGreene-ld8xi]

Hi Victor. This is great. Instead of inserting a blank row, how would I insert a row that input a value in one column then summed the values in the columns with numeric values? For example, sorting when the Ind = 3, it would insert a row with "Bags" in column 3 and summed each of the numeric columns 4, 5, and 6. Going to look through your channel videos to see if you have already answered this somewhere.

[@tamersalem7542]

❤

[@ExcelMoments]

Thanks

[@auroracanter8798]

I just wanted my sheet easier to read. I forgot how to do formulas. I never use excel. I am now in my new job. I WILL JUST KEEP IT SIMPLE. Now I am gonna have to do excel class all over again😢.

[@sunnybaggu785]

Thank you Victor sir, amazing content and a clean presentation with good explanation of the solution.
Always eager to see your content for learning, Thank you.
Even i have tried to participate without the use of EXPAND function ,
logic inspired from one of the LinkedIn challenges (logic taken from @excel wizard Bo Rydobon) and modified below for this problem:
=LAMBDA(_tbl, _ind,
LET(
_num, COLUMNS(_tbl),
_rng, SORT(_tbl, _ind, 1),
_col, INDEX(_rng, , _ind),
WRAPROWS(
TOCOL(
HSTACK(
_rng,
IF(IF(SEQUENCE(, _num), _col DROP(_col, 1)), "", 1 / x)
),
3
),
_num
)
)
)(B4:G13, 3)

[@ExcelMoments]

Thanks sunny for your comments and feedback. There's always more than 1 way to solve most Excel problems, so I appreciate this alternative