Insert blank row at every value change dynamically

Insert a blank row at every change in a specified column from the data set. I walk through building a robust solution that gives the user a lot of flexibility to insert blanks based on the column of choice

00:00 Introduction
01:07 A case for a dynamic solution
01:35 Caveats
02:22 Building blocks for the solution
04:02 Expand function to insert blank row
05:20 Reduce function-quick overview
06:51 Solution commences
12:50 fixing a REF error within the data
15:00 End of the simple solution
15:22 Making the solution more robust(user can choose column)
18:18 Sorting y column of choice before inserting blanks
21:30 Final Thoughts

Workbook:

[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 <3 <3 <3

[markpodesta]

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

[petercompton]

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

[spilledgraphics]

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

[IvanCortinas_ES]

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

[stevereed]

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

[waitplanwp]

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

[olukunlebabajide]

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))

[davidakomolafe]

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

[BillSzysz]

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,
DROP(DROP(end, 1), -1)))(B4:G13, 3)
regards 😁

[williamarthur]

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.

[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),
🤗

[JasonGreene-ldxi]

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.

[surekantbangalore]

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_ _

[sunnybaggu]

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)