I don't know why I still love old school solution perfect !!!
@excelisfun
2 жыл бұрын
Yes!!!! Old School Rules!!!!!
@darrylmorgan
2 жыл бұрын
Boom!As Im Using 365 I'd Go With The Filter Function But Gotta Say The Advanced Filter Solution Was Pretty Awesome...Thank You Mike :)
@excelisfun
2 жыл бұрын
Yes, FILTER is what I want to use too, but Advanced filter for most people in world who still don't have Excel 365, is that way go!!! For us, at least we have it up our sleeve as a party trick : )
@HachiAdachi
2 жыл бұрын
🤯🤯🤯 It's been a while since I actually exclaimed vocally watching an Excel tutorial... Especially for an "Old School" method! I am humbled and excited by this underutilized (by me) advanced filter feature!! Thank you, Mike!! 👏
@excelisfun
2 жыл бұрын
And, Hachi, the most amazing thing is that it has been there for over 30 years!!!!! That is true OLD S-cool : ) : )
@martyc5674
2 жыл бұрын
Xmatch for the columns is pretty sharp- I like it! Filter is to Excel as Select is to SQL, it’s definitely the function that gives me the most right now!
@excelisfun
2 жыл бұрын
Perfect similie: FILTER can do both SQL SELECT (columns) and WHERE (rows)!!!! : )
@scottgaines2677
2 жыл бұрын
This one blows me away! Thanks Mike! I would choose Advance Filter because it's easier. I need to up my game and get to know filter. Loved all the keyboard shortcuts you used. Very impressive as always! Thanks again!
@excelisfun
2 жыл бұрын
You are welcome again, Scott!!! P.S. Keyboards Rule : )
@erickarias3309
2 жыл бұрын
I love this channel.
@excelisfun
2 жыл бұрын
Glad you love it, Erick!!!!
@elbadlis
2 жыл бұрын
Inserting logical test to advanced filter is mindblown!!! I learned something new today. Thank you, Mike and Bill. Great teammmmmm
@excelisfun
2 жыл бұрын
You are welcome!
@simfinso858
2 жыл бұрын
Old School is Easier. This is life saving video for me. Thanks for posting .
@excelisfun
2 жыл бұрын
Great! How was it life saving? Did you just have this issue come up?
@simfinso858
2 жыл бұрын
@@excelisfun Yes I am planning to make all new series of videos on National Accounts of India. This video will save my Huge time.
@excelisfun
2 жыл бұрын
@@simfinso858 Great : )
@sachinrv1
2 жыл бұрын
This is simply mind blowing Mike. SUPER EXCELLENT :) Cheers :)
@excelisfun
2 жыл бұрын
Yes!!!! Mind Blow in an Excel way is always good : )
@josecarlosconejo5724
2 жыл бұрын
Speechless 😶!! Mind blowing!! Really smart solutions. Will explore that SEARCH function functionality I had not yet thought about. I have the feeling it will help me solve some data complex problems I have at work. Thanks for sharing. Not only do I learn a lot here but I also feel stimulated to further investigate new solutions.
@excelisfun
2 жыл бұрын
We do have fun here!!!!! I am so glad you are so pumped to explore and have even more fun, Jose!!!!!
@johnhackwood1568
2 жыл бұрын
Wow good to see Advanced Filter get some wow factor again! It's such a powerful, reliable and low performance feature. I love the new Filter and Sort formulas for lots of reasons especially for what they have done for cleaner, sorted drop down lists and the auto changing results but don't ditch advanced filter too fast, it has it's advantages a) handles the OR situation brilliantly incl wildcards b) handles changing criteria easily just type into cells and c) simpler to add in VBA. Ok the disadvantage is the results are the results and you can't build formulas from them and they don't update dynamically. That's where these Dynamic Array formulas are just awesome.
@excelisfun
2 жыл бұрын
That is a great summary! Very true : )
@wayneedmondson1065
2 жыл бұрын
Hi Mike. Awesome FILTER fun! That Advanced Filter trick is great, but I prefer the FILTER function for its dynamic behavior. Thanks for the Tuesday multi FILTER workout :)) Thumbs up!!
@excelisfun
2 жыл бұрын
You are welcome for the Tue Multi Filter Fun, Wayne!!!!
@SyedMuzammilMahasanShahi
2 жыл бұрын
Yet another EXCELlent vieo. Thanks Mike.
@excelisfun
2 жыл бұрын
You are welcome, long time teacher friend, Syed MM!!!!!!
@canirmalchoudhary8173
2 жыл бұрын
Truly amazing...opened to look into differently....using SERACH to get row values and XMATCH to get column values are awesome application within FILTER. earlier I used SEQUENCE, ROWS, INDEX to get specific column values but bow XMATCH is really easy-to-use 👌. Thanks Mike
@excelisfun
2 жыл бұрын
You are welcome for the easy to use, CA Nirmal!!!
@canirmalchoudhary8173
2 жыл бұрын
Also learnt one beautiful shortcut Ctrl+ > navigate to corners. I liked formula techniques it's easy to use
@excelisfun
2 жыл бұрын
Yes, that corner jump is just what you need sometimes : )
@chrism9037
2 жыл бұрын
Excellent Mike!!!, thanks for all you do!
@excelisfun
2 жыл бұрын
You are welcome, Most Awesome Teammate Chris : ) : )
@colinburrows88
2 жыл бұрын
Great video (as always). In order to help it sink in I made the data block a table and then adjusted the references. In case it's helpful to anybody else: = FILTER( FILTER( Table1, (Table1[IndicatorName] = AW1) * ISNUMBER( SEARCH(Table1[Country], AW2))), ISNUMBER( XMATCH(Table1[#Headers], AX9:BI9))) Note that creating the table changes the year column headers to text. So you need to either change them or change the headers in AX9:BI9.
@kulavajuma7956
2 жыл бұрын
The keyboard shortcuts that you have applied are awesome. Advance filter is pretty much easier thou you it requires re-run if data has been changed. But the filter formular is a bit comprehensive but it requires some amount of time for practise before you can master it. Thanks Mike 🇹🇿 from Tanzania.
@excelisfun
2 жыл бұрын
What a great summary you just presented : ) Thanks, Kulava!!
@zahoorsarbandi2982
2 жыл бұрын
East or West! Mike is the best!
@excelisfun
2 жыл бұрын
Thank you, Zahoor!!!!
@sjh1956
2 жыл бұрын
This is SOOO cool! I was just fiddlin' around with using LAMBDAs to extract data from a dataset (and actually succeding). Converting your dataset to an Excel Table and defining named ranges made my formulas look 'better'. Then I used the named ranges as variables in LAMBDA functions and in data validation dropdown lists, which made the output dynamic. The 'output' formula looks like this: =INDEX(OutputArray(B2;B3;B4;B5;B6);NumRows(B1);XMATCH(B11:M11;Headers)), where OutputArray is a LAMBDA function that filters the dataset based on the references, and NumRows is a LAMBDA that reads the number of rows in the filtered output. In the XMATCH function the lookup vallue is based on dropdowns in the cells B11:M11, and the lookup array is a named range based on the headers in the dataset. I had hoped that, maybe, I could have sent you a copy of my file, but couldn't find the necessary contact information.
@excelisfun
2 жыл бұрын
Great formula! Good creative invention, Stein!! P.S. you do not need to send the file. I can see the full beauty from your post ; )
@msmith3090
2 жыл бұрын
Awesome video as usual! For me the most exciting ideas are 1) filtering to pull only the columns needed using the target header names instead of array ones and zeros {1,0,1,1,0}; and 2) being able to have all the matching items in one cell with delimiters. Unfortunately, I don't really understand how the Advanced Filter method works. Thanks again!
@excelisfun
2 жыл бұрын
I have videos on Advanced Filter, but if you have Microsoft 365, then the FILTER is fully dynamic and the way to go!
@amardeepsingh5252
2 жыл бұрын
Mike - FILTER formula is something that one could still figure out somehow but how come you thought of using the trick with advanced filter. This is really out of the world. Amazing!! Did you bribe Microsoft to do this 😂😂?
@excelisfun
2 жыл бұрын
No, that the formula Advanced Filter has been in Excel for over 30 years. No bribe necessary lol
@johnborg6005
2 жыл бұрын
Thanks Mike. The filter function rules for me.
@excelisfun
2 жыл бұрын
Yes!!!! Ruling is always fun ; )
@maneshzaveri5894
2 жыл бұрын
Mind blowing , as always
@excelisfun
2 жыл бұрын
Yes!!!!! Mind blown by Excel is always fun : )
@bamakaze
2 жыл бұрын
Boom! Thank you, Mike!
@excelisfun
2 жыл бұрын
Boom! You are welcome : )
@garethwoodall577
2 жыл бұрын
Mike is a genius. #skills
@excelisfun
2 жыл бұрын
Just a guy having fun with Excel lol
@ExcelInstructor
2 жыл бұрын
you always figure out a way to make complicated task easy! Congratz!
@excelisfun
2 жыл бұрын
You are so welcome, YFN Excel!!!
@ExcelInstructor
2 жыл бұрын
@@excelisfun im still gathering up courage... to do excel stuff online, I know that's kinda stupid
@excelisfun
2 жыл бұрын
@@ExcelInstructor not stupid at all. You can do it!
@ExcelInstructor
2 жыл бұрын
@@excelisfun I finaly did it! its processing the HD quality right now... Wish me luck ...
@excelisfun
2 жыл бұрын
@@ExcelInstructor I wish you rad luck!!!!!!
@karolkrol4473
2 жыл бұрын
You are truly amazing
@excelisfun
2 жыл бұрын
Glad you like the videos, Karol!!!
@solex5480
2 жыл бұрын
Excellent. Thanks Mike
@excelisfun
2 жыл бұрын
You are welcome, Solex!!!
@Hazzarddu
10 ай бұрын
Amazing, thank you!
@philipschneider4864
2 жыл бұрын
Hi, thanks for a great video - very interesting and powerful. Can we use a variable "include" argument of the FILTER function? I was hoping to filter a main data table (on one sheet) to one or more other sheets that relate to specific columns in the main data table. I wanted to name the sheets with that column header, extract the sheet name and then use that in the filter function. I tried to create the command, then use the INDEX function, but I don't think that it can handle the array/spill functions. It would be great if there were an INDEX function that could spill...
@alexrosen8762
2 жыл бұрын
Fantastic!
@excelisfun
2 жыл бұрын
Glad you like it, Alex!!!
@jayantgupta3037
2 жыл бұрын
As always thanks
@excelisfun
2 жыл бұрын
You are always welcome, Jayant!!!!
@FRANKWHITE1996
2 жыл бұрын
Thanks for sharing! 👍
@excelisfun
2 жыл бұрын
You are welcome!
@m.raedallulu4166
2 жыл бұрын
I prefer the Mike's genius way!
@excelisfun
2 жыл бұрын
I learned it from Bill Szysz, years ago ; )
@davidmccarthy5760
Жыл бұрын
Great video, really enjoy the content. Would it be possible to add another parameter to exclude any row in your final result that had a GDP under 10,000 in any resulting column?
@kishorpanara4191
2 жыл бұрын
You are great dear
@excelisfun
2 жыл бұрын
Always glad to help, kishor!!!
@BillSzysz1
2 жыл бұрын
How I love these new features:-))) Thanks M(aster)ike ;-)))
@excelisfun
2 жыл бұрын
Me too. L-O-V-E!!!!!!!!!
@PatoSly
Жыл бұрын
Nice Mike. I was wondering if the advanced filter can be used to report on a table that has Columns (dates) and rows (tasks) and then on intersections it has hours spent for each task in each date. Either Dates or tasks can have more than 1 intersected value. The Report needs to show a row for each Date (Date, task, Hours) I have a sample Data but can't paste it here....
@GeertDelmulle
2 жыл бұрын
With the FILTER function no need to “re-run the Advanced Filter” or anything else: it updates instantly. I prefer the New School solution, even though it requires some more elaborate logic (but not that much).
@GeertDelmulle
2 жыл бұрын
BTW: only last week did I use FILTER-formulas like these! :-) I participated in a strategic programme meeting with the board of directors where I manned a fully integrated, multi-sheet Excel workbook (of my re-cration) that allowed for fully dynamic “What If?”-scenarios that were run on the fly in the meeting, where we optimised a so-called ‘waterline diagram’ for a multi-year investment programme. Many consecutive scenarios were applied and everything updated in real time like a charm. It was a very high profile, fast lane meeting and everything went swimmingly. Some serious optimisation was performed and a consensus was reached. Received quite a few compliments afterwards. My Excel-skills have become undeniable across the board (if they weren’t already) and the new calculation engine has supercharged them only more! Love it! :-)
@GeertDelmulle
2 жыл бұрын
Not too many people in my organisation know about Dynamic Arrays: only the ones who come to me for Excel Advice…
@excelisfun
2 жыл бұрын
@@GeertDelmulle You are soooooooooo good with Excel, Teammate: everything updated in real time like a charm, swimmingly so to achieve consensus and cause the board to feel superchared optimal happiness!!!!! Undeniable Geert radness!!!!
@GeertDelmulle
2 жыл бұрын
@@excelisfun Thanks Mike. Admittedly, it did cost me working through the weekend and late evenings during the week. But everything had to be checked, all formulas reworked (some errors corrected), all this while the workbook was used by multiple people to provide the necessary inputs (SharePoint). I even added some logic to pinpoint inconsistencies in the inputs, so that people would correct those (using the usual tricks: formulas and conditional formatting). For us (you and me) not so hard to do, but the workbook is full of DA-calculations, double FILTER-formulas, LET-functions, running totals by means of DA-calculations (our work computers don’t have the newest insider functions), etc. So, I had to be careful about which functions to use and which not. I even discovered that one of the directors had to update his MS Office suite, because he didn’t have the LET-function. I even taught someone how to do “copy - Paste Values” (right-click, V). All the other stuff I could not teach - there was no time. Oh, and I protected most of the cells so that people would not mess up good formulas, etc. A decision that proved to be crucial in this multi-user exercise. Having Excel as a hobby really paid big dividends this time! It was great fun to do for me - like I said to several people at work: I had a blast! :-)
@excelisfun
2 жыл бұрын
@@GeertDelmulle Like a charm. I love that!!!!
@alegomso
2 жыл бұрын
Hey Mike, thanks for the awesome content. I was wondering if there's a way in which you could use this filter function and then order the columns in a different order than the original table. I used the XMATCH to get the columns that I wanted, however if I scrambled the order of the field names in a different order than my original table, the XMATCH wouldn't display the data in the correct field names. (It will display the data in the same order of columns as the main table).
@msmith3090
2 жыл бұрын
Hello Alejandro, Ran into a solution for this. You can use the Choose function inside Filter as demonstrated by Mynda Treacy at this KZitem link: kzitem.info/news/bejne/u3mHp6ubqHOVgqw It's in the section about non-contiguous columns at the 7:57 mark. Hope this helps. M Smith
@ArturMarinSabates
2 жыл бұрын
@@msmith3090 The use of Choose in the array section to select the columns before filtering is very interesting. In fact rather than creating choose the array {1,2,3,...} you can use sequence which even allows to count the cells in the column header. However the manual selection of each column on the other hand makes the formula very long as soon as you have +3 columns. What I suggest instead is to do an index of the table selecting all rows and the columns in the preferred order. =FILTER( INDEX(Table,SEQUENCE(ROWS(Table)),XMATCH(myreportcolumns, Table[#Headers]), condition to filter rows)
@obentophaut8693
2 жыл бұрын
Good stuff.
@excelisfun
2 жыл бұрын
Glad you like it!
@mohamedchakroun4973
2 жыл бұрын
I love the old school here, lot of khnowledge :-)
@excelisfun
2 жыл бұрын
Glad you like it!
@shubhampawar8506
2 жыл бұрын
Amazing trick ❤️👌
@excelisfun
2 жыл бұрын
Glad it amazing for you, Excel Master shubham ; )
@shubhampawar8506
2 жыл бұрын
@@excelisfun I'm not master yet ..there is n number of things which I need to know...you are the one who helps me in the journey of mastering excel ...thanks a lot ❤️🤟
@excelisfun
2 жыл бұрын
@@shubhampawar8506 You are becoming an Excel Master because of your hard work and deduction : )
@pcodello
2 жыл бұрын
Hi Mike, excellent video and advanced filter is the way to go. Please when you have time can you do a video for Power Automation showing how to refresh an Excel file with Power Query and the sources files (TXT, CSV, ETC.) hosted on OneDrive. Thank you for all your help.
@excelisfun
2 жыл бұрын
I will have to try this myself. I have never tried to host csv files at onedrive and then connect with Power Query. Is Power Automation a specific feature? I am not aware of it.
@pcodello
2 жыл бұрын
@@excelisfun Mike sorry I was trying to say Power Automate. I am trying to automate the refresh process of a few Excel files with Power Query that are using csv and other excel sources available on OneDrive. Thank you.
@excelisfun
2 жыл бұрын
@@pcodello I made a video, but it is not about Power Automate. I do not know how to use Power Automate yet. I just took a look at it, but it was unclear to me. The video will show how to connect to folder with csv files at OneDrive.
@pcodello
2 жыл бұрын
@@excelisfun Mike the idea is to set up daily auto refresh to an excel file from a couple of files from different folders in OneDrive. I saw a video from Leila Gharani but I was hoping to see one from you, because you always drill down a little bit more on the details. Thank you.
@gordonyuntaohuang58
2 жыл бұрын
This is mind blowing Mike! Thanks for the tip. First time saw the use of XMATCH as column selector wrapped in a 2nd FILTER. I just played with your example workbook a bit and found that it seems you cannot choose randomly which column (Year) you wanted? Seems once you chose the first year, say 2000, the subsequent years have to be 2001, 2002, 2003, etc. i.e. in ascending order incremented by 1. You cannot choose say 2003, 1999, 2000? Any advice on this Mike? Thanks heaps!
@excelisfun
2 жыл бұрын
Did you convert to an Excel Table? If so then the years become text and that causes problems. You might have to add zero to field name text years to get them back to years.
@gordonyuntaohuang58
2 жыл бұрын
@@excelisfun Nah, I did not Mike. In your example workbook, if you change the years in Cell AY9 - BC9 to 2001, 2005, 2002, 2006, 2003 (originally was 2000, 2001, 2002, 2003, 2004 in your example workbook) the numbers returned for Argentina (I just picked one country here for simplicity) are not correct anymore?!
@tillacat1
Жыл бұрын
Can you explain how to do a formula to filter by a specific row criteria and also by column? I am trying to filter by criteria and also date set up like an exercise grid.
@excel_along_the_way
2 жыл бұрын
Hi Mike, thank you for a awesome tutorial. I like to play around and see if there are other ways as well. Not that I come up with better solutions. I would like to know what solution calculate faster between your solution and =INDEX(B10:AU3694,FILTER(SEQUENCE(ROWS(B10:AU3694)),(C10:C3694=AW1)*ISNUMBER(SEARCH(B10:B3694,AW2))),XMATCH(AX9:BI9,B9:AU9)). Yours is easier.
@excelisfun
2 жыл бұрын
Thanks for the cool formula. I am not at my machine where I can time it till next week. I will also try to add your formula to download workbook.
@excelisfun
2 жыл бұрын
I just remoted into my other machine and timed. There was no difference. 57 mil sec average for each. I also added your clever formula to the download workbook. Go Team!!!
@excel_along_the_way
2 жыл бұрын
@@excelisfun Thank you Mike for responding. Have a awesome day.
@vinayakpotadar4093
2 жыл бұрын
Super sir
@excelisfun
2 жыл бұрын
Glad you like it, vinayak!!!!
@excelmonkey
Жыл бұрын
I have been trying to integrate this into data that I am averaging. The data includes zero values which I wan to further filter out. Its unclear how to adjust this to either include another filter to take out the zero values OR wrap an averageif around the the forumula. To be clear wrapping an average around it does not take out the zero values and its unclear how to use an averageif given the mix of column and row filtering. Any ideas?
@Karthickchidambaram
2 жыл бұрын
Superb sir, I would like to know what is your most favourite function in excel other than dynamic array functions
@excelisfun
2 жыл бұрын
I have not favorites lol. I like them all : ) : ) : ) : )
@Karthickchidambaram
2 жыл бұрын
@@excelisfun 😁
@khansvirtualdiary
2 жыл бұрын
filtering in excel made easy
@excelisfun
2 жыл бұрын
Yes, indeed : )
@brunosegers5569
2 жыл бұрын
Hi Mike, I don't know if this is the right way to ask a question, but I'm going to try anyway. Is there a way to display the page number from - to in a cell. I use the first rows as headers on every page and our ISO standard doesn't allow doing this in the footer (where it's very simple to do it). I have VBA code that can count the pages and know which page we are on. But this code doesn't work in the header story. Mike, would you mind helping me further?
@excelisfun
2 жыл бұрын
I am pretty sure that is easy with the built in Page Seatup. Here is a video: kzitem.info/news/bejne/xJWQsICAhGeBnYI Check in show more area below video for tip #26: 26. (47:52) Page Setup becasue the the page setup in at the 47:52 minute mark. Post back and let me know if that does the trick.
@HusseinKorish
2 жыл бұрын
Amazing Mike ... i have a question on Xmatch function (minute 4:50) ... , I thought you'll choose the "AX9:BI9" as lookup value for xmatch not "B9:AU9" ...but the formula worked fine .... am i missing something ?
@excelisfun
2 жыл бұрын
You have to use the range that is the same size as the table being filtered in the lookup_value argument because then this function argument array operation delivers a results that is the same size as the table rows. Otherwise, it will not work.
@excelisfun
2 жыл бұрын
You got the formula to work the other way!?!?! Really?
@HusseinKorish
2 жыл бұрын
@@excelisfun NO ...of course it didn't work with me ... i meant that the formula worked fine the way you done it ... but i got confused for a while ...what exactly is the lookup_array here for this xmatch !!!
@HusseinKorish
2 жыл бұрын
Oooh ... i see ... its because you used another filter ... wow thanks mike ... it's easy now .... i got it
@markpodesta4605
2 жыл бұрын
FILTER is magic!
@excelisfun
2 жыл бұрын
FILTER is just like magic : ) : )
@jacek.kalinski
2 жыл бұрын
Super
@excelisfun
2 жыл бұрын
Glad it is super for you, Jacek!!!
@khonesavanhsoudaloth6342
9 ай бұрын
I’m wanna buy your excel textbooks but there is no on sale in Laos. How could I possibly be able to buy your excel textbook?
@KO1967
2 жыл бұрын
Hi Mike. I found a problem with this approach. Not in that it does not work but in that it doesn't necessarily return what is expected. The outer filter (on columns) will return the columns referenced in AX9:BI9 but not in the order specified in in AX9:BI9. For example, reverse the order of the years to list them from 2010 to 2000 in descending order (i.e. AY9=2010, AZ9=2009) and the data will still be populated according to the order of the original data set (i.e. 2000 then 2001). This would create an issue if the fields are not in the exact same order as the data would not match the header. Wondering if anyone has figured out a way to address this?
@KO1967
2 жыл бұрын
Follow Up: Here is the formula to use to have the data match the headers above. Not nearly as clean but in the end I think this is better and less chance of mistaking the data for matching the headers above. =LET(vFltr,FILTER(B10:AU3694,(C10:C3694=AW1)*ISNUMBER(SEARCH(B10:B3694,AW2))), INDEX(vFltr,SEQUENCE(ROWS(vFltr)),XMATCH(AX9:BI9,B9:AU9)))
@djl8710
2 жыл бұрын
Spooky ghost formulas! 👻👻👻👻👻👻👻
@excelisfun
2 жыл бұрын
Yes!!!!! The Excel Ghost makes our formula life easy : )
@tecwzrd
2 жыл бұрын
That adv filter formula seems way too easy. Only downside is that it doesn't update in real time but it's not that hard to remember to re-run it.
@excelisfun
2 жыл бұрын
Well said. Perfect analysis of two methods. The Advanced Filter is just such a cool tool, with the ability to specify just come columns ; )
@uhelalify
2 жыл бұрын
Hi Mike, in original data set when filter column C by GDP it shows 220 countries and four countries don't have GDP from 2000 - 2010 that means 216 countries have GDP from 2000-2021. But when you work out with formula it shows only 29 countries have GDP. Why other countries not showing up? Just for curiosity. Cheers
@excelisfun
2 жыл бұрын
I do not understand your question. But there are definitely countries that have no GDP data in the start data set...
@simfinso858
2 жыл бұрын
Another Question is :-Do You have video on How to Make "Chess sheet " in Excel??? 1 Black & 1 White cell 0 for black & 1 for conditional formatting?
@excelisfun
2 жыл бұрын
I never thought of that before... 10 minutes later: Here is the formula: =MOD(ROWS(A$1:A1)+COLUMNS($A1:A1),2) I'll make a video next week for you : )
@simfinso858
2 жыл бұрын
@@excelisfun Wow I am waiting.
@Tommie_Rogers
2 жыл бұрын
Hi Mike, I have a question, not related to the video, about a situation I am in. In South Africa we have personal ID Numbers where the first six digits indicate your date of birth (881025) will mean that you were born on 25 Oct 1988. There are a number of ways I can extract the birth year from this number. The problem is if someone was born in 2000 or after your ID number will start with 001025 for instance if you were born in 2000 or 201025 if you were born in 2020. Excel does not see these dates as being in the 2000 to 2021 range but as dates in the 1900's. Is there a way I can get Excel to give me the right year?
@excelisfun
2 жыл бұрын
Yes, but what is the hurdle? What is the cut off year that decides what goes in 2000 and what goes in 1900? 1930? 1950? or other?
@excelisfun
2 жыл бұрын
I guess if the first 2 digits are year and 0-29 = 2000 and 30-99 = 2000, then this might get year: =IF(LEFT(I15,2)+0
@excelisfun
2 жыл бұрын
Maybe for the whole date formula: =DATE(IF(LEFT(I15,2)+0
@Tommie_Rogers
2 жыл бұрын
@@excelisfun Hi Mike, the cut off is people entering the work place . Once you turn 18 you are legally allowed to seek employment. So if you were born in 2003 your employer would use your ID Number to see what year you were born and if you are old enough to work. So if you were born between January 2000 and September 2000 your ID will start with 0001?? and 0009??, October to December will start with 0010??, 0011?? or 0012??. 2001 would be 0101?? to 0109?? and 0110??, 0111?? and 0112??, the same with all dates up to 2009 (only 12 years old this year, so too young to work). The problem is that you cannot use the LEFT Function as Excel ignores all the zero's at the start of the number - the ID 001015 (born 2000-October-15th) will get the year of birth as 1911, but another date will be completely off the mark. You can start the number with an apostrophe and change it to a text entry, but this also fails at times.
@excelisfun
2 жыл бұрын
@@Tommie_Rogers I guess you could use the hurdle date as: EDATE(TODAY(),-12*18), that would always jump back 18 years . LEFT on Text ID, like 000205, would pull 00, so I guess you have used custom number formatting!?!?! for the ID? So I guess I am unclear.
@mr.brownstone5716
2 жыл бұрын
Both those filter functions seem pretty damn hard. Lol.
@excelisfun
2 жыл бұрын
Yes, this is just a flat out hard data extract task. But imagine having to do it by hand. Which a lot of people do : ( Lol.
@carlosmantilla7997
2 жыл бұрын
👍🏻
@excelisfun
2 жыл бұрын
Thanks, Carlos : )
@rrrraaaacccc80
2 жыл бұрын
👍
@excelisfun
2 жыл бұрын
: ) : )
@ALPHERATZ3650
2 жыл бұрын
AAAAAMMMMMAAAAAZZZZZIIIIIINNNNNNGGGGGG!!!!!
@AnandGautam9901
2 жыл бұрын
Older Solution is Better
@meniporat3527
2 жыл бұрын
Excel-lent. My comment has nothing to do with the contents of the post (which is astounding). I'd rather address the problem of pronouncing the contributor's name. Bill Szysz is Polish. In Polish "sz" is pronounced exactly like "sh" in English. So "Szysz" should be pronounced "shish", as in: shish kebab. Thank you.
@excelisfun
2 жыл бұрын
Thank you, Meni!!!! Shish : ) Bill Szysz has never corrected me in all the years he and I have been friends, so I never changed it. It might be that the way I pronounce it makes him sound more like the true super hero that he is lol
@meniporat3527
2 жыл бұрын
@@excelisfun My guess is that: Either he has never heard you pronounce his name, or he's been too shy to correct you.
@excelisfun
2 жыл бұрын
@@meniporat3527 He has heard me said it 100s of times. I will have to ask his, now : )
Пікірлер: 163