Extracting data as per creteria in different sheets

Key Takeaways

hi friends this is rajinikanth excel guru and one of our guy asked this query that you have to extract data as per this criteria in different sheets okay in different sheet not only one sheet different sheets okay so what i'll do here first of all uh i'll do all my tricks over here i'll zoom in first of all zoom it so that you can see the better okay this is the location as per location it should extract all these things date location id bank loan all those things you should extract right so here i will show you one click here like first of all i'll make it decided table control team my table has headers okay so converted into a table why am did like this means i want to keep the data valuation in different sheets so i'll i'll try to make it a dynamic way for that reason i converted this normal data into a table now coming to here here we'll try r dl here directly you can't uh make it okay here the formula is not visible the screen is not visible here so type indirect and f3 sorry s3 is not working here some problem no no it's not coming here we'll change this my table into alt location okay so this table name is location so i'll convert this table into here called dl in data validation and direct double quotes open the bracket double quotes location let's try okay the formula is in data validation you have to type like this indirect and the file name that is location this is the formula you have to type in a data validation so it will convert into a data validation as per this record as per this whenever you enter a new data it will automatically take updation over there in data validation so what i'll do here first of all i'll copy this all things don't see shift here here i'll keep control v these are the things i need right now first of all what you'll do you have to count how many are there as per secari okay so simply use the countif function countif i'll tell why i am doing this type of things in location okay this location the range f4 comma some number b to some number i'll change it to d1 f2 d1 d1 so totally one sikhari is there over here i'll show you why i used that one okay one shikari is there let's filter it first of all and let us check whether how many shikaris are there and select aside only one the only one is that okay now here is the thing you have to extract data as per the location so first of all what i'll do just i write a formula over here see as per location is equal to first of all we have to find how many are there if this f4 equal to in this range shift down arrow s4 okay now press f9 see some problem here okay so i have to keep that true value okay so i want one two three four five six like that so what i'll do i use the row function to get all the one two how many are omega totally f4 minus rho of a2 f4 close parenthesis a2 f4 come outside of this plus one not this a2 this to wait time a little copy this one reference control c here i will change it to the data and i will delete this one only this part already plus one i don't need false close parentheses f9 see wherever it finds the data to show up the true and remaining or all pulses the security one two three four five where it is here 22 position 22. here i think you can able to see 22. i'll zoom ctrl shift enter i'll zoom a little bit f2 now again i will show you first of all logical text f9 sorry logical test f9 and here this is the thing value true see the position is 22nd position here it is the true f9 c if it is true it will match it will pick up the 22 and i i like whole thing f9 that will pick up the 22 so we got the position so now we have to extract that position in this data the value the 22nd position value you have to accept in this date format okay everything is logged properly now f2 what i'll do small and i want if more than one i know i want one two three four five six seven like that so i'll keep the formula like this rows of a dollar for a dollar sport a4 so when i drag this formula it will pick up one two three four but the smallest to second smallest or smallest like that it will do it ctrl shift enter and you may will show you the errors because only one position is there and you want to change this to somebody see it was important i'll show you how to do this one first of all we will go to this now now we got position everything we got now we want index and index is the main thing that which range you have to get it the eat and everything that it from the uh menu index of what i'll do here simply watch carefully don't keep f4 i'm blocking the row but not the column okay i'm locked i'll show you because here i think it is not visible to you properly f2 see i'm locking the row but not the column why because when i drag this formula it has to pick up the location range from the master sheet that duplicate location range from the master sheet for that reason i have not logged in column when i drag this formula see what will happen it will show you f2 see b2 so it will be coming up the same thing when i drag formula still here it will pick up all the positions y zero because there is a blank over there and why what i said why i use the count over here i will show you wait a minute i'll convert this into normal date by pressing ctrl shift at the rate sorry hash for normal date okay converting the serial number into normal data now what i'll do f2 here what i'll do i'll copy the rows formula from this this is the k value control c escape if control v rose is greater than discount if it is greater than discount then give me the blank i'll run this but formula shift enters i drag this formula as much as i can on that drag sideways also so you know this is the thing okay why because you are something to make it like this that everything is perfect now here the thing you have to make it right what is what he said he want to extract each and every location in different sheets so what i'll just copy this heat from i'll type this one and another method is there that is very big method so i am showing the small method just copy the sheet from here see i'm clicking the left mouse and click on the control so it will show you the place else it will show you the blank paper when i press plus to show you the copy symbol that is copying copying the sheet to another sheet that is here so copied here what i'll do is change convert to right double click here same m a n d r e by a l a method copying the sheet and change over here okay double click f i k r a i s i k r e i see how many you want you can drag it to copy it and paste it here it is zero actually so what i'll do control shift tilde because bank loan bank loan number is not available here see what that is showing zero all right you can copy this sheet from sidebar and data validation is also copied from one sheet to another sheet same just click on that change this one to zen wall double click rd and wl involved enter now and make sure that header should be always capital so that it will understand that it is having uh headers for the range if it is not it will not understand now what i'll do i'll convert this into a table see what will happen if i convert this into a table control theme enters so converted into a table it's a dynamic now very much dynamic that i will add a new location i will add a new location new date location is some hyderabad some numbers some numbers name charge 1000 tenure 12 received 140 something like that now added a new location that is hyderabad now let's try here is it there okay okay it's not there see it's not there why because first i converted this into a table not this one if you convert first to this one as a table then it will add hyderabad otherwise it will not add a hydrobot hydrobot either but okay now let me check new sheet the copying sheet go to hyderabad see one by num location spelling spelling mistake is there for that reason no location is dead either but let me accept what's wrong here wait a minute let me check i don't the range has been not taken properly for the planet is not accepting hyderabad right the range is not accepted properly hydria there also last one hye but it is taken only wait till uh 25 convert this into 26 convert this into 26 so what you have to do you have to first convert the table into normal data into a range i mean sorry the table then it will work see okay thank you guys thanks for your support and if you have any doubts please let me know in the comment box