GROUP QUERY BY PRADEEP

Key Takeaways

this is Excel Guru rajant and here I'm going to share a solution which is asked by prep from telegram group it's very very complicated and logical too so before going to that I will like to tell you that if you have any sort of excel queries you can send me in telegram group if anybody wants to join I'm I'm mentioning group Link in description of this video now sharing the query here in the query see actually this very very logical complicated now what you have to do means first of all you have to match these names okay see different are this here if I type rames rames J nain okay then also it will mat match okay first name last name middle name are interchanged itself okay observe clearly here om prash here omash here r j na Rish Jin Rajkumar Kumar Raj so we have to pick it out if it matches you have to keep match matched okay if it is not leave it as it is okay suppose here instead of Kumar if I type Shiva then see what will happen value okay so search will not work match will not work if you want you can check it out with search of this sech of this with this then it will give you value ER because to pick up first character okay search will pick up the first character of the position so R is the position in one and here it is showing n for that reason it is showing value error done so here I'm going to share a solution see first of all F you have to separate each and every word individually okay it is in one cell you have to separate individually first thing what I'll do mid of subtitute of the delary space which already there adding this prefixing to this value in mid comma old text as we know that is space comma new text repeat space 255 times okay close parenthesis and here mid in mid function text part is over now coming to starting number okay so what are do 1 into 255 and number of characters all characters so what it will do it will show you Extra Spaces Extra Spaces with one name that is rames F9 okay so what I'll do here I will do trim to rid of Extra Spaces to removing Extra Spaces okay F9 rames if I enter two here instead of one it will show you the second word it may be a middle name or it may be a last name F9 so what I want I want three words at a time not one L two latter three three words at a time middle name first name last name so what I'll do here I'll use one function that is low of indirect and length of this close parenthesis still row now it will pick up three words with extra blank blanks okay extra blank without any spaces F9 extra blanks okay without any space so what I'll do here so many ways are there but I'm showing this way search of what I search off this comma in this it will it will pick up three words and it searching three words okay F9 will search three words okay not one word three words it will search with Extra Spaces with middle spaces like this okay control Z but we want only the numbers which are greater than one so what I'll do here I will show you if or if or okay if or let's see or comma greater then one comma no comma close parenthesis comma Now value if value two matched else not M done close par it is not a array function it is a normal function okay enter matched control D here value but not showing not matched why it's showing not matched here it has to show not matched right it is The Logical value True Value false yes here there is no false here is showing errors okay here it will show you the errors in down in this place it will show you the error neither true nor false it will show you the error for that pick uping the error see in first F9 I'll show you is showing error for that is pick uping the error okay if you want to remove error if you want to remove error then better use instead of uh this F2 if error if you want to remove this thing okay if error show me the blank enter control D got it see all Nam should mat three names should mat middle name first name last name here middle name first name last name three should match two should match if it is one one should match one and three sorry two and three then also it has to mat like this R Jan is there only R Jan here na Ram Jan is there okay it has to match right match minimum it will pick up the names okay Jan also it will work Jane Ram Jan na Jane three things na Jan okay Ram Jan okay Jane okay instead of another J we keep another gen like uh Surya J let think it will not work got it it will not work got my point okay so here I'm sharing the formula for you guys formula text here it is here is the sorry n is there where is the form here is okay here is the formula actually F2 here is the formula okay guys if you want you can check it with this formula okay bit okay I'll do one thing okay again another function what is the use of that okay here is the formula yes here is the formula okay thank you guys thanks for your support have a nice day