Hey Gasper, I wonder if you can elaborate on this use of sequence, I need to have a sequence of numbers that have a break in them at random intervals, so I cannot use the IF <13 etc. Instead is there a way to use a symbol or a colored cell?

mind blowing, great educational video and i loved it

I was trying to do a merge between two queries in Power Query using the left outer joint. But the second query is against a column that is a merge column of 5 text columns. So, I did not find a solution in Power Query and I ended using Xlookup. The formula used was =XLOOKUP("*"&E5&"*", code,quantity, "no match", 2), where a lookup-value, as text, can match a specific text contained in the merged column with different texts separated by a space. If there is a way to do it in Power, let me know. Thanks

Great. Thanks a lot! Are there new things to come, Gasper?

Hi Gasper, love your work. I'm a beginner, and I'm trying to follow your steps which you breakdown throughout the series. I'm having difficulty adding a column in bulk (12:31 in the above video). I have copied everything, cell by cell, formatting and it all works to this point. I enter: 1 calendar = ADDCOLUMNS ( CALENDAR("1/1/2017","31/12/2022"), but when I hit return, I receive an error notification starting ! Too few arguments............' what am I doing wrong?

Absolutely brilliant. Thank you Gasper

Could you kindly share practice file used in this tutorial to follow along please. Thanks.

Pleaseshare link to download pracice file to follow along

I've been looking for it for a while till I came across your channel...May Allah bless u.

Great video, I bought a copy of Alan's book.

thks bro. it is one solution I am looking for

Hi I have a question I hope you can help me Thanks in advance: My question is how to automatically number rows in sequence but per classification within the same sheet? Example: White has 10 rows, Blue has 7 rows and Red has 5 rows.

🎉

Brilliant !!!

I have to look you up on Udemy. I want to be in your Classroom. I am 10x better with my excel thinking :). Thank you.

why don't you multiply by 100? in that case the index will itself be "readable". e.g. 2017 Jan will become 201701, for the months will always have the 2 decimapl places, but unlike multiplying with 12, the year number will not change. Similarly, when I want to have the whole date as a "readable number", I use 10000*y+100*m+d.

Thank you for this. I’m not very good with excel and this is going to make me look good at work.

The Year Month Index is a game-changer!!!

Thank you for sharing your knowledge and simple files Thank you very much... because from the video you can follow it step by step easily using example files

Hi, please can you send me the files for this super exercise.Thanks

when you do the calculations for year and month based on the sequence column, yours auto fills, mine does not. why is that?

Hi❤😊

This is great! Could I please get the files used sent to my email. Thank you so much. Great job

Good videos

Look, its 25 questions on the SHL Robert Half test and you have less than 25 minutes to complete. Issues are too many options to solve and u fail. It’s bullshit! I used excel when it was shit!

Thanks! You are on target!

how i can extend to a thousand

Sir can you insert a symbol like beta in the name box?

You will be always remembered because of this control enter. It's such a breeze this control enter. I used to type same date or other text in many cells and copy and paste text from previous cells on cells down, and my co-workers did the same thing, you even hear them doing the same sound control comma plus enter or copy and paste on so many cells down. With control enter, not anymore this issue.

Awesome

Thank you Sir Gasper!!! 🎖🎖 very helpfull. Hope I can put il in practice.

very informative video sir. that would help me a lot in working with excel..... good thing you're not the CEO of the company that i'm aiming for, or there's no way that i'll ever get hired😁

Thank you 🙏

Definitely learned some things. But can you give an example on being able to scroll through sheets in the name box?

Thanks a lot Gaspar! It was interesting to see how to nest an offset func into filter func.

its too bad I cant biuu a copy of office 365. its just too much money for me and I don't like subscriptions. I love sorting with interactive check boxes also. . Have To use google sheets

thank you! I really liked your series on this topic and your style.

awesome nerding! exactly what I needed. Thank you!

Hello, Gasper. Thanks for the explanation. Another case is that I have hundreds of names that are repeated and ordered alphabetically. I'd like to assign a serial number ranging from "01" to "n" to each name, with the following name starting from "01" again, and so on for the remaining names. Is it possible to do this with Excel? I'm doing it manually; please let me know if there's an easier approach.

Ha! I just recently learned about working with lists and I was struggeling with a probl.... euh.... challenge where I want to know whether a hotelguest has his/her/their birthday during their stay. Because not everybody has the same arrival and/or departure date I cannot work with a general datelist for the stay. In earlier solutions I worked with dayofyear, comparing it to dayofyear in the year of birth. But that is not always accurate. When a birthday is the first of march or later in the year, and the year of birth was a leap year, or the current year is a leap year, the day is one day of. In later solutions I worked with a datelist that I expanded to rows for each day of the stay. 60 records could easily become over 400 records! With the list.intersect I was finally able to solve it quite elegant. I think. There is most probably a far simpler solution, but for now I was quite pleased with my code, inspired by this great video! let Source = ExcelCurrentWorkbook(), AgeOnDepartureDayRoundedDown = Table.AddColumn(Source, "AgeRoundedDown", each Number.RoundDown((Number.From([Departure])-Number.From([DateOfBirth]))/365.25), Int64.Type), ListOfDaysPresent = Table.AddColumn(AgeOnDepartureDayRoundedDown , "ListOfDaysPresent ", each List.Dates([Arrival], Duration.Days([Departure]-[Arrival]), #duration(1,0,0,0))), TransformDatesToDD_MMMM = Table.TransformColumns(ListOfDaysPresent , {"ListOfDaysPresent ", each List.Transform(_, each Text.Combine({Date.ToText(_, "%d"), " ", Date.ToText(_, "MMMM")}))}), ListOfBirthdays = Table.AddColumn( TransformDatesToDD_MMMM, "Birthday", each {Text.Combine({Date.ToText([DateOfBirth], "%d"), " ", Date.ToText([DateOfBirth], "MMMM")})}), HasBirthdayDuringStay = Table.AddColumn(ListOfBirthdays , "HasBirthdayDuringStay", each List.Intersect({[Birthday],[ListOfDaysPresent ]})), TransformListToText = Table.TransformColumns(HasBirthdayDuringStay, {"HasBirthdayDuringStay", each Text.Combine(List.Transform(_, Text.From)), type text}), BirthdayNotification = Table.AddColumn(TransformListToText , "BirthdayNotification", each if [HasBirthdayDuringStay] <>"" then "Birthday celebrations at " &[HasBirthdayDuringStay] &". Reaches the tender age of " &(Number.ToText([AgeRoundedDown])) &" years!" else "") in BirthdayNotification Should you have any suggestions for simplifying or otherwise; I'm open to feedback! Thanks again for this inspiring video! Martijn

very instructive video. I saw a you tube tutorial where the speaker used the name box to highlight column but it also showes the number of inches that column. do you know how that was done?

Split by rows! That one's a jewel! Thanks for sharing.

I want to make a sequence LIKE THIS; AAA AAB AAC AAD AAE AAF AAG.......AAX AAY AAZ ABA ABB ABC ABD and so on. How can I do this?

Thank you so much for this amazing tutorial. I learned so much from it it's remarkable! In 40min i upped my data skills 10 notches. It helped consolidate what i knew of arrays into an applicable real world scenario and the fact that you contrasted and compared the more "traditional" or antiquated ways to solve those problems in Excel with the latest and hippest was really useful. My only regret is that you didn't include a link to a test question file and more crucially to the 4 text files that were transformed in power query. I literally just discovered Power query and those text files would've been a great tool for practice. Anyway, fantastic job and i am now

Wow, my problem for the last 10 years turned out to be solved this easily? (using the second method) 😱Thank you very much! 🙏

I can't believe you couldn't come up with a less convoluted and more common use case as an introduction to lists. If you have to take 5 minutes setting up the scenario, you've chosen a bad scenario. Do better.

What if there are blank arrays, item, description, qty, unit price, total price are the columns, description is on multiple arrays within 1 column, i.e a BOQ of construction company

Thank you!

Great content! You make it easy. I'm now a subscriber!

Wow man. Thank you, Thank you, Thank you. I have been cramming excel video tutorials for the past week. Your videos are exactly what I was hoping to find. Thank you, I can tell you really enjoy excel. It makes learning a lot easier. :)