MIS INPHOSIS -5(19-21)

Key Takeaways

hi friends this is excel guru the ginikant and here i am going to actually this is the company i said already i informed you this is the company where it's having 50 questions so i'm uploading step by step step by step in some videos few few queries in some income videos few solutions for the activities so totally 50 now i'm going to explain you the question number 19 20 and 21 three questions right so i'll have to ask you how is it possible okay how is it possible how to get the names as per this so everyone is thinking that why don't we use normal vlookup okay we'll try that one also that is normal vlookup right see is equal to we look up we look up this one comma and this one comma two comma second column comma zero enter why what is the reason is this extra space inside there no it's not having any extra spaces right it's not having any extras just a minute okay i'm taking is it recording or not so what you'll do here first of all here it is wrap text means it is so it is taking extra line a new line okay a new line so there will be ascii characters to 55 right in that here they use the extra line that is grab text see that is wrap text so what will be the extra text number how can we find that so what i'll do alt enter sorry f2 alt enter this is the extra line enter and i use this function called a core function where i can get this code numbers 10. so i use this code number over in this formula see how i'll do here substitute of the old text will be the character sorry code 10 code of 10 and with blank i want to remove that extra spaces from here right extra new line not a space story is extra new line and i press enter f2 control shift enter now see the magic why it's happening so what i used i inserted space over here and replaced with the code here here new line is this here space is there i am removing this space and replacing this space with a new line so it will show you as it is here f9 c but here it is extra line enters what happened it is right only what happened substitute of space yes with cotton yes wait a minute f9 49 okay i cannot quote any captain sorry it's character character 10 sorry guys sorry for that okay i played only enter not a ctrl shift enter it's normal enter if i change this name to the ginikan 15 right i replace the new line and replace the space in new line okay so it will consider there's a new line over here f2 let me tap f9 to show you as it is but it's a new line okay if you have any doubt ask me sorry for uh this thing and here desired output is like this everyone is thinking why don't you concatenate okay we'll try concatenate over here i will try concatenate over here is equal to this ampersand space ampersand this is this right answer let me check convert this into type [Applause] see it's not changing control one will try in another method also control one time is there so i use custom for your confirmation h colon mm see no it's not working so what i'll use here before going to this i will explain you this i'll convert this into general format by using ctrl shift delay so this is the general format from here date will consider as decimals like this and sorry time will consider decimals like this and get a serial number like this okay so what i did here see observe carefully suppose okay i uh first of all what i'll do just i'll copy over here from here and i'll paste it here and what i'll do see wait a minute okay what i'll do first of all i'll convert this into general control shift tilde and i will add these both things okay now this is the thing lesser decimals are there sorry after five automatically it will take the nearest number of eight that is more than eight that is nine so it is showing 27486.9 now i will convert this into time now control only this thing is showing so we will convert like this control one time where it is date and time we have to use date and time over here so custom remove whole thing just to come wait a minute okay i have to remove am pm over here sorry dd column not colon dash mm dash y y y y 4 y space okay so i'll convert this into normal by date format um general what i'll do is i'll use date format over here that is date short date the year and here here i've used time format time t i type this is the thing i used over here is equal to this plus this here is not yet formatted so what i'll use i'll go to here format painter and i'll click over here and i will test it right i hope you are understood and if you have any doubts ask me in the comment box double click right guys and coming to the third one what i said you have to find the duplicate which number which name or a number is having duplicate so here raju ravi kiran ramo and all names are there so we have to find which name is having duplicate name so simple thing that i will use countif here see by using vlookup without helper so we look up this one contain this one comma and the criteria will also be the same range expecting single criteria but i am giving a range so it considers an array f9 see here raju is occurring two times so it is showing two and again this radio over here that is two and remaining are all one so what i am interested i am interested in two not one so what i'll do is greater than is anything greater than one right now now what i'll use here i'll use cues function one comma two flower bracket comma and this is the value sorry this is index these are just index means i want both things if you will enter one it will pick up one but i entered this array constraint means it is having two things i need two things to pick up this is the value one and what will be the value two the value two will be the same thing closed parenthesis now i will show you how it occurs see true raju false ravi false kiran false ramu falsita false meena true raju false excel okay so here it's a cable format so it can it will convert into a table format that is f9 all truths are in one one row and why are you in another row it will be like this see i will show you i'll press enter [Applause] suppose true a year only i'll show you so that you can understand better true false false again here too sorry guys here's some okay sorry for that right you're also false f2 false you're also false you're also false you are also false right so it will it conjure like this so what i did here i have to home vlookup i want to pick up this true means i want to pick up like this the table converted in like this and i want to pick up the true means it has to take this one comma 2 comma 0 no it's an array function you have to use ctrl shift and enter that's it for now thank you guys thanks for your support and if you have any doubts please let me know in the comment box thank you guys