tech mahindra mis

Key Takeaways

hi friends this is the query which I asked in the group to let you know first of all I will explain you this qu L I will show you this solution okay see what it is asking means you have to find the sales representative sales as per month okay three different sales person three different months so you have to get them total sales maximum and minimum see so many actually as per my knowledge and experience and skills having only one solution for this if you'll use some product also it will not work if you'll use some also it will not work see it's very very logical and Magic query which I'm going to explain you guys see first of all I'll I'll Zoom a little bit okay see first of all what you do I explain this L you have to show how to highlight these things as per here okay see match of match of whole thing whole thing F4 comma here F4 comma Z F9 okay okay I have given the names now so let come back here and I will this one so it has to find the positions it is the positions in 2 three and five right and I'll use the index but it will not work you have to do some magic from where you have to start from here F4 comma the same thing but here month should change here the month F4 4 I not keeping the comma here F4 comma F4 comma Z okay this is the row here is the row and here is the comma see what will happen if I type row it has to give positions first of all I'll press enter because I don't want to miss that formula row number F9 and column number F9 sorry column number F9 but it has to work right it has to work but when I highlight the whole table it give you some errors 160 so it is not the right answer okay it is pick uping only the one thing 1670 where it is Krishna Jun Krishna June see only this thing it is pick uping but we want the three things Krishna Ravi pravi June November and February so here you have to do some logic here have to okay formula is there now okay see here you have to do some logic I'll Skip and then move a little bit F2 here you have to do some logic yeah maximum people is unable to find why this function is going to work how this function going to work see and for number because we are searching for and if one comma and close here the part and again the same thing n Open Bracket if Open Bracket one any non zero number considered as a true comma close parenthesis see let's see is it work or not F9 we also F9 yes it is working now just see the magic F9 okay the three things 1670 6 65 61028 see now Ravi November 6 6 let's check I'll press enter because I don't want to list November 656 let me check RI November 656 see here see here okay now simply use a some for this F2 some product of all I'll copy this formula because I have to use number of times control C Escape as to some product control shift and enter control enter it will not work control shift enter okay if you want you can count it see count it no need no issu we will count it Krishna Jun where is the Krishna Jun Krishna June where it is June here it is 1670 okay I highlight this one next Ravi November Ravi November here it is 656 okay February February see here you can see the count 359 why doing the wrong the procedure is correct I think I what you say okay I highlighted this one also for that reason I highlighted this one also so here 656 and 1670 and next 69 so February if you add okay I will write here that's better 1670 next Ravi November Ravi November where it is R November 6 by6 okay 6 by6 next next February what is the pr February can February 10 to8 10 to8 alt equal 354 354 if I change this to August okay August see will change dynamical okay I already saved the this so I use a Max function control V close par control shift enter and me okay is equal to Men of tab control V close parth control now you have to highlight these names as for this whenever I change this it has to change dynamically okay as for this you have to highlight those things right in next session maybe next week or maybe some days I will show you this one all right thank you guys thank you so much for your patience and may God bless you all ram ram [Applause] hi friends this is am Guru after a long time I am back with the conditional format of first query how to do the conditional format how to highlight the sales person name see how I highlighted if I I'll Zoom a little bit bro sorry from only this one you did right no bro you and only Dido last week no I did not highlight it just I said to highlight the uh sales person name I only did the total sales maximum and minimum but I did not said how to highlight that uh sales person name okay okay now got it can press it BR okay so here if I change Raja to uh sorry Raja to some other name which is jna okay jna is there okay jna DEC so automatically it will highlight the jna December where jna December yes it is 82 December okay what is your idea how to do this [Music] one yes yes yes condition format only but before starting I will explain you one thing so that you can able to understand see first thing when these are like this then it's called and and conditions are like this a b c and d e f so condition is and I'll place it here and okay when condition is like this ABC and JH some JK when it is like this then it's called an r or when it like this add okay this is the thing which I'm going to show you if the condition is like this it's called or when the condition is like this it's called and yes sir okay this this is the thing I'm going to explain you guys here so both condition match yes because or and and this yes when yes in end both condition must be positive must be true then it will work in in or out of two or three one condition is M that means it is our right right right exactly exactly exactly so here see what how I do I already ped or here or and open parenthesis this one I'm freezing go rows and columns is equal to this one I'm freezing only the row sorry column but not the comma and again the month is there here month freezing F4 equal to G ising row but not column one hand is over second and this one F4 equal to starts from here F4 here you have to free column here also and here also and comma and month F4 equal to J here I'm feing the row but not the column again and three conditions are there totally and F 4 equal to this one freeing the column but not the row comma this one F4 the month equal to here I'm freezing the here I'm freezing the row but not the column okay so these are the three conditions if the any of these true here is a logical one any of these true it should highlight okay so it's an array function so I'm using control shift and enter okay control R down control D see wherever it finds that it will show the true so what I'll do I highlight this whole thing first of all I'll remove the condition clear selected okay now I will this I copy this formula from here F2 this whole formula including equal sign control C Escape highlight the thing which you want to highlight the data only not the names okay not the names only the data which I want so I'm highlighting only the sales alt HL R think it will not show new rule here you'll find the new rule control V format with orange only fill with orange F with B gold and black okay okay apply okay any doubts whenever I change this one whenever I change a month or a name automatically it will highlight those sales from here from here to here okay uh B out sir yes yes one out yes go ahead uh I understand the and gation okay why first we use or yeah why I'm using first yes yeah at the first the formula only or yeah yes yes here only I said that and here and yes and here here or here or okay we also want the names name and then month okay okay so and whenever it find any true among this yes sir yes then it it has to show true it has to show false for that reason okay sir okay that reason and how many guys did not ask why I used NF logical functions what is n and what is why I used over there here I have used here n function yes sir F2 here n function is this why I have used this function see there are two functions one is T and one is n t for text n for numbers okay if I 25 M A BC okay if I use n here it will pick up 25 okay when I use T here it will pick up this text see so I'm asking that function to pick up what wherever the numbers are there pick up those numbers all numbers not one number all numbers reason yeah go ahead uh for example if we write ABC 25 and use t and n formula in uh different columns then it will extract the ABC no no no no no in one cell it will not you want to two two difference u and v u and v okay ABC 25 ABC u in U function and 2 okay yes sir now in any use okay yeah equal WX yes in W equal to uh U 16 uh n n number number uh U6 click on U6 again okay you want to make it like this yes you not you sorry T is actually okay right you want to it's 17 it's 17 no no uh last s uh U 16 this is the 17 yeah that's what I'm said oh yes yes yes see one second I think it is not zoomed are you clarify the out yes sir yes sir it's concatenated both things no no no no I don't mind to conate you want different yes yes yeah if I use my question is in cell 16 U6 you have written ABC 25 right yes in in uh V7 uh I if I write equal to equal to T then is U6 yeah this will extract ABC yes listen consider a text okay consider a text not a because ABC is written yes yes yes ABC if I remove ABC then it can understand that it is a number number okay okay like that okay understand and for that reason I use the logical function over here okay that if any true nonzero numbers considered as a true if it is true then pick up all the numbers wherever it find in a array okay for that reason I have used the logical function directly it will not pick up those numbers you have to use a logical function for that it will pick up all the numbers okay that's why use the number if condition uh must mandatory for this okay okay okay sir because always n will pick up single number not the array numbers so for array for array I have used the if logical okay this is the thing okay sir any more doubts no sir no sir yes sir yes sir I will practice if I uh got any uh doubt I hi friends this is am my Guru Rajan and here this quy which I posted earlier in telegram group so still now no one uh answered this query and here I'm going to show you the solution for this that is actually the query is find the cell address of the employee name who did maximum projects in the month of Jan so we have to find the sell address of the employee name okay so here the totally two conditions that is January and the project maximum later we find the employee name see how I'll do first of all we have to find the ma first of all we have to find the how many are Jan here so I'm using a textt function over here to pick up where is the Jan how many Jans are there and where it is so I'm equal to it's a text so I'm using Jan F9 so wherever it find the Jan that is January so it will pick up the true and remaining are all falses so here is the condition to find the first of all you have to pick up the maximum sales or a project of the month of Jan so I'm using this max if a logical function and pick uping these projects F4 so it will pick up the maximum projects done in January so I'm using control shift enter for this because it's an array function so 62 is the ma maimum compared to Jan compared to other months the January is having two only two projects have been completed in January one is 62 and one is 70 wait a minute for 57 one is 57 and another is 62 so maximum is 62 okay so what I'll do I will ask where it is F4 okay where it is F9 so it will pick up the only one position wherever it find the 62 that is maximum projects done in January that is 62 1 2 3 1 2 3 okay guys so here is the one condition is over and the second condition is now you have to find the row address of the employee name the two conditions over first of all let me let you clear everything that is January and the sales and you have to find where it is so two conditions is over now you have to pick up the row so we need employee name right so for that reason you have to use the row of employee F4 wantedly they they will the company will confuse for that reason it will take some time to understand so it will pick up this one so five but five is here is showing five see the row number see how I'll do is this all or F9 true true true the third position is true control Z again have to do f to see the third position is first of all 62 I will equal to this project F4 okay into the row of the row of this because we need the S representative name okay why what's happening here wait a minute the row is closed the row is closed some something went wrong the [Music] proed yes number two okay you have to remove this one okay and close the parenthesis here so let us check like this and you'll find F9 okay somewhere went wrong here again check whether it where it went wrong the maximum logical text and value true so maximum F9 number two is not there okay so I'm asking whether it is true or not so bracket for this let us check okay and R number F9 so it will pick up all starting from three and ends at 17 3 4 56 so what I'll do first press enter and I'll step so that you can able to understand EAS in the third position that 1 2 3 and here the row position pick up the fifth position that is true where it find the true will pick up the fifth position okay guys so totally F9 all zeros y [Music] Max yes and here is there some brackets I forgot every time the brackets where from to understand guys logical text and value true so remove this one F9 okay okay equal signal sign F9 Zer why zero braet iset Max is F9 where I'm wrong the procedure is correct but where un to understand that one maxal so logical text and Val so number one n yes here uh all zos why all zeros have to pick up that one January where I went wrong why zero January is there let me check again F9 62 equal to find whether W 62 F2 F9 the third position okay so I'm using the whole thing the whole thing into we need the employee name right so here is the employee name F4 actually I have to keep a row over here row of employe name F9 so it pick up the F so we need this F okay so what I'll do I use function that is all Z 0 + 0 0 + 5 = 5 only okay I copy this whole thing from here because the same formula except one change that address here is the number five and comma the column number in of row I'll do the column column okay close parenthesis one close pareses at9 so B5 is there so what I'll do here control Shi enters where I went wrong here okay I have not the number c here I will to use column contr V inad of row column that's it okay guys B is the sell address where the employee who did the maximum projects in the month of Jan and if you have any more doubts please let me know in the comment box or in my telegram group and guys by today or tomorrow I'm going to actually this company is not yet completed totally 20ies are there so I have completed only two queries till now so after completing this query I'm going to start a new videos that is Mr Junior Bill Gates I also call him Anand and I and we are both are St a new video where one query is been solved in VBA and that the same query will be solved in Excel 2010 functions so both so I everyone please cooperate with us and ask the doubt in telegram group thank you guys thanks for your support have a nice day ram ram is hi friends this is my Guru here I want actually this query from the company which I posted earlier in a group so first of all we have to count how many are this okay so but the query is to find the person or agent who did not reach the target okay sorry actually we have to find the person who reached the target okay these are the person who not reached the target okay these are the person who did not reach the target so you have to find the person who reached the target okay so it's a c maybe take some um uh 9 hours work okay in office So within 9 hours of work 1 hour gone for lunch tea break all those things remaining 8 hours in 8 hours how many have reached the target this are the person they have given that they did not reach the target but we have to find the person who reached the target so it's very simple okay so first of all we have to match how many are there here okay so I'll use the match function and I select this one control shift down arrow F4 comma and the lookup array Al this one that is previously the lookup value now it's a lookup ARR exact match so it will show us the two things one thing wherever it matches these agents who not to get a Target and the person who did not reach the target so here NA means not available not available means available these persons who not reach the target who did not reach the Target and not available means reached the target I highlight and I press F9 see Ravi is in first position he did not reach the target n Raju is not at all there and two and third one Kiran kir the second place so he did not reach the target so remaining n are the person who are not reaching the target sorry the person who reached the target the person who reached the target so here n are there so I'll copy this not now so what I want to I want NS because already are there the person who did not reach the targets are showing the position where it is now I use e na so converting NS into a true and false F9 wherever it finds the na that is not available so it will show us the true and remaining are all false so I convert Tru I'll copy this one now control C double negative so double any mathematical operator will convert true into numbers that is one fals to zero okay by using any mathematical operator so every time I use double negative F9 previously it was true and falses when I used the double negative before the formula so it converted false as a zero true as a one okay we are interested in one so simply I use one function that is some product it's an array but some product can handle array so totally six there who reached the Target now we have to find the person who reach the target so what I want first of all I use if is a name just now copi that one okay if he a name what you want I want row of whole thing control shift down arrow F4 my row of this F4 + one this type of thing formula array constraint are used in lot of questions lot of problems lot of queries okay again again I don't want to explain all those things first I highlight this one and I will show you F9 see 1 2 11 we we need 1 2 3 4 5 so it's 1 12 l so whereever it finds the na it will pick up that corresponding number from here okay so I close it F9 see second it's pick uping second 4 5 6 all the 9 and 10 these are the numbers I need so I'll convert these numbers as a row numbers Okay small I need for smallest to and the K value will be a row first smallest second smallest like that in my previous video I use this one G7 G7 okay this is finished control shift and enters okay these are the positions okay so now I want the agent name okay so I use one function index of this down arrow F4 I'm I'm locking only the row but not the column when I it moves from here to here it will pick up the B value as well okay close parenthesis now control shift and enter I drag for formula what I'll do here again there is lot of things so I use a small magic function I copy this only K part what I'll do this is a new thing I'm going to teaching you guys control V greater than if Ro is greater than six F4 greater than six me what you want I want blank else run this formula as it is control shift and enters let's check it out whether it's working perfectly or not see ramu 46 pravi 46 all are reached the target remaining are blank okay understood guys and if you have any more doubts please let me know in the comment box and if you find useful in this video so I request everyone like share and subscribe thank you here actually this query asked in telegram group so what is this query actually I like to explain you guys see this is the Amazon okay so we want to find which city is selected Amazon for shares okay which city selected sorry which s which city is opted for Amazon company okay so whenever I change here okay here it is not there RDL I select this one F whenever I S the company it has to show that which city is selected this company so first of all what you do index of I'm taking two way look up I mean to say it's having rules as well as columns so whenever you take rows as well as columns so index will expect both things whenever you select only range in index will expect only either row or a column but when I selected whole table then index will ex both things rows as well as columns so what you need I need to find the how many are excess here in this range okay so I'm leaving blank so it will understand that you have to it has to show whole table I mean say whole range as per the selection that is Google match of Google comma comma zero so index will understand that you have to pick up whole range of a condition that is Google this thing F9 see X nothing is a zero har nothing is zero so X here so here x is there so what I'll do I have to find the position of the X where it is actually so I select X comma Zer okay so it will pick up one or two or three so it is pick up the one when I select Amazon it will pick up two when I select flip cart it will select three so it is one simply again I'll use the index function to pick up the companies because we need the companies and row is the one close parenthesis now simply control shift and enters whenever we change the company name automatically it will pick up the city okay Amazon flip cart so it has to pick up harana right see and uh I like to inform you guys that as early as possible uh there is a two things one thing is that the qu which is solved by VBA and again that the same query which be solved in Excel function because Anand I call him Mr Junior Bates and myself myself will solve in 2010 or any other functions but Anand will solve in VBA as ear as possible we are coordinating with each other and going to start this session so I request everyone to participate and make it success thank you for your okay sir okay thank you so much for your concern okay sir thank you thank you B you all after a long time okay so here I have given this query long back but I don't know no one has been tried about this so here I'm going to show you the solution for this right see what is the query actually find the total sales of respective employee by using suif only okay so how you will do with Su if okay that will be the very logic you have to use over here okay so what I'll do first of all what he said you have to find the total sales of meline Scot and the person did sales on South East North okay so you have to find South Region total sales of the sales person if I change south to North Sea automatically it will pop the correct answer how you'll do see not Med s okay so we'll see this is the not okay and m s what is the m s so here I will write those sales over here to get you better understand so here it is not 310 and another thing 21 38 and another thing is 3963 okay now alt equal 941 is it right over here let me check see understood now I will show you the procedure how you will do this one okay now here first of all you have to get total range of this okay as for this okay as for this you have to get total sales if not so you have to pick up all things only not okay let's try so what I'll do here I will use the index function is equal to index of whole thing okay whole sales F4 comma and I I want the column so what I'll do sorry I want the row so I'll skip the row so it will understand that index will understand that you have to pick up total not range and you have to use the match function that is this one comma here it is F4 comma 0 so it will understand that you have to pick up only the north range we need only North if I press F9 see what will happen 2954 310 2954 all not sales over here still 4792 4792 are you able to understand what I mean to say control z now I will use a function called Su okay if you want some if okay some if some if range so you have to pick up the range and criteria this will be the sum range so the range will be this one F4 comma and the criteria will be this one comma and Su range will be atically this one F9 and I highlight this one F9 okay somewhere I went wrong here mhm [Music] some range is right some is wa a minute range criteria some range exactly what will have what happened anything wrong bracket let yes F9 941 C control Z is a normal function it's not an array function just you press enter it will populate that the correct answer okay coming to second part what I that you have to add last five row and it should be a dynamic it should be a dynamic so what I'll do first of all I'll convert this into a table by pressing control T enters so it converted into a table okay so now what I have to do here just use the offset function offset and the reference will be this one and it will start from zero 1 2 3 4 okay this is the reference so what I'll do how many rows you want to move okay Ian not move how many rows you want to jump from there from here to there so I will use a count function where it will pick up the all numbers f for all numbers what will do it will pick up how many are there totally 14 are there from here totally 14 are there but we don't want this one we want last five so what I'll do I'll skip the column and the height will be minus 5 so automatically plus 5 means it will go forward minus 5 minus negative means it will go backward so I use the minus 5 for that close parenthesis F9 see v9 65 is the last 1 2 3 4 5 36 76 see simply add those things some product enter if you want you can check 1 2 3 4 5 1 370 I'll show you is equal to sum of okay we can added the this one so I'll remove this one wait a minute alt EA I'll move here is equal to last sum of last five okay 2 3 4 5 enter 1370 okay if I add any new here any new number here like tab tab like uh 1,000 so here how much it is there 13 so 1,00 means 1470 yes three 4 oh last five this one1 094 sorry1 094 here1 094 okay understood and if you have any more doubt please let me know in the comment box thank you thanks for your uh patience and learn more from amay Guru and you can join I will mention my tegram link over here so if anybody wants to join welcome thank you guys thanks for your support