Insert blank row at every value change dynamically
HTML-код
- Опубликовано: 16 июл 2024
- 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:
docs.google.com/spreadsheets/...
"This is not hype, this is a fact." = ❤ !!! a quote Momehnt.
Hahahahaha, Carlos, it was actually a fact 😁
Thank you Victor. I like your "I'm out!" at the end.
Hahahaha..I guess I like it as well.
Thanks Victor, a nice little trick and good use of the Expand function
The fundamental idea is simple, the execution is a little more involving though. Thanks for your feedback
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)
Thanks sunny for your comments and feedback. There's always more than 1 way to solve most Excel problems, so I appreciate this alternative
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))
Very Clean. I love your approach. very straight to the point
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 😁
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
@@ExcelMoments I'm only junior lambda master 🤣
This is very useful in the presentation of certain reports. Thank you very much Victor. Great video!!!
Thanks Ivan, you are absolutely right. Appreciate your feedback always
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.
Hahahahah, Chess and Reduce 😁😁 I guess I would be a good chess player if I chose to learn it
Another brilliant video Victor. Great to see the use of EXPAND
Thanks for your feedback. Yea, EXPAND is very useful in this regard
JUST WOW! LOVE U SO MUCH VICTOR FOR ALL THE YEARS!!!
This is an amazing one. So much respect for you Sir
Thank you, Prof. Hope to do more
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_ _ ______________________
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) 🤗
I love your modification. The REDUCE starts with the header as the initial value, which is really good
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.
❤
Thanks