Extracting data in a single column

Key Takeaways

hi friends welcome to excel Guru rajinikanth here I am going to teach you some amazing things that is my mode is not proper I don't know why it's happening to me okay lift off okay so here I'm going to explain you two things and after solving these two queries I'll give you one solution you have to find I'll give you one query you have to find the solution for that right now one of the guy asked this community to extract data like this like this thing after 57 automatically 34 should come and again it should repeat like this after 69 32 has to come after 69 okay 69 really 69 mm-hmm 56 39 13 after 13 where it is 32 see okay that it has to extract like this is not proper so I am maximum I'll try to use with keyboard only so first of all it has to extract this one later it had to start from here and I think it has to start from here and after it has to start from here means what you have to do this for that so here I'm going to show you see here what I actually the condition is see here first I'll Zoom a little bit first row First Column second row First Column third row First Column fourth row First Column fifth row First Column sixth row First Column seventh row First Column eight zero First Column after coming here what will happen second row second row yes second row first uh second column second row of column like this first row second column first uh second row second column like this so here you have to column is second column and row is one one two Row one column two column two the column is stable it has to repeat eight times it has to repeat eight times right so here I am going to show you some amazing thing you have to keep concentration on this so first of all we will extract the row one two three four five six seven eight okay here here it is okay so what I'll do first of all I'll use the rows function that is J dollar foreign right so it is not a proper one so don't use this one okay because it will increment numbers but we need still eight only not the whole numbers we need still eight only after it in place of one in place of nine it has to come one two three four five six like this so what I'll do here instead of that the total Eighth Avenue so what I'll do I'll use the mode function comma we have to divisible by eight because eight rows are there see what will happen troll d control backspace see in place of zero in case of 80 it is coming zero so here 8 should come and here 0 has to come here eight and here 0. increase in this place in one place it has to come 0 and here it so what I'll do here I'll simply use minus 1. see what happened it has to come wait a minute my mode is not working properly Ctrl d so zero okay now what I'll do I'll just mind the first one and pull D okay one two three four five six seven eight one two three four five six seven eight one two three four five six seven eight three okay so don't ask me no coming to the column part the rose part is over now we have to use the column part what how you will do the column path now I'll show you the same function where you have to use the rows K dollar three colon K3 okay that's great it will give the increment okay mode is not working on I don't know why my mode is not working see it will increment but we want one one one one it has to repeat eight times all once okay so what I'll do here I'll use one function before that I'll divisible by eight so let's see what will happen Ctrl d control backspace and now see 0.125 all it will take all integers and in place of one it has to come 0 here let's see how well do what I'll do here minus 1. control enter double click see one is in place of zero it has came one and here zero so what I'll do I'll use int function in of this whole thing until enter no need to worry I'll explain you first of all let me show you this zero repeating eight times so what I'll do I'll just add one one is between eight times and two is repeating eight times three will repeat eight times so this scenario I will use over here see how I'll do here is equal first of all what I'll do in place of again doing just I'll copy to open copy clipboard you have to type Ctrl C twice Ctrl C twice to open a copy clipboard control c c but so far I'll clear it all Ctrl C only once okay and I'll drag and I'll escape and I'll come to here F2 again the same thing control shift down arrow because Mouse is not working so I'm using keyboard Ctrl c c okay it is copied now what I'll do here index of F4 comma the Row part is the mode function mod in place of J you have to use the F dollar two f dollar two here also F2 k and now coming to the column part it and here also the same thing you have to use okay I have dollars a dollar not three it is still these two okay it is two and close parenthesis control enters and I'll use the keyboard I'll drag formula I'll drag it just I will use Ctrl d here reference arrays are there so why because okay because there is no number of terms 68 after 68 there is no numbers over there see for that present is showing reference error to read of reference alert what you have to do you have to use that for one function that is if added if added if if you find any error of this or another one is also this another one is also there I'm using it because it's not an array function it's a normal function only for that reason I'm using a query if it's an array function you have I'm having another method right so here what I'll do control enter control shift down arrow control d C all of that frequency 56 39 64 here you'll see after 57 again 34 33 86 like this okay if you have any don't ask me now I am asking how to extract the smallest to largest smallest to largest so I'll hide these both things one second height how to extract small is two largest it's very easy guys use this use the small function method copy the data collect the data control F4 to lock all sides and when I press one it will give you first smallest so I want to drag the formula till our whole range and tell all those things so what I'll use here for rows open the bracket L Dollar Tree colon L3 here I'll use that formula we can it's not an array function you can use it now here I will not choose still control d okay smallest to largest it will extract smallest to largest thing if you find any error so what you have to do this one what I'll do if rows L dollar three colon L3 is greater than there are numbers so I will count F2 F2 wait a minute it's not accepted wait a minute fourth greater than counter F4 then blank else than this formula that's it F2 here you have to use to control enter down arrow you can make it as much as you can control d c Okay small is the largest same like that you have can you can use what is in place of small if you want largest to smallest so use this one large control enter control shift down or control D it will accept largest to smallest right and here I am giving you one query which I'm going to post in my telegram group if anybody wants to join I'll in description you'll find the telegram group link you can join over there and you'll find this query okay you have to extract only here Dev extract decimal and non-decimal numbers like this how to get only decimal numbers only decimal numbers here only non-decimal numbers so how you will do this one if it is raw data like this if it is one side I I can but it is two sides like this like a table okay thank you guys thanks for your support have a nice day