JUNE REAL MIS -6

Key Takeaways

hi friends here this is the quy As Good by one of our friend in comment box so first of all I will explain you the query later I will show you this solution what he's saying he's saying we need a data validation where where we can keep a unique values okay where where we keep a unique values whenever we select any item from here automatically a duplicates C cost a should extract okay if it is C only one it won't be here okay only one that should be dyam okay whenever we enter any new value here or here automatically data Val to update okay so what I'll do here simply what I do I Del this one and I just I named one file in nam range a header okay now what first of all I'll explain you step by step keep concentration on this here is a duplic case are there so you have to find the unique values from here so there is one function where it can help us to extract unique items but one problem is that it will uh it will it will know only the numbers but not the letters here the characters are there that text characters are there but that function will understand only the number that number is nothing but free seey will understand only the numbers but here EXT there so you have to convert this Che numbers into a number and find it out uni see how I'll do frequency frequency if I don't need any BL I for is not equal to blank it may be any blank I don't need the blank then what you want if it is not blanks then what you want I want to match look up value but here look value is not there okay and that to match is expecting a single value but I will make it array by adding a whole range match comma the same look up Z okay and if it is that is true and what will be the bins next first of all I will extract the it won't accept so I will do AR f9c car toy three double doubl are car here car here car here car here car second one is toy and third one is tool here fourth one is 1 2 3 repeating this many times one R is one and 1 2 3 4 1 2 3 4 again c one two Okay show you if it duplicate pick up that number only okay it duplicate one and again one again one see and this number are this number to last number to two and again two two again two and to to count two is in third position another tool is not there here it is there 1 2 3 3 4 5 6 1 2 3 4 5 6 how many TOS are there actually one and two no more TOS are there okay so it will pick up the unique values F9 what it will pick up first of all it will pick up one the number which is repeating more than one time will pick up that numbers only like one one is repeating two is repeating and four is repeating okay this are the of all it will pick up the repeating numbers later it will check which is not repeating to a uni numbers control Z now I'll convert this into bin for p I'll pick up this one F4 minus of this one F4 + one why I took this one because I need 1 2 3 4 5 if I highlight only this part F9 from starting three 3 4 5 6 7 okay so what I did I minused from one row F9 it is pick uping 0 1 2 3 I need one two three so I added one last f9c okay now what I'll use small if logical text all logical text whereever it it finds the number greater than zero it should conu as a true okay just I'll copy this one if it is true contr Z the same formula and comma 1 because I I need to pick up first smallest okay last it has to pick up the second smallest third smallest like that just for for your clarification I'm showing you the one F9 it will pick up the first smallest if I do the two it will pick up the second smallest if I place in place of two if I pick up three pick up the third F9 thir see I highlight the whole small first of all to show you the formula f9c smallest 1 2 3 4 5 6 7 okay if I type seven it will pick up the second smallest seventh smallest F9 s smallest is not there third fourth five just to show you if smallest is seventh number okay sorry guys now what I'll do it's an array function in of doing all those things I'll show you every time I don't want to changeing the K value what I'll do H3 col H3 enter so it will become one TR formula will become 1 2 3 4 5 6 7 this formula I will use here in place of this F 3 col F3 okay the r r r of F3 F3 shift and enter it's an array function now it will show us the numbers only 1 2 3 4 7 1 2 3 4 and 7 see I'll convert this into index index of F2 F4 comma so it will become now it will become the row number as pick up the first row number sh and enter now it's not a dynamic the formula is not yet over okay the formula is not yet over I TR formula a little bit but I what you mean I should make this as a dynamic and I should keep this in data validation okay first of all what I'll do I convert this into a table of all what I'll do F2 to rid of Errors everyone know if error if error if you find any error in this function give me the blank double click now I'll convert this into a table okay I'll con this into a table I play somewhere else in this range control T so it will pick up all this and here it's Shing my table as header yes this is the header okay this is the header I'm making this a dynamic okay it's starting from F2 sorry F2 and ended with F15 okay you can see this dancing not dancing it's around line okay now so now this is the table but it is having some name we have to give some name to this okay otherwise it won't understand it will understand but we'll get confused you will have more than table it will give you table one table two Table Three like that so for that what you'll do there is a shortcut key Alt J T to dump into the name of a table so here you can give extract extracted extracted okay that's it that I have given extracted okay now where I have to make data valuation I'll delete this one here I'll make a data Val for that okay alt DL list of what F3 because not we have given some name extracted right so here is the function in direct double put it's a table okay extracted okay let's check whether it's working or not indirect extracted okay now it's okay blanks are there so here the blanks so can't remove blanks because here there is a Formula there is a formula for that I don't want to remove blanks okay now coming to the the table part okay now here this part is over now now we make a data valuation now okay now just to make you understand that all are there see car toy tool Sho tool all are unique are there see so I I I will make this also a table okay because whenever this will update automatically this will update when this will update automatically data validation will also be update okay so I place here control team enter now I alt JT alt JTA main data sorry guys me this is the main data now I will enter a new record I enter a new record so that it should update this one should update and this one also should update automatically so this is the dynamic and so many ways are there but the person asking not using unique and solt function for that reason I'm using 2007 function it will work in 2007 also now I'll use a tab uh then new one which is not in the above in this list which is not in the above list okay just to show you Shir okay here also updated now we'll check here it updated or not sh okay with one short three birs with one short three bir this is a one word this is another word and this another bir one short three birs okay I want Z this one yeah now coming to this this one now and we have to extract the tool this tool see see I want to C I'll show you what I I need to extract only a should come here only T that's it whenever ENT datation from here okay now see the m of formulas what same formul little bit different small if anything what I'll do there is F3 no there no F3 what name main data main data is there main data is there and I need what you need you need items close parth now it will become the color Okay equal to to f for locking from all side if it is true then row of just to show you not this much okay because I take that one minus row of this out from here guys F4 one yes now the K part will be go it is dynamic guys for that reason it is having a table names next in some other video I show you how to we don't want table name but it should work dally don't want the table name but it should work dynamically okay I'll show you that one also try to complete that one also if it is possible because I'm having so many works now a ro I doar to col I where it finds the data the car will be it will show you the true see here logical first of all I show you logical see where you find all of to is there tool is there tool here tool is there so what it will become it will pick up the position of the true from here F9 F9 what happened I this wrong here why a comma here be no comma here wa a minute I'll do it manually so that it't won't get any problem so what I'll do I will drag till end F4 what I'll do I'll type only where last one 12 okay back space two will be number now I try to complete it is true logical test F9 what happened why you showing error here okay I'm not close to this one remove this black now so where you find the true it will pick up that number and it will do just now I said the Rose Part f97 shift and enter just I want to show you just drag like this give you some errors only two only two is only one [Music] I go to end and check everything is right wrong F to in of main data what I'll do logical test what I do I start from here same like this it's C32 like this f4a C2 dynamically because I don't want to continue with table nor culture nor culture where Exel will give you some name to the table P2 okay now I'll show you index part index of what you need you need this one same C3 to doing like this D3 to D2 it won't give you table nor will pick up that one for that we'll get some problem again we will not understand properly is having be F9 value error small if small if here the main thing equal to Tool F4 F9 P so tool will become just pick up this P another P also there here see let's test out some problem with this let's out control shift enter double click pick uping only one I don't know why pick up only one should pick up another P also it's pick uping only one when it is having maybe here maybe space will be there see space is there enter okay try to analyze the data whenever you get an error it may be a special character like space car 120 car 150 like it will be there analyze the data first of all analyze the data why it coming the error why not finding the particular value from a data if it is available also now coming to this part now I I want to show you the dynamic way okay now what I'll do I'll type tool here sorry tool here tool zero nothing is there F not fq what I'll do I'll type h from what any anything what it may be now go to set is updated here and here also set will be update here also Z will come and another thing guys like to tell you why don't you make it a dynamic so that we can why don't you do the condition format to highlight the particular cell so that whenever it will come automatically it can be visible to us easily okay so what I'll do here just all right I'll copy this one I'll Del l contr c contr v I'll delete this one what I'll do is equal to this what I'll do I'll Lo the row but the column okay it should sorry I like the column to Mo no no she should not move okay is equal to this locking from all s whenever it find the true the same data see we drag formula till the color is there so here is the true yes only how many are there only Z is there so what I'll do just copy this formula from here conditional format I explaining you if you have any doubt ask me in the comment box alt HL shortcut key for conditional format just do it like this go to format make it fill with purple border with no border font will be The Bold and it will be the white so it can visible easily like this okay okay so what will happen if I do like this whenever we change this data validation from over here automatically it will pick up the particular item or a value from here and it will highlight those values or those items in a table tool see only tool items are highlighted easily can easily we can justify that is it right cpq cpq right guys you have any doubt ask me in the comment box thank you thanks okay no actually I hide the column which is in pan numbers so it's uh it's not a right method to reveal others span numbers that in social networks for that reason I hided this pan numbers and now gam you tell me is it possible is it possible to get only the tdus amount as per two criteria that is name and month so how you'll do in as per your knowledge how you'll do goam are you busy if you're busy I will no sir I I didn't get the question actually listen actually there are names and month here also names and months here like this okay names and months and he wants to find only the TDS amount should be a month-wise like it is April sisha ail so it has to get 40,000 a here you have to get 40,000 how is it possible 40,000 as per uh month as per name and month as per name and month and month here it is because I don't want to share file also in groups because it's official one just for that reason I'm showing you only this thing we look up will not execute this one no how can you say we look up will work over here what way I'm saying we lookup cannot do this I yes we lookup cannot do this right can we use some product okay but okay we'll try some product after that I'll show you another method okay we'll try some product as you said is equal to this I'm locking the column equal to the whole table F4 as you it okay again it is selected this one M I'll do like this reverse order better F4 equal to this one I'm removing the sheet reference right and later I will do with do with like this anything month equal to April equal to a I'm bling the row but not the column the sheet one that is highlighted it is in different sheet it will highlight but it is in different sheet okay sir okay it will highlight ically but we can't able to see that's it okay sir and another one which is the TDS amount F4 come back to the same Feld close paresis wait a minute I'll this one close parenthesis and I highlight and I will [Music] show where it is 40,000 is not there let's do some product then we will come to know I'll do some product here contr shift enter c what I'll do here I will make it here plus okay because it's an or not and gen is not working sir how about prise th will not work because we are here the duplicates are there okay sir okay wait a minute the sheet reference is not properly it is showing sheet two but it has to it is sheet one F4 I'll remove this one and I'll use this one and I remove extra brackets for this I loged everywhere then why showing zero okay here I did not loged it where here I not Lo it uh B yes doing another amount I think so it's doing all the SU F2 let me check February this one okay let me check [Music] February all things andove Place only February got it goam 11 16443 correct only correct only what I me to say correct only what I said correct whatever you did okay but here another thing is there like September here it is there so we'll check September is there or not for the do sish for month of September 40,000 is this here it is showing zero why so so maybe reference not go ahead go ahead go ahead go ahead I think maybe there is some error in reference no so is miscalculated no no no no no no not like that think think maybe it has uh same data I mean like duplicate values so that it cannot no no no if it duplicate it will give you the another amount but not the zero so maybe didn't found the data we mentioned I mean what yes it did not find it why because we have in this scenario if it is there then also if it is not finding then we have to check is there any Extra Spaces over the names and the months in the month it is not there see here it may be let me check also not there here F2 here also not there and last thing here we have to check where is September F2 there is extra space in the month okay in this type of scenarios you have to do like this okay and another method I will show you only when when it is there a numbers if it is not a numbers to extract suppose it is having some other values but not the numbers if it is numbers we can easily find it out by using some product but if it is not a number some something else is there then how you'll do that one example I'll show you wait a minute if it is the names like this name one enter yes now is it work with the names if we take the names yes go ahead no sir since it is multiplying it will show zero yes yes some product some yes but some product will it will show you not zero it will show you some other errors like value error okay sir okay it's a value so it is showing us some there is some error in value for that it will show you the value error so for this reason what we have to do means there is a function called index for some product it is right whatever you said it is right but if it is names okay what I'll do just I'll copy over this here contr C and I play in another sheet like here okay I'm removing I'm I'm removing this whole data and we are finding in that it will it will find the numbers as well as the names in this scenario in this formula that is called index okay is equal to index of what it is one is there what we need we need names simply to to make you understand I'm using this one okay okay comma and what it is match of you have to concatenate two things here yes sir match of name and name first we'll take this one F4 locking for the what you say this one yes % and month the column yes comma I don't know why it is showing the big size I don't know why it is showing okay so concatenate these two things and I'll remove the sheet unnecessarily it will mess up all the things right and just go to there first of all what we did this one the names one so add names F4 Amper hand now the months whole range we have taken okay whole range we have taken F4 comma 0 double brackets and it's an array function control shift and enter NES means it's not founded okay okay it's not found it n it's not there the February of the name of something okay so we'll change this to now we'll change this index part to that's for numbers so that you'll able to understand this one easily now we took here we took only one range if it is whole table definitely you have to make it match for row and match for column but I took only the range not the whole table for that reason then I you do only one match okay sir okay but here it it won't show you the uh zero it will show you na wherever it's not there so we will find it this one in February whose name is this in February whose name is this C in February month let's check it whether it's wrong or not like not there not there for that is showing this one okay there not there okay understood go yes sir and please yes go ahead what I'm asking is can you could you highlight match formula line I mean how it is working or can you do show me in okay okay okay okay okay first of all I highight this thing this concatenate part okay only concatenate part I'm using F9 I'll make it big so that easily you can able to watch little bit all right F2 now I'll highlight only the concatenate part F9 okay it will contact the both it will concatenate both things name and month now here I'll concatenate the whole r wait a minute whole range F9 okay it's having more than 8192 characters so it's not showing that one so it will concatenate the both things the ranges see how it will concat I will show you example I'll take a little little one like this so maybe you can show it in a formula tab formula evaluate formula then also it will uh okay I'll try my level best to show you if it is visible because in telegram the extra dialog box it won't be visible let's try one okay let's that's what alt NV oh alt alt T UF all T UF are you able to see now but I can't zoom it okay sir okay okay first of all what it has taken it has taken this two concatenated parts yes sir April okay sir and it joined both things without any extra spaces between the concatenate we are not using any extra spaces between the two values that is name and a month okay sir and here what it is showing it is showing adding both ranges okay sir are you able to see that one yes sir it's adding both things the name as well as month here you can able to see this one yes sir first match yes so it is pick uping that one one so index will pick up the first row and and it will give you the value okay thank you so much thank you so much for asking this this elaborate thing because direct directly I went there I did not show you anything but you asked because so many people is having the same doubt so I think there will be certain limitations for conas inating data I mean for lookup fetching that values I think yes yes no no I think so it's not there I mean like in what way you can you can you tell me in what way you are telling that one I think any example is there any limitations I mean like when you join both arrays I mean like you are joining both data site in ARR for match function I mean like can it work in all type of scenarios or it will work in only like all type of scenarios like the like this also you can do the same thing I'll do the same thing but instead of match what I'll do in so many ways are there instead of match what I just I'm removing this one what I'll do just I will keep equal sign and concatenate part is there I'll remove this one also I'll use equals uh instead of comma I use equal sign here also it will work here Al it will work it will concatenate these two things and it will concatenate these two things so it will show you the true so what I'll do here match of true whereever whereever it finds the true to give me that value okay sir but here is a array function so in case imagine we are giving index an array instead of an entire column or entire row still it will work can you repeat one again slowly I mean imagine in index first argument we are giving an array instead of an entire row or specifically enti column can it still work or it will have to it will work it will work it will work because actually for entire it will not work you mean to say you want to select the whole table right in instead of taking only one range why don't we take the whole table yes sir no no no not not here but in case in a real [Music] life if it is February over here instead of row if it is in columns like this instead of here if it is here then you can use index of two sides suppose index of here it is there I'll keep here okay first of all I'll remove this filter function what I'll do instead of ail I'll use April over here somewhere else not there in that data somewhere else okay then in this scenario if you want to find anything which is the it should match the name and month suppose here it is the go is there suppose example so if you want to find the gum then you have to use index of the whole table still goam F4 comma here we have to find the two things one thing is Dr sisha and another thing is April match of this comma here only I'm taking you can take in another table also okay comma zero and here is the Row part is over now the column column part is match of April that's F4 comma go to somewhere else or else you can select this one also if it is another sheet you have to select over there comma Z now it has become the normal function it's not an array function simply press enter if it is if you finding the both ways like a row and a column the intersection of the row and the column suppose it is there uh C3 and H3 uh here it is wait a minute so H3 th this thing and this thing this thing I think you should control I think maybe it is this one H yes yes yes wait a minute I'll color it okay it will take all colors only this part is colored first of all I'll make it this color this one and this one the intersection in both column as well as row if it but but we are taking only one range but not the whole table just we need only one range okay sir right only one range for that reason you have to use match and con CET Parts if it is numbers okay some product will work but it if it is a text like this then you have to use index and match okay okay any doubts ask yes go ahead I have doubt but this is not regarding I mean this query this actually condition for sir I have some okay go ahead I will try my level best to solve it sir uh uh can we condition format I mean I have to want to set a condition for a cell that is having a text and when I edit the text it has to highlight I did the text means I did not get you so imagine the cell C3 [Music] Dr I want to highlight it in case it was edited if some some edits it has to change automatically that it has tolight yes I got your point is it possible [Music] means if someone will edit this we have to know that it's some something wrong went over here yes sir right yes yes yes okay I'll try this one what we it will work because if it is in whole range if it is only one thing we can mention it like if this is equal to this only then we can do it but where when where is the whole table okay we'll do one thing one thing we'll do we'll take one thing like first of all you what you'll do means find it take it out one control C I'll paste over here control V and I remove duplicates duplicates are there in data remove duplicates continue with current selection yes here we can do see how I do see okay it is a way it is a way very good what I'll do I'll match with this okay I'll match is there anything with this name F4 comma 0 so it will give you the position okay we'll make it here because there we have to do conditional format so we have to do here like this match of not match inste of match we will take directly what is the because the duplicates are there F4 I'll make it and because is anything should correct if it is 1 equal to 1 okay if one is equal to a range if you'll type like this it will pick up true true and falses but it will not pick up the exact which one is true for that reason you have to use and function to evaluate only one false why showing enter sorry control enter why is showing false why value I think more than one argument is required sir for end yes but you have no no no no no no it's it will work it will work it's not a problem it will work I think Extra Spaces let me check by collecting once again F9 okay actually you have to use or not and because each and everything should be true then only it will pick up the true okay sir because truth and falses are there for that reason if anything true see all are TRS because all are there what I'll do here I will make it this selection control C I'll select only this range alt HLN I don't know why this dialog box will not visible to you if everything is visible it should be visible this one also what I'll do I'll format this one with some other color that is purple or yellow something wait a minute all right right okay okay now I will change this one I'll change this one okay with other name someone will change change this name with go or edited some name okay we think that someone is edited instead of O he typed zero yes sir then also it will not work if someone will change anything because we have given the condition that it should be true if it is not true then pick up the H sorry show me another colors okay understood any doubts in this or we will try that okay we'll try that it should be another color other than blue if someone changes it should not come white like this it should come another color which with other than white okay that's [Music] better one condition is there okay so what I'll do here here only what I'll do I'll use not not function it should not true because that one the conditional format will understand only the true so we are making false as a true converting false as a true so it will become sorry it's an array function true so what I'll do here same procedure I'll I'll copy this one contrl C escape and again I'll select the same range alt [Music] HLN go to the cell format here it is and format purple color here I will do changes with suppose if I control Z okay control y I forgot the name okay copy paste it contrl c contrl v it's not working so we will make to work here alt OD alt OD okay conditional format only are you able to visible this one not now no sir conditional formatting I'm not getting to feel okay I'll make it will come down the purple the blue color will come down so we'll change it to upside the blue color will the two things will come first one is purple which we did whenever it is a wrong then show me the purple color are you able to understand goam if it is wrong if someone edits something yes sir then it should it should become the purple color else it should be in blue color only so in conditional format dialog box what it will show you means it will show the reverse first it will show you the purple then it will show you the blue color so what I'll do I will make it purple as a a one and sorry blue as a one and purple as a two okay sir by default it will show you the purple as a one okay you understand the mistake the mistake it will show you the default thing whatever we we edit it will show you the one so what I'll do whatever it is there first of all see that one first of all see that one if it is okay then okay if someone edits then it will convert to purple okay sir let's try this one I did not select it I think so wait a minute not selected this one R Al right right only right only right only right only right only yes we'll make it this one first of all let's check it out it has to change actually that is the method okay I will make it this one the blue one also not changing some somewhere wrong I think I control C okay the thing is that if you want to remove a conditional format if you copy some other value from somewhere else and paste it in the same place here then it will remove the conditional format so that's the thing happen over here again we have to do a conditional format if anything ped from other value to here got it gotam that's the thing I did what I'll do here just I'll type as it is it has to come actually okay I'll take it out this one okay I'll check it out this one okay okay okay thank you goam thank you so much for your queries thank you sir so next time I think we should try Gmail me sir sir it is hard to understand for in case that conditional formatting or other dialog box is not a okay okay zoom zoom or something Gmail meet or Zoom I think we should listen listen it is there option is there not like that but what the reason what the mistake is we are unable to record that one okay only live session is there recording session is not there I think in Zoom it is possible sir for recording but I don't know about Gmail meet exactly okay uh okay I'll try in Zoom okay as you said I try in Zoom okay by next meeting I will make it that one only let me study about the first of all Zoom if it is okay I will make it that one okay sir I will also search about Gmail me I mean can we record or not