converting one column data into table interview question

Key Takeaways

foreign and here I am going to this is the actually this is the company query this is the company query which I'm going to explain you it's very very important query so you have to this is the raw data I'll Zoom a little bit so that you can understand better this is the raw data you have to convert as per this header like if it is gate you have to type date and if it is a ABC this representative name you have to type this one because this product it should go like this all right so you have to convert this into a data like a table it's in a raw data you have to convert into a proper table by using Excel functions right so here I am going to explain you how to do this okay see first of all I'll make it all borders alt HBA for all borders here what I'll do what whatever I need I'll type here date sales rep and here products okay so these three things you are it should convert into a proper table like as I shown before so here C first of all how we have to find how do we have to get C one two three [Music] here four again you get five again here six again your seven like this I'll show you eight nine see how it will be one two three one two three again one two three again one two three again date did one two three four four it should come under date it should come under sales representative how you will do this one see I will explain you step by step right see first of all what I'll do I'll use one function that is columns columns that is dollar dollar wage the formula dollars I 2 colon I2 control enters the controller see how I'll do Ctrl d foreign now you should convert this into here it should come four five six seven eight nine like that okay so how you will do here one two three right so here what I'll do if you try it or wrong by three okay I will block the column but not the row right okay now what I'll do F2 Plus for those of I Dollar Tree colon not really sorry two colon I2 it is not it over guys see how it will come and control r controlling let's see but here it should come one two three four five six but it's not coming like that so you have to convert that like that okay so first of all I'll remove this because I did it right so what I'll do here I'll simply do minus one okay now observe it what it will happen how you will do okay see everything is coming not that much proper but it's a proper so here it should come four five six so what I'll do here multiply by 3. foreign from all sides and F2 and close parenthesis control enters it is in a serial number so you have to convert that into serial number the proper date and the errors also there so what you'll do you have to type this address function if error if you find any error it's a normal function it's not a array function it's a normal function okay so you have to convert this into proper date so how you'll do I'll do like this Ctrl 1 and I will go a date option over there and okay here is the date here is the sales representative product and here is the product again two again three again four C so I will do a conditional format for this see what it will do I'll make it like this okay um F1 for not F1 sorry sorry I forgot control one alignment not alignment condition format I have to go so here it is new rule and a I've dragged little bit is equal to is this F4 f I am removing all locked cells is not equal to blank so format how you will do format I'll format all borders when it is when it is automatic uh don't need currents all borders [Music] [Music] foreign [Music] okay okay now I'll convert this entire table Ctrl t enters so it convert into table so what I'll do I will see I'll add a new data over here like uh observe guys absor here it will Incorporated that new data into a table like first what will there first date I'm taking a random date [Applause] and you say what it is representative name and uh product five enter see automatically it is incorporated and it's already conditioned formatted with all borders okay now I will show you another thing okay you see it I am adding a new data so here it is name date of birth you have resulted and here I will write sales here I will write salary okay so what I'll do here I'll type field one cl rep one enter and I'll drag still wherever I need Okay so like this and here what I'll do I'll keep a random dates sales also sales and salary is equal to Rand between 1500 to 10 000. these are the fields of the products and here I will add random dates what you will do wait a minute simply or type something something okay these are the dates okay so without using indirect function without using indirect connection I should get name sales like just without using indirect function I need like this here I need you you need to extract those names sales and dob without using indirect if I change any header over here like sales not sales salary so it should change accordingly wherever your salary is dead okay without using indirect function without using indirect okay if anybody wants this file I will post it in my telegram group and that telegram group is uh I'm mentioning in description so you have to go over there and look into that right thank you guys thanks for your support and this is the interview question so that this one and this one both are interview questions so I like to shade with my friends who are interested in Excel so please like share and subscribe and I will share this file into telegram group if anybody wants thank you guys thanks for your support good night sweet dreams enjoy the days