mis interview questions part -1

Key Takeaways

hi friends this is Excel Guru rajinikant here I'm going to share a solution for amay interview question part one so here is the question which I'm going to explain you clearly see here there are uh duplicates employee names are there okay not one more than one is there okay so you have to find if I enter am it has to find first am second am mobile number see am is having more than one mobile numberers whenever I enter any am here that second am third am mobile number so it has to show accordingly if I enter any name in this in between this if I enter any name it should reflect that first uh mobile number of Am these numbers actually what happening means these numbers are entered uh manually 1 2 three four like this for that reason isar came to top first mobile number if I enter here it is is first mobile number not second mobile number okay is appears more more than one means each for first mobile number and second mobile numbers understood guys so here you have to find the mobile numbers of am am is whatever the employee numbers we enter over here it should reflect their mobile numers accordingly now coming to the solution so what I'll do first of all I will ask condition that many amans are there how many amans are there whever amans found it will show us the true remaining are all falses right and this array I used so many times I'm taking only this one I don't want to go again back to there minus row of B4 F4 plus one okay this is the first condition see I'm doing this with only V lookup not index uh anything okay only V look up and some other functions later on you'll come to know but it's very important maximum companies nowadays they are asking these type of questions for that reason I picked this one the maximum uh uh question okay so here if I F9 it will show you the positions one and seventh position is Amon and 10th position is also Amon right so what I'll do here I will keep a choose because I want two tables one is employee names and another thing is mobile number so this is employee name the first one is employee names and and the second is already copied now I only control V in place of B I use C in C range there is a mobile numbers okay in C range you find the mobile numbers for that reason I simply changed C nothing I did okay F9 see wherever it find the numbers it will pick up that numbers and zero also there we need 1 seven and 10th positions only remaining zeros we don't need okay remaining other than zeros only we need other than Zer not Zer okay that is 1 7 and 10 okay after control shift and enter after coming down it will give you another one here F2 so here see what will happen F9 here also we need the first one here it is where it is 0 0 0 0 where is one one should be there I don't know where one went all zeros are there fourth is there yes fourth not one it's a fourth one okay so we need fourth because is in the fourth position first mobile number of isar in fourth position okay Escape see how I do here now I will use another function that is we of all we take V lookup as it is here I will use small small if I'll copy this one from here control C because again again and again I don't want to go this there comma if it is true so pick up this one so again again I don't want to go over there for that reason control V comma so not comma close parenthesis over here and we don't now we need the K part see here K part K means which one in small you say small okay we found 1 7even and 10 so you need which smallest do you need to pick up that mobile number so I because here it may be some duplicates will be there okay duplicates will be there so what I'll do here every time I will use a rose function 1 2 3 but here I will not use rose function I will use the countif function countif what it is H for H dollar for colon H4 close parenthesis comma not comma close yes comma comma the this will be the table array F9 and this will be the lookup function it is small what happened to show column IND number okay we have to take column index number comma second column zero exact mat so I think here I did something wrong there remove one bracket from there let's see small array and K yes and again close paresis over here control shift and enter let's hope it works okay no it's not working somewh went wrong here I have to use another close I think so close parenthesis look up value why not coming look up value it has to come accordingly it has to come and table is perfect and column also perfect R look also perfect and why is not coming it is correct only now okay okay okay I did not mention the criteria that is H4 so for that guys look up array table array column index number range look up small let's see what will happen for small it is pick uping one see control shift and enter I will explain you control D see I'll will explain you okay I think I have changed the name over there okay M right okay see here what happening it is pick uping the second mobile number ofs 96 uh 964 5964 am 15964 see okay and what is there iswar iswar 618 right here I think I did something mistake over here somewhere I did not lock I think so everything has been logged I think so wait a minute wait a minute F2 M sorry not H6 here I typed H6 here H4 only here also H4 okay okay I'll come to top one have2 here H4 h right to correct only when I drag this formula it has to see what what it will do I will show you here countif of H dollar4 colon H4 comma H4 control enter control d c is pick uping is's first mobile number that is what is is's first mobile number 913 where it is 913 this first mobile number and first thing is Aman's first mobile number is's first mobile number Aman's second mobile number got my point if I change Aman here again it will take this one Aman first mobile number Aman second mobile number and third mobile number got it what I mean to say now coming to the second question to find what it is which week to increase 75% production so we doesn't know all calls and targets are there but each week agent should reach 75% but one week which is not increased to 75% so here it is see 75.6 okay reached 83% as per Target the calls okay here 8 81% here 81% but here only 66% so week six is is having only 66% so we have to find uh this one six what you say what week six so we we want to find week six right so what I'll do here it's an array function this F4 divided by this F4 okay into 100 F9 so it will give you all the percentages and one percentage is so less that is 66 easily we can find it out that this is the so less percentage so what I'll do here minan of whole thing and I will copy this one easily you can use index and match here so what I'll do here match of this whole thing comma control V comma zero so it will pick up the position it's an array function we have to use control shift and till okay six it is in sixth position so simply use index to get that position that value that is index of this F4 comma here again close parenthesis control shift we six is so less so easily you can do it 75% minus that okay understood see how much you have to increase means I'll pick up this one number one C and we need 75% so how much percentage means is equal to 75% minus of whole thing enters okay H okay control F2 okay okay okay okay okay okay okay because of bracket it is showing like this what it is okay show everything now okay okay so what are do here I'll pick up this one the max one the minimum one look up value contr c s 7 is = to 75% minus of whole thing F9 65 control shift and enter 56 is there why you doing minus only the procedure is correct only I don't know why 66 75 minus 66 okay use 75 only F9 totally this much percentage control enter control shift percentage not percentage sorry Control G this much percentage okay 8% you have to increase to get 75% approximately 8.3 like that okay now coming to the second part the third question find the employe mobile number having start see the person of the employee find the employe mobile number means employee is not attended office on that day so whoever not attended office on that day will be marked as a star so so HR should call to that person so we will send this to HR so HR will find wherever starts are there and call to that person why you are why you are not turned to office for that reason this query I will explain each and each and every query why this query is comes under M interview question right so you have to find the name of but not the name you have to find the mobile number whenever we enter any mobile number here see here it is every everyone thinking that why don't you search all things so search of star comma in this but the as that star will indicate what it indicate it will indicate the character by press it will show you all things but we need only one name which is having star so whenever use only star it will IND it will show you all the names so what you have to do here means you have to use one special wild card that is till till is programmed in such a way to pick up only the special characters wherever it finds in a value right if I press F9 it will show you only one thing and remaining are all valuators and one thing is in E position the star in eight position if you count you can get it 1 2 3 4 5 6 7 eight position start and where is that 1 2 3 4 5 6 1 2 3 4 5 6 understood so what you use is we need number so use e number e number close parenthesis and use V lookup to find it out it's really very easy true comma choose Open Bracket curly bracket 1 comma 2 close comma comma value to mobile numbers F4 close parenthesis comma 2 comma 0 control shift and Enter key that's it see you want to highlight those STS you want to highlight those Stars okay how you'll do that one simple search of the same procedure search of the till star comma in this enters okay I not closed this B so what I'll do uh I lock only the column F4 but not that row control enter right control R double click so we make it easy uh to the HR okay so easily you can you have to color it so what I'll do just I'll C this formula from here contrl C Escape highlight alt HLN shortcut key for conditional format go to the format and we have to mark it dead because the the employee was not attended office so we have to mark it in red color with the font White so easily we can find it out it should be like this and okay okay see and if the what they will do the they will filter like this they will pick up filter with color what the color red color so it will show you all the red color employees so EAS they can find it out this one what the mobile number so they will call to the employee why and they they'll inform they will ask why they did not turn up to office right and this is three questions over hi friends now this is the fourth question now what we have to find here this is very uh what you'll say it's not this much complicated just you have to use little bit of knowledge that's it okay no need to worry that it may be any right this Min this but now fourth question what is the fourth question see what you have to find star indicates to give 15% increment from concerned employee salary so find the total increment inform to hrn accounts department so we have to find the total what mean first of all I will explain you what is the meaning of St or asri see the in some cases it may be some late or the EMP employ did not attend it office or something like that in some cases the St and attendance for that reason you have to make a what you say you have to make a uh increment you have to give a increment actually the manager inform to you to give 15% increment to those person who is having stats and send this total amount how much it may be to the HR and accounts Department okay this is the thing so what you'll do here 15% increment okay so what I'll do here so first of all as previous uh second question I explained you this one right now this is the fourth question not second question third question this is the fourth question the same question search of till and star in whole Range four wherever it finds the asri it will pick up the number of the actually it's a position and whereever it is not there it will show you the value error half so three employees having what you'll say starts I mean say best performance of asri so I am interested in that so I am using interested in the numbers so I'm using e number to pick up that numbers into the salary F4 into .15 sorry okay F9 it will give you like this I'll will explain you after completing of this use some product some product or some because it's an array better use some product control enters now I explain you first thing I explain you this one the EAS number F9 so so wherever it find the as you will pick up the number and remaining are all falses so I am interested in not sorry not this one still this one okay so where it find the number it will pick up the true here what will happen here also it will multiply each and everything with 15% F to multiply from the salary from this okay multiply from this salary so what it will do it will pick up the true corresponding number that is true this one and again true this one and again true not uh total three Tru are there yes one and it will what it will do remaining are all Zer wait a minute right F9 remaining of zero so you can use some product for that understood if you have any doubt ask me in the comment box or in my telegram group okay ese now coming to this find last log out time agent after 9 hours of duration okay so we doesn't know which person logged out last we are not giving any increment to that person the company is not paying extra for to that person but he not able to complete his work in time for that reason we have to find last log out time why he uh what we have to ask him that why you have taken this much time actually the office time is 9 hours only then why you have taken more than 9 hours why you are not completed within 9 hours that work for that reason so is equal to log out minus login every maximum people know all all this one easy very easy minus but we have to multiply with 24 will it work or not just check it out all 9 hours are there okay only one person did 5 hours 1 2 3 4 1 2 3 4 5 6 7 1 2 3 4 5 6 only sh 7even okay so this is not the right procedure okay we have to get more than 9 hours but it's showing only 9 hours so what you have to do means mod of whole thing comma 1 because it may be a night shift also for that reason you have to do in this way okay and ask is there any greater than n what you do like this nine let's check it out not work remove only keep nine why all of Fales why is not greater than 9 here I already did 9.5 hours have to okay we have to be yes this is the logic minus of this F4 the procedure is correct only little bit confusion Max and down into 24 we need maximum people maximum hours worked simple okay this minus that Max because about 9 hours I'm telling above 9 hours find the last log out time he said only log out time but he did not mention who did last loged out okay for that reason 9.5 hours means 9.5 hours means 9 and half hours okay 9 and half hours contrl Z to control it's an ARR function coming to sixth part okay it's very very complicated one see question number six is as per date add prefix like th SD still desire output given below okay if it is five you have to mention fth if it is 12 you have to mention 12th 28th 14th if it is second you have to mention second if it is two you have to maintain second okay let's try it out guys look up look up day of this comma I use curly brackets one first second first second third fourth again 21st 22nd 23rd 24th 25th already there 31st okay let's check it out guys this is first time I'm doing I do don't know okay so first St comma ND comma third Rd comma fourth T comma T comma okay where we are 1 2 3 4 1 2 3 fourth okay now 21st SD again SD comma ND comma Rd R comma T comma St this will be the lookup this will be the lookup this will be the lookup value and this will be the look of vector sorry this will be look of value this is the lookup vector and this is the result Vector this is the result Vector this is look of vector and this is look of value this is a look of function okay let's trite out th okay let's TR out and add day over here again once again day of this Amper sand okay finger cross let's try this is the first time I'm applying this type of formula I doesn't know how to solve it just instantly I used this formula all right now press enter let's try it has to come yes yes yes now simply what you'll use here the lookup value to pick up after look up program in such a way to pick up the approximate match so fifth is there here day five is there here five is not there so it will pick uping four what will be the four 1 2 3 4 so 4 th okay it's approximate see here the day look of value F9 so5 so it will not pick up the fifth 1 2 3 4 so it pick up the fourth because it's an approximate after four there is no other numbers less than or equal to five that is four okay again what you it will do it will find the position of this four where it is 1 2 3 4 so it Pi up the T I hope you understood control Z and again and what I'll do text off I add text function here okay text off why double quot no double quot here sorry text off value and what he need he need month okay I'll give one space month that is three characters space and four characters of here control enter done okay and coming to 7th one it's very easy guys you have to find the nearest greater value as per this value that is 10 greater value as per this value on lesser value as for this so 10 is the max so 10 or 10 is the maximum so okay 10 the maximum so answer should be Jonas one thing and another thing is that ramu 12 right 12 and 7 are the nearest numbers okay I'm not telling nine okay try to understand I'm not telling nine I'm telling these nearest values okay sorry guys JN and Jonas okay it may what it may be so we have to find the nearest Value so I use if function if this equal to this so not this not so sorry greater than then pick up this what you need nth okay call 1 2 3 see actually okay so greater means it's a smaller so use less than here instead of greater than you have to use less than here to find the greatest value the 15 is 12th is the greatest so Min sorry Max Max greatest value control shift and enter 15 F2 I'll copy this formula contrl C and come to here equal to control V here what I'll do I use greater than here I'll use minimum control shift and enter okay one not one that should be n confus every time yes 12 nearest values 12 and 9 55 again 15 came here see correct there 15 and 12 the another method is very easy like we look up of J J not J 10 comma choose of braet 1 comma 2 close [Music] bracket okay you have to take this one first one this one comma this one close choose comma two shift enter should not come here 10 only 1A 2 yes correct 15 what 7 okay if I do 2 Comm 0 what will be the ANW where is John this minimum minimum nearest Value seven and some went wrong of this one this is the correct one the correct one guys okay coming to the eth one here what the answer is that in a below yellow color cell and a range allow only first and last name with space between in between space two words only see guys only two words and one space and that should be a proper like after space there should be a capital letter that's it so I try my level B I doesn't know this one also I'm trying now itself what I'll do is okay first of all I'll do hand function okay so first of all I I need only one space so I use length function and length of this minus of length of substitute of a space substitute of this value comma space with this one blank that's it close there so logical test is one so what I'll do I I need only one space so I'll do like this and another logical function is it should be a proper so what I use exact exact proper we are proper of this comma this close parenthesis close parthis control enters you have to do data validation for this control d if it is if the condition mats then it allows the names or everything if the condition is not met what will happen so I delete this one all e and again I highlight this one with lellow color or something like this like this one with yellow color so I highlight alt HB now I'll copy this formula from here I'll copy this formula from here control C escape the shortcut key alt DL data validations okay alt DL go to the custom and add this one and press okay if it met then it has to work if I use like this what will happen it will give you the errors if I type Excel Guru it will give you error because space is not there so what I'll do space Excel Guru then I'll try then it will not work so what I'll Excel space Guru now it will work am Guru it will not work because it's not a proper M Guru okay we'll we keep like this will not work m m Guru then it will work okay now coming to the another part nth one see you have to find the you have to count sorry you have to do some of those numbers which are appearing here as per this BDA b d here and you have to count this one total 199 are there if I change this to a BD now a b d a b d then also it has to come 199 because just I change the places see 199 if I type only a what will happen 92 yes if I do BC a then 11 44 55 147 okay now I'll will show you how to do this one made of this it's very easy very very easy mid of this row of indirect I'm just I'm separating each and every character individually indirect of from first to two length of characters simple it's very simple length of characters okay and number of each and number of character is one and separating each and every character individually F9 BCA now what I'll do here I use one function that is look up look up that is that will be a look of value and look of vector will be this one F4 that's it and use some product for this some product this and just press enter let's see it will work or not it is working actually it's an array the not it's working that's it