MAY REAL MIS -3

Key Takeaways

[Applause] hi friends this is Excel Guru am my Guru actually renamed by am Guru and here I'm going to explain you actually this query asked in group by someone whenever start and end date is there so we have to extract only those names right only those names we have to extract if I change any name over here like 01 05 sorry 06 0 6 2 1 0 then it will extract 1 2 7 9 here 9 is there see okay June 9th to if I change to here 15 6 do 2 1 0 it has to extract those this see okay now I'll show you how to do first of all what I'll do here first of all I'll take this date equal to this date F4 plus row of C 5 colon C5 so what will happen it will it will increment every one okay plus one so we want from first not from second so what I'll do here I use minus one here control enters so it will extract from one but here is the logic see wherever you extract it will go on there sorry so we have to stop at end of this date that is 15th right so what you'll do what I'll do now C I'll copy this whole formula control C and what I'll do means if no no r no if this date F4 is less than or equal to this date then what will happen if less than equal to this date then it should repeat as it is else is false value false then blank it's not an array function it's a normal function control enter sorry I use less than actually I have to use greater than sorry control enters how many are object will take contr d c we show you only one first to 15th only if I start uh uh like here I'll do like 25 0620 1 0 then it has to show us still 25 after 25 it has to stop now what I'll do I will extract little more this contr D and now I use the February month let's check it out the February month because it's having only 29 now see if I type is equal to I do one thing Evo mon of of this comma zero only 28 are there till 28 it will show after 28 it has to stop see you can take it like this or you can use any custom dat that whatever the date you need you can use over here like 25 0220 or else if you want I said you can use Evo month end of the date also you can use that is e date okay like that also you can use and not only that the date function also you can use here okay whatever it is you can use over there it will work and more doubts in this or if you are not understanding then let me know I will explain you the procedures now coming to the mis1 sorry see what I said find the duplicate ID with v lookup and your formula in colored cell only with colored cell only without helper without helper you have to find the duplicate what is the duplicate is there by using it's very easy see count if comma is great it will show you means 2 two means here the first one is two and second one is two it is counting two times that's why it is sh two two and remaining are all one one one one one one one they are not counting the position they not showing the position it is counting this is repeating two times here also the same thing understood so what I'll do by using we look up only okay greater than one because we interested in greater than one whever it find the true it will pick up as a where find the true it will pick up as a this F9 understood only those things it will be true and remaining are all false now choose early bracket 1 comma 2 comma and come to the end and value two will be this one close parenthesis it is a CU F9 so we are interested in two this two automatically it will pick up a b c okay we look up 2 comma come to end comma 2 comma Z control shift and enter right and coming to this second [Music] question it's very easy I don't know no one is tried this one what I said how many are HR who joined in April only that thing I said HR who joined at on April okay how many that see it's very easy I don't know why okay is equal to take as a month of this I will remove the format equal to month of this equal to this but it will not work see not it will all falses but we need true so what I'll do I'll convert this this to as a month F so it will pick up true and true but we need HR also the same procedure into equal to this one F4 so it will show you one two because I used mathematical operators so I'm not using any double negative to convert true as falses because double here as is there so it's a mathematical operator so automatically it will convert ones and twos F9 but it's not converting so somewhere we went wrong here totally like this F9 only one is showing not two why only showing one here two are there HR HR okay only one is there some product I will show you some product and close parthis control enter only when it is showing I'll convert this into ail once again let me check whether it's working yes it's working okay if you have any doubts ask me I will ready to explain you guys here also we look up okay see how I lo see here so maximum improvement from previous so not previous from morning to afternoon and afternoon to evening so two sessions are there actually it's not a um amount it is a call duration it's it is a call duration it's not an amount right a call duration or a calls whatever it may be I'll convert into calls it's not a call sorry it's not a amount of currency it's a c so I want to know who is improved from morning to evening that is from morning session to evening session that is 11: to 2: p.m. and 2: p.m. to 6: p.m. so who the sales person not sales person actually some agents so we have to find that one what I'll do here first of all I'll use one function that is Max minus not minus here minus the same thing I'll use here F9 it is showing 34 but we need the person name here not 34 right we need the person name so what I'll do here we look up of I'm not using Qs comma able sorry not table array I'll take only numbers control C control V comma here it is showing the maximum so automatically pick up the 34 okay here it will pick up the 34 so I have to take Cho right I have to take choose true comma Max of this sorry one second chose of 1A 2 comma X of this equal to control V we can do so many ways are there but I'm showing this way okay so close the choose now comma what happened okay I have not used another curly bracket over here okay comma 2 comma 0 what happened here I think some went wrong okay I not choose the second one okay only one I comma the second one is name raakesh is the person who improved maximum calls from afternoon sorry from morning to evening session okay coming to the fourth query and if you have any doubt again and again I'm asking it's very easy guys why I don't know no one right just they are making me they are making us confused that's it nothing is there observe clearly nothing is there simply use okay we look up MCH anything you can okay first of all I'll remove the format alt e f alt HBA simple thing why you are wasting I'm unable to understand that one match of this Comm this F4 comma Z actually they are they want us to check index of this enough that's it no nothing is there why you are I don't know why you are wasting the time yeah now it's very fifth one okay here I will do what I'll do first of all is equal to R between from one to I will take how many pie are there okay count if so it will pick up only that many times comma P F9 F9 okay so that many times it will randomly increase F9 C6 F9 3 like this okay only but only P Counts from that to 1 2 3 4 5 so between one to five it will randomly changes not five is there six totally 1 2 3 4 5 six so what I'll do here I'll use the index function to pick up the employee name F4 comma close parenthesis control enter once done make it a it's a r between automatically it will change randomly F9 F9 F9 f99 so convert this into normal because once they have taken the activity mean they are not supposed to change other activities suppose the Newton has taken seven but it should not change other activities from seven to another six or five for that reason what you have to do you have to convert this into normal function now it's a static one so it will not change at all so I converted this into values understood and if you have more doubts let me know in the comment box or in my telegram group thank you guys thanks for your ram [Applause] ram please like share and subscribe for more amay interview queries click on Bell icon so that you will get a notification thank you for your support ram ram explain you first thing you have to make a descending order as per this this is the condition if I change this one it has to take only B numbers and it should be descending order but not the ascending order it's very easy okay left of this F4 equal to B okay this is the first condition okay let let close the bracket here also right right I'm not giving this one how many number of characters because by default by default it will pick up one character control enter okay this is the first so what I'll do here I use a function called large if this is equal to B then take right of this whole thing comma 2 okay see it is a text first to think it is a text I'm using text function so it is showing text numbers okay so I convert this into a numbers because large will understand only numbers but not the text comma and what will be the K I will give you row of d dollar 3 colon [Music] D3 control shift and enters D and now use if I change a it will be pick up this a descending you want if you want if you want alphabets also then easily you can add like this [Music] then it will work okay okay sorry not control why it's not working control D I have to copy then it has to work why it's not working control D what it this they not working I don't know why Okay pink okay A Rose one okay white has to work actually why it's not working control shift enter control D yes I press enter only that is not working I press now array so control shift and enter shift enter Then only it will work now coming to want one question sir if want in ascending order it will automatically then what we if question is uh in ascending order then ascending you have to use small okay large is okay in in of large small it will work small okay that's it nothing is there will work okay now coming to this second one here it's very easy I don't know no one has tried this one it's damn easy damn damn easy B see if the call dur if the call duration has given like this then you have to pick up the uaer right simply use V look up we look up of this okay of all alt EF is equal to we look up of this comma F4 comma to close that that's it enter control wait a minute control by sh time I have taken two okay three if I change any time between this like 12 10 see it is between this it has pick up this one I did approximate it's very easy yeah coming to the third one yeah yes now here is the logical and magics how many first of all let me know how many understood this one so that I can able to show you the method this I know yes we look up but we look will not we Lo will not pick up that one if I see F4 comma 2 comma 0 if I use star over here what will happen drop down list stars is it five star if I press equal then uh equal equal is working properly right if I press plus is also working but one thing you have to add a wild code for this that is is this one whenever you see any special characters then you have to add wild card that is till why you only only for special characters okay it is understanding it is not understanding It's A Plus or as as what is the meaning of asri normal word what is the meaning of as as no no no in as Exel as means number of characters okay okay it is thinking that number of characters for that reason you have to add K means exactly the ASC it is not mean to for number of characters I need exact whatever it is in the value or a condition understood that is the difference between if I change to start is it okay start ask I any brackets then okay till useful for these type of queries this one is also very easy I have I have explained you in so many questions it's very easy first of all take index of whole numbers what are the numbers are there F4 and I want each and I want the row of B that is only 0 2 0 1us 5 okay so I will use a Mach function to pick up only that type of uh values and index will expect when I'm taking whole table index will expect only row and column but I have given only the row but not the column for row you have to skip just like this if I if I will not do like it it will give us the error if I add a Comm means I'm skipping mean I need each and every number from a row as per this condition for that reason I have to use comma now it will show you 0 2 0 1 - 5 now what I said find the last positive number I'll copy this one look up 9.9 e + 307 sorry 307 or any any biggest number 1 / by is greater than Z comma pick up that number it is an array function but look up will understand array but no need of shi and enter enters b means positive number B yes positive number this one I to B2 d it will pick up three understood and here see you done a very big big big no need of big only two conditions that is what here only only two conditions first I'll use and function here here you can use it for 255 logicals maximum 5 not more than 255 you can use logicals okay first thing what I'll do as you said count if countif of this F4 comma this F4 if it is duplicate then you have to use greater than one comma and here I will use this one F4 equal to duplicate if both conditions true I'll copy this one contr C enter now what I'll do I highlight this one alt H Ln okay the window will not show you control V I'll format it and I will show you that window will with duplicate now so it should be red red and font will be the white okay okay wait a minute here I will show you where it is wait a minute why is not working here I did something wrong here something went wrong duplicate duplicate correct only I copied that one why it's not working here let me check okay I have to use only single one not double one sorry yes yes yes control enter I I have used the whole thing no need of thing control HL R to edit that control V okay apply not applying again I did something wrong here where I went wrong here the procedure is correct only here I will show you I taken the screenshot of that wait a minute where I went take screenshot where it [Music] is this one is a screenshot okay so I don't know where I went wrong here I'll drag this yes everything is correct only I don't know why it went wrong I'm un able to understand that one spelling is correct d u p l a c a t e even spelling will also make it duplicate means it should be greater than one only yes sir and why it is not working it is I loged everything let me highlight once again control C alt hlr this one greater than one yes correct only I think spelling is wrong I think so maybe spelling is wrong I will try F2 same like we yes it should be lock duplicate double quot also there count if yes counting b50 okay it's nothing is there what it is UN bro just you can check the formula in of form mention as unique no greater than one means listen greater than one means it should be a duplicate duplicate one single is means single means noted unique value no I'll keep equal and I'll use here okay as as he said unique okay I'll copy this again contr C Escape ENT Al H nothing is there just uh I'll copy this control V uni now right okay let him check whether it's working or not no it's not working see it's not working see I drag this formula again it's sh all Fales let me change unic yes is working unic is working but what about duplicate then when I try now I'll do duplicate I'll do let me check have to Unique I'll type duplicate duplicate and here I'll use greater okay duplicate is it right contrl C Escape new rule because I'm not disturbing that one I'm adding new one maybe red so I take green okay and uh on side will be white on side will be white okay okay apply okay now it's working it's we need to apply differently or we can combine list what what mistake I did means when I'm using that one it should not be there like this it should not be there should be there directly applied for the is not understanding L I change to unique and try the duplicate then it is working mean whatever it is there it should not what you'll say suppose see listen here uni is there when I when I duplicate when I duplicate here it will work yes sir L I have to change these both things and S asked one thing that is what is static and [Music] dynamic static and [Applause] dynamic see sh one thing static means it never change it never change like 24 hours will it change per day 24 hours it will change those are f those are 60 60 0 seconds per minute will it change 365 days in a year or 12 months in a year will it change so these are so these are the Statics so we can use like that like a mod of some what you'll say I'll give some time here like uh okay time is there so I'll use mode of this comma 0 into 24 here 24 is a static because here 24 will not change understood what I mean to say okay 24 will not change that is static it will be it is not a dynamic it's a St 24 will not change whenever you use the formula like 60 seconds 60 Minutes 24 hours or 365 days like that it's a dynamic sorry it's a static dynamic means like this the value which I have given like this Dynamic way already I have given this if I use this here like is equal to B it's a static it will change it will work I'm not telling it will not work it will work but when I change here like to a it will not work again it's working very good it working I don't know why it's working already hardcoded this one oh it is not working because I added a here I remove that one it will not work because for yeah only it will show V see when I'm changing it it is not changing okay the values are not changing here mean it's a static if you want to make it Dynamic just change this to then it will be a dynamic whenever you change condition whenever you change the condition from here it will change the data also understood San the difference between static and dynamic hard coding the values in a [Music] formula okay only that that is the difference in second question in which we use we look up how yeah answer is coming because uh due to network isue I am unable to understand that one see see for special character for special characters you have to use wild card that is no no this one I understand this one I understand uh last one last one from this one we look up no no no wait a minute Second One Second One Second One Second One and yeah this one it's very easy I Ed only approximate in V cup okay approximate okay yeah that's that's I'm wondering because no value is matching there then how it is so simple okay okay okay okay then I I will leave now thank you thank you guys thank you so much [Applause] sir this is one of our query ask by Anand in our group out of 25 queries this is one of that query right so uh raak what is your idea to get the grades as per name and subject what is your idea uh in this but uh I think but both are in I think if data in the same row same like name subject then it's easy to do what uh in this case I [Music] think no sir no idea okay okay okay I will show you it's very simple now that's not easy very very very simple okay first of all I'll copy this data and I'll paste here and I delete this column this this thing okay now first of all here there are two conditions one is name and another is subject yes okay both should met equally okay and wantedly they are confusing they are confusing Us by taking the RO number this two is enough for us there no just they need they confusing Us by giving rule number okay so what I'll do here I will select this one and I will freeze Only The Columns but not the rules equal to F4 and another condition is another condition is subject I'm locking the column here sorry row here equal to this subject when both will met it will show you one true into true 1 F9 okay if both will met it will show you one now what I'll do I need that one okay so I I use choose function Open Bracket open FL bracket 1 comma 2 because another one is another one is there that is grade picking the grid F4 okay now here by mistake I did here wait a minute value one okay comma value two this one it's very e F4 close now I will use V lookup here so what what will be the lookup value one yes correct because here it is there value showing one if I highlight the both things F9 C all of Z only one is there your any Junior will get two one comma and here simply take comma 2 comma 0 control shift ENT control R control d that's it so simple okay instead of use but we can use choose is must there there because creating the table can you repeat one again choose is must in this case your or we can use any other no no no no no we can use another one so many ways are there we can use match also we can use match also okay okay uh see how how I do match I'll show you I'm copying again copying here control C control V so here I will remove match Okay match of this one thing amp and this both uh so I log this one row here row the second part and first part is column and here don't take directly the table you have to pick up differently F4 Amper sand F4 comma 0 then also it will pick up the positions if I okay control shift and enter control R control d okay now we will use index no no how it came 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 okay okay okay let's let's use index here have to index let's see will work or not first of all because all one 12 3 4 5 6 is there for that reason I'm unable to understand that one index of control shift enters it has to work let me check yes working perfectly uh randomly we will check V SST so it should come d v SST yes here it is yes it is working perfectly okay yes sir yes sir maybe I like to tell you that sir [Applause] in the column okay is counting 1 4 okay see 1 2 3 4 5 6 7 5 6 7 so we we want as 1 2 3 yes sir okay so why we need 1 2 3 I will show you so what we have to do means look up okay here come to end what I'll do we getting 147 right 1A 4A 7 okay understood what getting 147 so we are telling we are getting 147 this one see we are getting 147 okay okay skip so we need 1 2 3 but not 147 so by using look up B will replace uh 47 like yes yes yes how how because look up will what it will do I will show you f look up will pick up the approximate match not the exact matcha 147 comma 1 comma 2 comma 3 we need this one so what we look what we look up sorry what look up will do I'll press enter first of all what look up will do lookup value F9 it's a one so it will pick up one and the result Vector will be one one okay yes sir now what will happen it will pick up the approximate F9 sir four so it will pick up this one what is two because check the four and it will go to two Okay but you here three look value three seven so pick up the seventh position that is three my point yes sir uh one question sir uh if we change the position 1 2 3 2 3 2 1 it will automatically replace in that criteria inse one last one last one is will come okay understand yes sir you got 7th me if take 3 to one it will pick up the seventh position as a one yes sir H now simply use the choose function choose of comma first table this one F4 and the second table is this one F4 comma and third table is this one I will explain why I'm taking the Cho then you'll able to understand F4 I close it I I will explain the choose function okay here I will explain the choose function are you there guys yes sir okay done thank you see what will index will pick up it will pick up the one F9 so automatically it will pick up this table if I take two it will pick up the second one second table not only that example I will show you here somewhere I will show you example what choose will do actually choose of one comma here I will take raak comma 7 okay so it will pick up the ret okay if I press two here it will pick up the second name that is sh this is the choose actually okay whatever the index is there it will pick up the corresponding value whatever it may be if I take 10 it will not pick up because here it is having only two names no 10 yes sir what when data is as for given 1 2 3 4 5 data must be like that exactly exactly that is the choose we do so that one I I implemented here that here see that look up okay for 1 2 three so it will pick up automatically pick up the first one it is one so it will pick up the first one when I come to down I have not dragged the formula if I come to here it will pick up the three second three not second because it's three one third one you pick automatically you pick up the third one okay three tables are there for that reason I have used this one more than you can use it but it takes some time so here I use here I use lookup of product one comma this is as per truth it will pick up the table comma 2 comma 0 control any doubts no doubt sir one question one question sir yes go ahead uh uh there is no other uh function instead of choose choose make the table now for us yes choose always make table for us yes not only table it will pick up the corresponding values not only the table it will pick up the range functions also see functions also will it will pick up see instead of this we can we can use here the V look function here is a sum function a function whenever we change the number it will okay multiple if okay it will directly use that function at that position three is there okay okay understand sir I'll show you choose first of all I'll take one okay not one we'll do one thing we'll take a reference of this okay comma we we look up okay we have to create a table for this okay we look up of okay there s a r v n one second s a r VN comma whole table f4a 2A 0 and another also I will use here another also if it is here G2 one pick up one because value is not there so it is sh one okay okay sir okay another one some of this okay enter it will not do anything if I press one it will do see if I do two it will pick up the some of those things okay got my point you can use functions functions values ranges tables all those things you can use in choose whatever the in different in different if you want to get from different sheets the value then also you can use choose okay sir and uh again go ahead uh I I am asking that uh uh is there another for example in Google sheet choose is not available so I use the C to make the table yes right okay right uh uh in this case is there any other option of choose in normal yeah no normal Excel okay so see one thing we can use indirect indirect okay IND also yeah we can name the range see how how yes sir I I I'll I will create a name for this that is Hyderabad okay Hyderabad okay and here also I use warangal yeah same as per the list required in which we are searching yes warang okay now directly I will use here not here here it is okay is equal to V look up and product one indirect here you have to use indirect and name the range what will be the range Hyderabad Hy the C4 can we give the also or not which one uh uh4 C4 C4 okay yes tell me C4 is yes yes you can use it like this also yeah because we we are giving the same data R name yes it is showing it is showing why it is showing the error F9 difference errors what is the reason check the spellings if it is p not accept not accept suppose it may be having spaces Extra Spaces and it may be having Extra Spaces or it may be whatever I taken the table it may also having the extra spaces for that reason you have to be keep uh what you what you say Perfection stre okay okay so because it will you have to only ah you have to use not Cas sensitive but you have to use only underscore between the names okay no spaces no any special character except underscore other than underscore the name range will not accept Okay by underscore sir it but but it will consider as underscore uh only UND I I don't know but only underscore it will accept only underscore okay okay I will keep in mind if you do like suppose example hyd hyd atate e it will not accept it will not accept Okay hycore underscore then only only underscore it will accept okay sir okay see example example in mail ID in MA ID uh it it will not accept any special characters except underscore except underscore yes sir yes sir yes [Applause] sir hi friends this is amay quy by rajinikant and here I'm going to explain you the first of all I will explain the query L I'll show you the solution all right here in invoices some errors are there the percentage of Errors the five percentage of Errors five sorry not percentage it is actually five errors in 122 bills so what will be the accuracy the correct bill invoices right it's very very easy so first of all I'll will show you before going to the solution I will show you if I press zero errors then it should get 100% accuracy okay are you able to understand if I press anywhere anywhere if I press zero it has to get 100% accuracy okay now here some four is think okay 97% accuracy so what I'll do first of all is equal to this divided by thisr divided by builds okay Enter so it will give you some like this okay now what I'll do I'll convert this into percentage not now not now okay so this is the thing if I change again zero see what will happen it will show zero okay so what I'll do here F2 Simple Thing 1 minus enter contr D I'll convert this into percentage okay if I press 100 here and here 25 so 75 should come here and 25r means accuracy 75% right understood I hope you understood guys thank you so much for your uh patience and learning a lot of things if you want more more more Mis queries here I'm here to explain you jam