No video :(

Insert blank row at every value change dynamically

Подписаться 6 тыс.

Просмотров 2,5 тыс.

50% 1

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.co...

Опубликовано:

17 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 29

@spilledgraphics 9 месяцев назад

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

@ExcelMoments 9 месяцев назад

Hahahahaha, Carlos, it was actually a fact 😁

@markpodesta4605 9 месяцев назад

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

@ExcelMoments 9 месяцев назад

Hahahaha..I guess I like it as well.

@stevereed5776 9 месяцев назад

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

@ExcelMoments 9 месяцев назад

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

@IvanCortinas_ES 9 месяцев назад

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

@ExcelMoments 9 месяцев назад

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

@olukunlebabajide6089 9 месяцев назад

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 9 месяцев назад

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

@petercompton538 9 месяцев назад

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

@ExcelMoments 9 месяцев назад

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

@waitplanwp4129 9 месяцев назад

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

@davidakomolafe3609 9 месяцев назад

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

@ExcelMoments 9 месяцев назад

Thank you, Prof. Hope to do more

@BillSzysz1 9 месяцев назад

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 9 месяцев назад

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 9 месяцев назад

@@ExcelMoments I'm only junior lambda master 🤣

@surekantbangalore3499 9 месяцев назад

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

@JoseAntonioMorato 9 месяцев назад

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 9 месяцев назад

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

@williamarthur4801 5 месяцев назад

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 5 месяцев назад

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

@JasonGreene-ld8xi 4 месяца назад

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 9 месяцев назад

❤

@ExcelMoments 9 месяцев назад

Thanks

@sunnybaggu785 9 месяцев назад

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 9 месяцев назад

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