MAY REAL MIS -6

Key Takeaways

is okay guys here is the query okay I'll Zoom it okay right actually here you have to find the matching records as per this but uh in V lookup in V lookup you have to find in this way whever it finds it has to pick up that value and it has to uh output should be here okay so I tried this one have2 but it is not showing the proper answer right so we have to make it a correct one right so what actually why it is showing the error see here we are searching this value in a which is in a row to the columns so it is unable to find this it is finding only one thing this one only remaining it is not showing so what I have to do I have to search this value in this whole range not only in this here here also here also here also here also when you are doing like this uh when you whenever you matching rows to columns you have to convert those to columns by using transpose then it will understand so you are searching the value in a row in a row wise okay the both things the value which in a row sorry the value whichin a column is searching in a row whenever you do transpose the both will be in row then only it can be find and the result Vector also like this so we have to do transpose here also right actually uh I like to tell you guys in some groups they said that it can be done with we lookup and match okay so I request in the in my group that try with we look up and match now I will press control shift and enter double click why zero because no value here why zero here because this is not matching wherever it find the matching it will pick up that value it is not matching so it is not pick uping the proper answer it is matching D here and D here and 300 years C and 300 years so 300 is matching whenever the value matches it has to be pick up like this understood so I request everyone try with the we look up in we look up and match so I hope it will not H look up make an H look up equal to H look up look up this look a value in this F4 comma match of a comma in this F4 comma 0 and again comma Z is it work or not do a okay now I have taken from here so you have to take the table from B here it should be B now let's check it out see and uh okay we try with v lookup again here instead of H we make it V lookup and enters okay again we try another thing but it never as per my view it will not give you the proper answer with we look up and match so again we try another method for that reason only I'm showing uh right okay no it is one two so okay we'll Place one here now it's working see again it g wrong answer answer 100 why 100 there is no 100 at all here okay got my point rep of C see for that reason only I said that we look up with um M will not work here if you can try you can try Okay in because I'm telling in the group okay for that reason only I I if if you want you can try in the group sorry if you want you I will post this one in group if anybody can try with we look up and match okay go ahead that's nice because I to learn from them okay okay sir and uh what about the query which I asked about the tech uh this one [Music] hi friends this is my Guru rant and this query ask from the other group actually check this first of all check this what I what actually the query about the person asking to extract or to find how many Accord numbers are deposited the same amount of rupees okay you have to find that are we able to understand what I mean to say so see here 601 100 rupes deposited again 602 I mean2 account number deposited the same amount here also the same amount right so here you have to extract so what I'll do I enter 200 here see only three account three persons deposited 200 in different account this one this one and this one 6 1 603 607 137 okay so we have to find that how to extract okay depends on the rupees you have to find the account numbers see how I'll do first of all I ask 200 F4 equal to any I'm taking each and every cell and freezing everywhere okay FN wherever it deposed that wherever the condition met it shows the true and remaining are all falses so what I'll do I keep in a logic okay if finds any true because we have taken not a range we have we have taken whole table okay including rows and columns for that reason we have to use the rows whole table F4 why I'm using that one I'll tell you F4 + 1 okay see here whever it find this true okay I'll press enter and I will show you first of all I'll go to logical test that is F9 see where it find the true it has to pick up the corresponding number so it will pick up the two semic colum means next row column comma means column semic colum means next row okay see 1 2 3 4 so it will check 1 2 3 4 2 in one it finds the one one and it will check whole this thing whever it finds it will it will pick up this one again the same thing where it find the true it will pick up the corresponding two okay if it is f if it is not there it will jump to another one the same thing the third one wherever it finds the true it will pick up the corresponding three here okay understood for that reason I have taken the function okay F2 so all thing at F9 see here one here three two is not there here observe guys two is not there three again wherever it find it can pick up the corresponding number from the row for that reason I have taken that one okay so I want to extract from smallest to largest so I will use the small function small function will pick up if I press one enter one it will show you the smallest if I enter two it will pick up the second smallest 137 is this and small will ignore falses it will not pick up the falses it will pick up only the numbers so I'm making true and falses to convert into 1 2 3 so that easily we can extract those positions so instead of row I will instead of K value in a small K value I'm using the rose function with D5 colon D15 e e e [Applause] hi friends this is Excel Guru amay Guru actually renamed by amay 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 06 2 1 0 then it will extract 1 2 7 9 here line is there see okay June 9th to first if I change to here 15. 6 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 col 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 ract 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 see I'll copy this whole formula control C and what I'll do means if no no 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 enter how many are there still will take contr D see we'll show you only one first to 15th only if I start 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 control 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 EV 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 1 0 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 M1 sorry see what I said find the duplic 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 showing 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 V look up only okay greater than one because you are interested in greater than one wherever it find true it will pick up as a where find the true it will pick up as this F9 understood only those things it will be true and remaining are all falses 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 c F9 so we are interested in true this two automatically it will pick up a b c okay we look up two 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 uh no 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 are there see it's very easy I don't know why you were 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 show all falses but we need true so what I'll do I'll convert this this two as a month F9 so 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 operator so I'm not using any double negative to convert through as falses because double here as is there so it's a mathematical oper operator so automatically it will convert ones and twos F9 it's not converting so somewhere we went wrong here totally here like this F9 only one is showing not two why only showing one here two are there HR a HR okay only one is there some product I will show you some product and close pares control enter only one 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 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 calls so I want to know who is improved from morning to evening that is for morning session to evening session that is 11: to 2: p.m. and 2: p.m. to 6:00 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 so 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 table sorry not table ARR I'll take only numbers contr C control V comma here it is showing the maximum so automatically to pick up the 34 okay here it will pick up the 34 so I have to take ch right I have to take CH two comma Max of this sorry one second chose of 1 comma 2 comma X of this equal to control V you 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 comma 2 comma 0 what happen and here I think some went wrong okay I not choose the second one okay only one I comma the second one is name rakes is the person who improved maximum calls from afternoon sorry for morning to evening session okay coming to the fourth query and if you any doubt again and again I'm asking it's very easy guys why I don't know no one is 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 mat anything you can okay first of all I'll remove the format Al e f alt HBA simple thing why you are wasting I not able to understand that one match of this comma 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 I don't know why you are wasting the time here now it's very f 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 1 to 5 it will randomly changes not five there six totally 1 2 3 4 5 6 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 F9 so conver 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 s 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 support ram [Applause] ram e hi guys this is am Guru rajinikant and here I'm going to explain you the questions from the company okay I already mentioned the company name I don't want to reveal that company name now okay coming back to the question and answer okay so actually what it is here is the data okay you have to change a data in this format okay it may be a large data but you have to change like this so before starting what it is actually we want 1 2 3 sorry one 2 3 and coming to here 4 5 6 and coming to here 7 8 9 like that we need okay example like that we need so what I have to do now we have to bring data in this format I'll Zoom a little bit so that you can able to see properly so here is the here is the solution to have to do see first of all what we have to do here okay do first of all I'll use the rose function sorry columns function and where we are I okay dollar sorry I do7 colon i7 right contr enter control R control D okay um I like the I lock the row but not the column so you change like this control enter like this okay now how you'll get after three you have to get four here so what I'll do here I will add a row function row of I doar 7 colon i7 okay I'll show you oh how to do 2 3 4 okay control R okay so how many things you need three things first of all before going to it I will do minus one come to end control enter control R here what it is missing it's missing only one small thing that is just multiply with three because we need three it's a normal function see now I think everyone understood this concept right if you have any doubt ask me you make it F2 when it's a normal function it's not an array function index of this data F4 comma and close parenthesis control enter control r control D okay you can drag it contr D here also because it's not not more than this so I'll convert this into normal dat by using control shift hash you want you can check it out here ABC ABC product one here okay now question number two yeah what I asked first of all try to understand the query what I asked here you have to find the first of all you have to find this one okay wherever tl1 is there actually this is a question number two so it is a team strength the 30 53 team strength these are the designation what maybe so you have to pick up the maximum team strength from here that is 84 this one who did ma in the team strength total 5 hours 22 means the strength is more in the team then what is the call duration okay the strength is more pick up that call duration from there total call duration among Total Team strength from the total team strength okay here I will do sorry guys first of all I'll search comma here this F4 okay so what it will do it will show us the position that where it is occur what are the positions here if I use math what will happen it will show you only one thing first urrent of a value but not duplicates so here it will show you all things F9 see whereever it it show the numbers like this 1 2 3 4 totally four are there so I want to rid of Errors so that I'm using e number over here multiply by what the right of two things the right of 2 f4a 2 F9 okay I forgot parenthesis F9 see pick up all things here is the thing you have to observe it will become true and falses okay before going press enter see F9 true true true true because I use the EAS number F9 so it will pick up and it will convert actually the text number because we have used the text function that is right for that reason it's showing the double quotes when when we are using the ASC mathematical operator so it converts into a number I highlight both things and plus F9 okay Escape oh what I'll do here F to copy this contrl C Max of this so it will pick up the max so I'll use a match function to pick up the maximum team strength the position okay guys here I see now so I want to pick up that position for that reason I have use the match function so if I press control shift and enter it will show us the position that is in third position okay now coming back to this and we have to find the duration then use index of this whole thing F4 comma and close parenthesis it will not give it will give you the general format so we have to convert into number format that is actually five hours 22 and 24 so we have to make it control one seconds also we need for that reason I'm doing seconds that's it right for me it's different maximum this one only little bit different for minimum okay and contr V here what I'll use I'll use a comma here comma and L it's a Max yes okay it's right on it's not right where I went wait a second yes he if he number where is match we have to match if is number so I'm removing this one if is number if is number and in place of Max I will use minu let me check I press enter it okay logical text and remove one bracket from this there so it will come value true this one value false no need of false here right okay close parenthesis okay for math logical test yes number okay remove this one the last one here see I'm closing here okay not here logical okay I have to close this one now because I need the true is number value true where is value true yes close parenthesis and close parenthesis now see it will give you the wrong answer no need to worry I will explain you because here it's a text for that it is showing the zero I double negative F9 yes reference error why reference error if you write to okay search to right okay instead of this I will use this one in of right I will use this one okay right also we need okay done done done very good okay row 53 is showing yes 53 but position is not there okay 53 is the answer but we have to use match again here match of only number this one only contr c m m look value here is the look value so I use comma here look up match comma 0 comma Z control shift same tl1 four actually 4 hours actually I will do this format painter see uh where it is is see here tl1 here it is so it will show you this one okay now coming to this part okay coming to Third okay you have here the country names are in capital letters so you have to extract only the country names right so here I will use mid of substitute of what is the delimer delim is the space Amper this one comma old text space only comma new text repeat space comma 255 times okay this one part is over and second part is starting number so we have to use row of indirect from starting to all the things you need so I'm using length of this close parenthesis close parenthesis close parenthesis and 255 comma 255 I'll copy whole things because I need it but not equal contr C also trimes in previous video I explained this function F9 okay so I use the exact match over here for this thing comma this thing close parenthesis close parenthesis F9 so we are interested in this true why this two here so you showing this true okay so what we have to do here here we have to do some less enter actually here you have to do some logic over here then only it will show you the correct answer here you have to use upper function because we are making each and everything into upper upper now F9 see exact text one text two it's a normal I use the match function match of true comma here again comma zero it's an array function first showing fifth one no it's not showing fifth one why it showing fifth one so somewhere went wrong that to show one procedure is correct only iuse index over here I no here I have to use yes F9 see to shift and enter contr D now I use index to pick up you're also trim if you have any doubt ask me I'm here to explain you guys no need to worry about that control shift and what is the ARR first of all let me check oh another bracket here what is the yes what is the row number yes and close parenthesis in last PR ENT see okay it's an error function here is a very easy guys I don't know no one has tried this one I'm using some if range this one criteria this one wild card comma and Su this one that's it okay I forgot the here you can change to females also your data validation so I I'll remove data validation like this em enter contr c going to here control V now see the check okay Maes also you can check okay this is the thing Mals countries everything you can check because it's a wild I'm using here wild card for that reason okay and if you have any doubt ask me here is the thing the fifth one okay okay in fif I will explain to later guys it's very easy it's not a complicated one very easy but I will show you in lat session okay thank you guys yes up [Applause] hi guys now this is really a magic in Excel as of now still now I have not seen this type of magic in Excel whenever you change the sheet name like ABC is there ABC CBD is there so I want jkl value K here okay I'll color first of all I'll color it so let me try Okay I'll Zoom a little bit now see see if I change the values from here it has to pick up the corresponding value and it should be going output here wherever in the sheet wherever in this sheet you can place it that formula which I'm going to show you but I am giving assurance that it will in 365 this function will to say no functions are there in 365 to do this one you have to use this function only okay only with 2010 functions only you have to use and I'm changing the value to jkl observe here guys clearly observe JK KL okay I'm pressing only enter okay if you want I can show you once again dgf the value is 55 you have to pick up 55 here where it is what happened DF why it's not working already taken okay already name has taken here in the Shi for that it is not showing GH I now see the formula cell in cell there is a options called address column color content file name format parenthesis prefix protect row type WID but I need file name comma and the reference whichever cell you want you can pick it out it doesn't matter this function will show that where the file location is there okay where the file location this sheet location totally where it is so always I'll give you J1 it will show you the drive folder file name see C drive test I'll move a little bit is the this total file name the location of the file totally including the sheet name that is GH Ty okay see now what I will do I will use a replace function and I copy the old text that is this one and I want to it's a replace function it is asking the from starting number that is one where where you have to start from one to number of characters how many number of characters do you want we have the function has to go this comma where the location here is the location close parenthesis comma double quotes before going to enter I will show you what I'm going to explain you old text F9 so I'm replacing from first first here to still here the closing square bracket from starting to here okay understood from starting to here I want to remove it and it should be replaced only with this GH it should be there only with DHI and searching this one the closing parenthesis it will show you the position number of characters 47th position is there means I'm using replace function to replace from first position to 47th position replace with null replace with null that's it enter see if I change anything here anything it will reflect here anything like uh anything raakesh one observe here guys observe nice onece understood whatever the SE name it will appear there 1 2 3 XY Z MX anything anything to reflect here so simply I use look here simply I use I use the this function before but uh not for that for the location of the file I use this yes for the location only actually this file this function only for to pick up the location only with that with that only I'm using this one the date it will show you the N because raage is not available is not available there it's a normal function control enter if I type Anand here a n a n and Anand see Anand is there if I change to jkl see here again jkl is K this is I'm telling now this function still now I have not seen anywhere yes sir yes sir if the name as it will output will be 55 right because yes B2 in front of output a column this is a455 It's Working like that like we look up for example yes we look only only I'm pick uping the sheet name that's it all functions are same we look up function only that from this file yes sir what we need to find name yes sir yes understand understand sir okay okay then any doubts clear everything is clear oh wants to ask something yeah on what purpose you mentiones can you repeat once again a a is the cell seet name c one thing why because we are we are first of all we are asking the cell function to pick up the file location where it is it may be A1 or it may be any any cell like like this one also you can take C it then also it will work name is coming from there yes any cell any cell in this sheet only not in another sheet okay then only then only it will work otherwise it will not work you have to pick up that sheet name only that sheet that it will take from another sheet like like another one like any other like this that that's the thing that the thing will happen do like that it will pick up that one so better use in this only ENT another one more question bro okay you can why use AR function I mean close close see here I'm not doing see before only I explained again I'm explaining old text okay F9 see here the close parenthesis is there okay I want to remove from this one to this C the drive is there from here to here I want to remove and replace it with so I I'm searching this one I'm searching this one this close parenthesis after that the seat name is coming we want seat name here that's why repl yes from from drive to sheet name okay you have to remove each and everything and replace only sheet name that is the thing okay okay okay got it bro no doubt no doubts bro got it bro okay you guys create something in this like this okay anything anything by your own any any table or anything whatever as your wish but you guys create something like this not not not this one like use index match index or anything any such things so that it will this function should use over there this whole function should okay whole function should you have to use index anything yes sir okay suppose I'm giving example see whenever I change the sheet name like this here it should highlight any those values which is available in this range okay those values should highight over here JK is this if I re G here it should highlight these two things automatically okay okay whenever you change the sheet name automatically it should highlight those both values which matches in a range with this function with with the cell function it's cell function only you have to use you have to use this function with this also you can use any function but this function is mandatory after this function you can use any other functions but this function is mandatory this is the base for yeah this is the mandatory you I want to know how much you understood yes yes sure anything anything okay but this again I'm repeating this function should be mandatory not replace S function okay let's see how many many will try okay okay okay thank you guys it's now visible okay now here is the query I'm going to start that what is the last text as per this name you have to find the last text so what is the procedure see this is the query for actually this query for team leaders not for Mis team leader position okay and total 10 questions are there this is the first five and remaining five I post again okay tell me tell me tell me index of I'm taking whole table whatever we need this whole table F4 jumping back to the formula comma I need the row okay so for that reason I'm using a math function to pick up that one comma and here it is control backspace to jump back to the formula f 4 comma 0 we have taken whole table for that reason it it will expecting row and a column so I have given a row so for column leave it is blank or zero likees it will understand if I press F9 it will pick up all the name three I done eight these three things will be pick up C I done and blanks show that's 0 0 okay so we are interested in a text it may be a see it may be a double text also don't think that we can use a match to pick up this one if it is having double text see one second contrl Z enter for name three what I'll do I will make it another text like uh um ABC ra raakesh okay so you have to pick up the rage but not this done F9 no no it is not I have write name wait a minute name three I have to write I wrote name one name three where it is okay name three I'm moving this one name three okay yes sorry guys I got a call for that reason okay I'm back now see this is the last text we have to find okay are you able to see the screen first of all let me know yes sir yes sir okay okay so raes is there so we have to find the rakes now if I highlight it will show you the raage also but not the done so what I'll do I'll copy this one okay and what I'll do is is text come to end F9 all TRS are there but we need the last true but not this true so what I'll do I want to make it a numbers okay and remaining are all zeros zeros or something whatever it may there errors zeros whatever it may be so what I'll do I use one divided by so false will become one divisible by zero error divis yes error yes numbers okay we are interested in last number so I will use the maximum number here look up any number I'll take will work will yeah because maximum yes we have to pick up the maximum value and showing one so I'm taking 10 you can take it as much as many as I I going to say that 10 12 100,000 10,000 no issues and just now I copi that one and I past okay one second see raakesh control Z control enter it will handle array now ask me the question okay okay no it's clear uh okay uh by we use a second index for getting the name raak yes yes see I'll use the look up formula over here and convert into text see first one this one look of value and look of vector and result Vector so what if if if the look of function will pick up that number okay so what next we need we need the result okay what is the result that so you have to pick up that one and look work look up work vertically as well as horizontally horizont yes yes look up work both yes it is not it is not having any restrictions okay sir yeah okay thank I'm copying these things F4 control enter and dragging this control D here what I'll do e number EAS number that's it in place of that I'll use EAS number control ENT and again can I drag this one no I think no ma we will use yes yes yes so what I'll do here see the same formula I will use but not that one this one index one contrl C escape contr to S is equal to contrl V now it will pick up all the numbers numbers and text Max Max Max and control enter it is working yes okay it is working no issues okay now coming come on tell me instead of raak we put any value it will because the maximum value uh if we enter 10 uh 10 then 10 will appear otherwise if lesser than eight then eight will be right yes yes yes yes yes yes okay coming to this it's very easy I don't know no one has answered what will be the H and nothing is what I said you have to find the HD me average call handling time you have to find average that's it average that's it okay there is no need of other data okay no need for other this is just for confusion or ah yes yes for confusion only they did like okay yeah yeah okay okay and coming to this one this is very big formula can okay I will make it this one okay this one this one okay see first of all what I ask you have to find the maximum salary for IT department sir sir yes yes sir can you little bit zoom out because uh this is unable to see okay okay now okay no okay see what I'll do here what it ask to find the maximum maximum employee salary for the Des designation of it so first of all I use find find it capital letters because it's a case sensitive you have to find the capital letters okay where because see here in unit also it is there okay in unit also it is there so they wantedly made like this if I use search it will also pick up this one also it will pick up because sear will not yes sir yes sir okay now yes is number multiply by this it's very easy F9 and make it Max of this and what I said here sorry control what I said here what is the max nearest to maximum salary for the it only okay only for it okay only for for the answer is it team leader okay okay nothing is there you have to use equal to this is greater than sorry less than this if than this comma pick up that one F9 okay not less sorry every confus greater than F9 so it will pick up like this so we are asking to pick up the greater than those which is maximum salary so now I'll use a Max function to pick up the nearest maximum salary for the ID Department got it s yes sir only for IT department not others any me we can do the large function large two then yes why that easy question they will ask in that companies they will they will confuse us [Applause]