MIS INPHOSIS - 6(23-25)

Key Takeaways

hi guys this is rajinikanth excel guru this is the query one of the query for 50 questions asked by one of the reputed company so we have to find the solution for that and this query can be done from advanced excel but they are they said to uh they do with the excel 2010 version so let's try this one so what is the query see here the products and the customer buy these these products so you have to find the total how many are there you have to find the total price for that see here two product id means he you buy these two things so you have to find it out from this what are the two things of productivity compared to this one the price so here we will do first of all i'll hide this one minus height see how i'll do first of all what you'll do you have to search of these product ids f4 comma in this f9 to check whether it is there or not see one only one are there another one should be there where it is for the two things should be there and only one is there maybe somewhere it is okay we'll do one thing we'll make it such ease number i have nine one true is there where is another true two things should be there two only one two is there i don't know why it is showing one true it has to show once a can't i did at the very first minute i did it a little bit this one comma in this product i have four five see all our falses are there there is no true or true okay let it be this is the correct one so [Music] here it is and just to multiply with product f4 and f9 only 13207 is coming i don't know it has to come two things but i don't know why it is showing one because ah two five one zero one four nine eight it is not that maybe and just do some product some product of this and close parenthesis control enter [Applause] some products you know white is not showing properly all to you right see it is there i don't know why all are right see already over here i will show you unhide see all are correct i think this product is not available here two five one zero one two five one zero one four nine zero not yet i think so yes yes this product is not available over here for that tonight is not pick upping that one so what i'll do i'll add this product let me see i'll add this product two five one zero one four double t i'm not just showing the same thing yeah yeah it is changed it is changed to double one to one foot here is showing the same thing two five one zero [Music] yes now it has changed okay two five one zero one four double two and coming to here this is the very very important thing guys very very important and that is a simple thing but these two things are very very important you have to keep a concentration on this that you have to infect one space between first and last name right you have to interrupt space between first and last names what is the way first of all you have to find the capital k the position of capital k right so how you do that one first of all i want to extract each and every letter from a name like made of this comma rho of indirect ampersand length of this close balance the closed parenthesis and each and every character so it will extract each and every character from this r a j a n a but we are interested only in the second capital letter not in the first capital letter so what i'll do here is just a little bit logic that i will convert this into upper cases total everything every every every character in this value should convert into upper cases and what are we just copy this one exact i want to compare each and every letter character in this f9 wherever it finds the capital letter it will show you the true and we are interested in the second two note in the first true the the second second la logical truth now what i'll do i use the large function large if is anything called then i want one two three four five how you'll do in a text in a row we can do low voltage minus row of the first cell plus one but here you have to do in this way control c control v i'll show you what will happen if i will do like this i'm explaining each and everything f9 it will show you all the characters it will take from starting to end of the character that is it will count how many are there from first to last of the characters so total eleven are there this is the true close pattern i don't need the false and i need the first largest f9 so it's showing seven is it right one two three four five six seven yes the seventh character but i want to insert a space between first and last name so here is the very very logical thing you have to do that replace of this name comma and starting number what will be the starting number then this starting number will be this one only comma number of characters how many number of characters do you need if i press one it will pick up the first character okay it will replace the first character if i press two it will replace two characters but what i'll do i'll do zero to insert a space over there f9 see this is the logic if you have any doubts ask me in the comment box i'm ready to answer those things i think everyone understood that it's an effective control enters it is showing a wrong answer you have to use ctrl shift and enter the genie space can't gama space krishna your face is not there yes this is this here space is not this yes this is this if you have doubt ask me for number of characters in a replace you have to use zero to insert a new character do you understand what i mean to say suppose if i enter any one here f9 3 k has gone from 7th character right if i did 2 f9 see two characters are gone but when i use zero over here f9 no characters are removed and inserted a new character that is spaced between the two words or first name or last name so this is the logical thing okay now coming to the second part see you have to insert you have to insert a new code like this pro hyphen abc hyphen 234 iphone abc you have to do if it is not available you can keep it blank but if it is there you have to extract that thing right so first of all what i'll do i'll use the vlookup see how i'll do we look up left off left of this how many characters we don't know how many characters maybe two or three or four or five so what i'll do find hyphen in this right close parenthesis comma table array this one is the table array ctrl shift right arrow up arrow down f4 comma two comma zero right control enter ctrl script enters why what happened why it is not there pro one is there so let me check whether where it went wrong f9 see one is not various so i kept a hyphen over this find the left of minus 1 let's have minus 1 over here and f 9 value first of all what i'll do left of this comma find this comma hyphen f9 values so what i'll do here and [Music] this text in this so what will happen f9 pro 1 so you have to be uh you have to remove the dash from here so minus 1 f9 now use vlookup over here vlookup of this f4 sorry not this one it's not a lookup value this will be the lookup value and then table array is this thing ctrl shift right arrow up arrow f4 comma two comma zero control entries double click where it finds this code it is came but we have to get like this we have to insert a this thing the new code for the product so it's very very important so what i'll use here i'll use a replace function again over here replace of this thing comma and starting number will be we look upon not vlookup starting numbers nothing how many characters starting numbers old text wait a minute here i got little bit old text starting number number of characters zero and new text will be blank i think i did wrong here somewhere i went wrong should we not be escaped i didn't thing right you please find this one and we look up replace of f to this thing number of characters find of this thing for you know this thing hyphen of this thing comma all text starting number wherever it finds it has to be hyphen over there number of characters is zero it should be zero number of characters should be zero comma and this is this will be the new text f9 see here everything is done except hyphen after product one hyphen after product one so simply what i'll do i'll go to vlookup function and i will add hyphen over here that's it f9 and closely ctrl enter double click i have to if errors if you find any error in this function to give me the blank i'll run this formula as it is okay and i request everyone to like share and subscribe this channel and these are just so many 50 total 50 questions are there till now i completed only i think 20 or 22. 20 are completed and mode for 38 28 are there as soon as i do i will finish it as early as possible thank you guys for your support and please like share and subscribe