Grab the file I used in the video from here 👉 pages.xelplus.com/unstack-file
@tennesseemashed
5 жыл бұрын
One of the simplest and smartest things I’ve seen in excel. Wow
@christoskyriacou7036
2 жыл бұрын
A great and very smart way of doing it. Thanks, you are a treasure.
@JayEastward
3 жыл бұрын
This is brilliant. I had exactly this problem, my usual spreadsheet jockey was running an errand out of the office, and I needed this done asap. Found you with Google and said to myself: "Really? That simple?" I tried it and BAM! A total of 3,976 rows (copied and pasted from a table on a website) transformed into a 497x8 array just like that. G.E.N.I.U.S.
@rivernet62
5 жыл бұрын
Don’t worry, we will be using YOUR initials ;)
@randallm9083
2 жыл бұрын
Yea your initials are the formula now lol
@deepbhatta1743
3 жыл бұрын
One of the simplest and smartest things I’ve seen in excel. Excellent
@LeilaGharani
3 жыл бұрын
Glad it was helpful!
@solomongebremariam427
2 жыл бұрын
Smart and easy method to follow. Thank you very much!
@TheNabob57
4 жыл бұрын
Wow. I have been using Excel since 1997, and am pretty proficient. But I have never seen this *most excellent* hack! Kudos to you! I have subscribed to your vids because your explanations are spot on, simple and your voice is measured and quite pleasant. Yay Leila!
@TheNabob57
4 жыл бұрын
Oh. and I will use YOUR initials too :)
@rasoulkhoshravan5912
4 жыл бұрын
Leila your are really smart. Your simple solutions to very hard issues are really amazing.
@ranjanadissanayaka5390
3 жыл бұрын
That's extremely useful... I thank you
@mattias5063
5 жыл бұрын
As always with your examples and tutorials; straight to the point in an easy to understand way and quite simply (no doubt) the best solution. As I´ve said before, never a dull moment watching your tutorials. You rock! :)
@LobanRahman
5 жыл бұрын
Love your hacks. Love your instructional style!
@LeilaGharani
5 жыл бұрын
I'm glad you like it!
@joydeeppurkayastha8015
5 жыл бұрын
What a smart way to unstack,thanks Leila
@waleethmohamedismail4543
5 жыл бұрын
Obviously... Very useful shortcut method. Thank you very much
@LeilaGharani
5 жыл бұрын
You're very welcome. Glad it's useful.
@allisonbell8721
2 жыл бұрын
This might be the most brilliant video ever posted on KZitem. If the Nobel Prize people had a prize for software use advice, you should get that Nobel prize.
@m.p259
2 жыл бұрын
really smart function!!! thanks for sharing:) I enrolled your lesson already!
@مقدادحديد
5 жыл бұрын
Happy New year sweet queen and thank you for this trick
@ramya3928
5 жыл бұрын
Perfect! Thanks daily I come across this kind of separation and since I am unaware I was doing all these days manually or via other formula..
@richsandness5409
4 жыл бұрын
I like "Lazy Ways", ... ;-) Well expressed, and well conveyed.
@kaaa3485
5 жыл бұрын
Wow, very simple and very clever. Thanks, Leila.
@LeilaGharani
5 жыл бұрын
You're very welcome :)
@johnjudge8436
5 жыл бұрын
That....is genius. Thank You.
@LeilaGharani
5 жыл бұрын
Just some Excel magic :) Glad you like it John.
@jreder25
5 жыл бұрын
For unique values I would just make a pivot table, but this is a neat solution. Thx
@jfiacco2000
Жыл бұрын
Genius! Thank you!
@vhjaimes
5 жыл бұрын
This is very helpful Leila. Thank you very much!
@nandhagopal8699
5 жыл бұрын
Really creative..Thanks a lot..
@54788963
5 жыл бұрын
Smart, nice trick. thanks!
@moisestatis5418
7 ай бұрын
AWESOME! THANKS SO MUCH
@niyanaknowledgecave3277
5 жыл бұрын
Best excel tutor I have ever seen in my life...!! Thank you!!
@Amr-Ibrahim-AI
5 жыл бұрын
Wow! Simplicity is the mother of beauty! This is an awesome trick
@richsandness5409
4 жыл бұрын
Yep !
@alitanim3945
5 жыл бұрын
Leila I like your teaching style. I bought ur VBA course from udemy couple of days ago.
@LeilaGharani
5 жыл бұрын
Thank you Ali for your support! I’m glad you like the tutorials.
@raymeester7883
5 жыл бұрын
Holy Shit! It's so simple it's pure Genius!
@LeilaGharani
5 жыл бұрын
Glad you like it :)
@flaviobertasi2281
5 жыл бұрын
Brilliant!
@9660137157
5 жыл бұрын
Thank you for such beautiful idea
@mdraza924
5 жыл бұрын
Beautiful trick Thank you so much 😊
@LeilaGharani
5 жыл бұрын
You're very welcome Mohammad :)
@azzeddineseghiri8466
5 жыл бұрын
Thank you ....great trick
@LeilaGharani
5 жыл бұрын
You're welcome! Glad you like it :)
@nexsel3934
3 жыл бұрын
Thankyou so much
@dbookmarker9524
5 жыл бұрын
This is not Excel, this is Magic! TY!
@miguelitobautista8908
4 жыл бұрын
Awesome! This the reason I subscribed. :)
@LeilaGharani
4 жыл бұрын
Awesome! Thank you!
@michaelmccatty1341
5 жыл бұрын
you are amazing. Thank you.
@LeilaGharani
5 жыл бұрын
You're very welcome!
@mikoajkomorowski1290
5 жыл бұрын
You could also use the OFFSET formula: =OFFSET($A$4,(ROWS($A$1:A1)-1)*3+(COLUMNS($A$1:A1)-1),0) this would make it more automatic and easier to extend in case you have to get more data input
@AbuTalha-eo7pr
5 жыл бұрын
Super; thanks
@BondiMacF
4 жыл бұрын
Great thanks. I was just going to try to work on something similar. Instead of ROWS($A$1:A1), how about simply ROW(A1)? Now you have =OFFSET($A$4,(ROW(A1)-1)*3+(COLUMN(A1)-1),0) which works out as the same thing but less typing
@samuelthamburaj
5 жыл бұрын
Awesome Trick...
@LeilaGharani
5 жыл бұрын
Glad you like it Samuel.
@pralhadshetye8863
5 жыл бұрын
Excellent
@debasischatterjee1328
Жыл бұрын
Pls make a video in excel, A set of data copying multiple times very easily
@surihabana
5 жыл бұрын
You're amazing!
@LeilaGharani
5 жыл бұрын
Glad you like it Suri :)
@prafulldindorkarvlogs
Жыл бұрын
Can we add attachments automatically while sending such mails, for example I am daily updating the debtors list after receiving payment from the customer and every time I have to generate new mail with upadated sheet. Thank you
@salmansadatkarim
5 жыл бұрын
Awesome 👍👍
@dmr450
5 жыл бұрын
Very nice!
@LeilaGharani
5 жыл бұрын
Glad you like it David.
@mauriciomolinahn
5 жыл бұрын
Leila is one the most wonderful and gifted teacher of all times! It's so awesome to watch her videos!!!
@alexisafrica7810
4 жыл бұрын
This is genius and simple
@eks007
5 жыл бұрын
So glad I found your channel, Leila. As an IT professional constantly working in excel, your videos are extremely useful. Question: what if your stack of data has no clear pattern? In your example, you had a new title every 3 rows from the previous title. What if you're working in a sheet, where the title appears randomly? Thanks in advance!
@spiralingsphere3785
2 жыл бұрын
Collapse the rows until it shows in a pattern.
@r0shantamang724
5 жыл бұрын
Data dumped from different accounting software will not be in a proper format which will somehow make us difficult to work in a excel workbook. Can you please share some ideas to manage those data in a proper format in excel. Thanks :)
@michalroesler
4 жыл бұрын
cool video and nice trick.
@LeilaGharani
4 жыл бұрын
Thank you! Cheers!
@AzeemaFaizunnisa
2 жыл бұрын
Awesome.
@debbiegaydos
5 жыл бұрын
I needed to put a single column of 300 values into a 10x30 grid. Googled and googled and tried to figure out a simple way to do it. So glad I found this, what a great (and simple) trick! Thanks!
@taizoondean689
4 жыл бұрын
Superb
@roycemekolle7334
5 жыл бұрын
Please I will like to know how u will approach this if the pattern was uniform. Like some had 3 rows under while other had 2 and others with 5!?
@i8777-z3z
5 жыл бұрын
very nice
@Gonzalo2233
5 жыл бұрын
Master !
@arsalaniu
5 жыл бұрын
Genius as always. Can you share any dynamic way of doing it?
@01chippe
Жыл бұрын
Use the wraprows function. I explained this in a comment. If you don’t see it, just do a Google search for Excel Wraprows. It’s 1,000 times faster and easier.
@cfrith8963
5 жыл бұрын
Incredible!!
@zedzpan
5 жыл бұрын
This blew my mind. What a great tip!
@thedr00
3 жыл бұрын
This is immense, and do simple. It was taking me hours to manually move cells around, your tips had me finished inside of 10 minutes. Thank you thank you thank you!
@LeilaGharani
3 жыл бұрын
Fantastic! Glad it helped.
@fuzz-head4800
5 жыл бұрын
Offset formula does trick as well using plus 3 for rows
@m1ha1mateescu
3 жыл бұрын
Very smart trick! What if there are 2 or 3 empty cells between data? In your example, A6 is 240, A7 is Blend, but what if A6 is 240, A7 and A8 are empty cells, and Blend is in cell A9 (I copied a table from a website, where looks ok, but when pasted in Excel it offsets some rows). Thanks in advance if you have the time...
@marcaboujamra
2 жыл бұрын
Did you receiv an answer, i have empty cells between the data, its anoying when i want to drop down list etc...
@direnchatfield4112
2 жыл бұрын
I added emails and I put exact format you used. However when I extended the format for the other 2 emails, it doesnt change and use the new email address. It still sends it to the original email used in c5 cell. where do I make the mistake?
@sjn7220
5 жыл бұрын
Cool trick! I have to do this quite often but have always used =index(array, column(A1)+(row(A1)-1)*3). Column and row can be switched whether you're destacking vertically or horizontally (and 3 would change depending on the group size). At first I had to always think it out but now it is so ingrained in my head it comes out somewhat naturally.
@niuean22kid
5 жыл бұрын
Amazing ! I would normally just use power query but this is even quicker
@iecivil
5 жыл бұрын
Well now it can officially be called as LG function, Thanks for the tip 😊
@iankr
3 жыл бұрын
Brilliant! Another, similar trick I learned was to disguise formulae if you want to copy a large block of cells with formulae that don't have absolute references, but where you *don't* want the references to change when you copy. Select the range, do +H to Find "=" and Replace with "%%"; copy the range to where you want it, then reverse the Find and Replace to put them back to formulae preceded by "=". I need to do this so often, that I've put the two stages into macros, triggered by buttons on my customised ribbon.
@anshuiway
5 жыл бұрын
Really mind blowing trick for stuck data converted to proper format Thanks for sharing this video
@majdyazigi8185
5 жыл бұрын
20 dislikes!!!! some people are really strange, great video Liela
@harshrr1
5 жыл бұрын
Jugaaaad level 100! much appreciated. _/\_
@LeilaGharani
5 жыл бұрын
Glad you like it.
@TechnoNetworkGuy
2 жыл бұрын
Some Keyboard shortcuts which improves productivity :- kzitem.info/news/bejne/1GuQ22qLqIegl6A
@IssueBoyStefan
5 жыл бұрын
Actually because there is a pattern that every third one should be put in each column, you can easy do this with offset function. For example for the APP column: =OFFSET(A4,(ROW(1:1)-1)*2,0) For the other columns you just need to change the starting point from A4 to A5 / A6 :)
@jameswomack7328
5 жыл бұрын
This can also be done without the need to find and replace. Just build the formulas for the first 2 rows then select them both and drag to fill just as you did after adding the "lg". Same results and eliminates a step.
@leftofpunk
5 жыл бұрын
Subbed!
@LeilaGharani
5 жыл бұрын
Welcome to the community!
@hello201014
5 жыл бұрын
Really cool! Instantly subscribed!
@243WW
2 ай бұрын
Oooo this would be nice if my excel worked the same as yours!!! I can't get the drag to recognise the pattern... 2024, 365. Yep got it, turn of the filters!
@FredericLEGUEN-Excel
4 жыл бұрын
Great job Leila, but my method with INDIRECT is also great 😉
@LeilaGharani
4 жыл бұрын
I'm sure it is :)
@SandipBanerjee
4 жыл бұрын
Excellent idea! Thank You :)
@sachinrv1
5 жыл бұрын
leila watching your video for 1:31 min i got the following solution. hope you will admire. love u n your videos =IF((MOD(ROW(),4))=2,OFFSET($A2,COLUMN()-2,0),""). this is when each record has 4 rows details. one can twick this to fit his situation
@planxlsm
5 жыл бұрын
cool, I thought you would use the DESLOC function, but this method is easier and faster.
@vetman98
Жыл бұрын
Thank you I have been looking for solving this problem for a long time. Your videos are amazing
@ybaggi
5 жыл бұрын
Ok, I-ma an spreadsheet wizz... or so I thought bu tthis trick is awesome and I can see it applying to many more of my situations.
@Pseudo___
5 жыл бұрын
just use a side column.... 1,4,7,10,13 ect winows autofills down, then use a index("datacolumn", "sidecolumn"), index("datacolumn", "sidecolumn+1"), dct windows will add +1 to each column done and expandable
@변준영-l2x
2 жыл бұрын
AMAZING. Thanks. I have a question if same problem can solved using Power Query.
@OneEyedOneHornedGian
Жыл бұрын
I kinda like the indirect formula for something like this. There are three variables so it would be something like =indirect("A"&3×c4+1) where c4 would be the iteration number, 1 would be the variables after the original and 3 would be the number of new columns being created.
@a.j.wilkes6352
5 жыл бұрын
Clever trick!
@carlosparedes733
4 жыл бұрын
I have been watching your videos which I find very good. I have a question. How do I extract data from a table to make smaller tabes From This R&D John QA Luis PROD Charles MAINT Richard QA Anthony PROD Rachel R&D Eddy MAINT James MAINT Johnny PROD Mary R&D Paul PROD Robert To this R&D QA PROD MAINT John Luis Charles Richard Eddy Anthony Rachel James Paul Mary Johnny Robert Thanks
@SuperDk03
2 жыл бұрын
Try this - =INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1)) where 5 is the length of the repetition. Thanks!
@Tat-cx1lr
5 жыл бұрын
Merci
@nebojsa1976
3 жыл бұрын
Here is the question. Let say there is one long column, 1500 rows and for example, one row in that column has these numbers: 2 15 43 and there are 1500 rows like this with different numbers of course. Would it be possible to separate these 3 numbers in 3 columns (number 2 in one column, 15 in second column and 43 in third column and it goes down 1500 rows and separates them all). Is it possible to do that somehow?
@marshallcan7763
5 жыл бұрын
WOWOW just awesome
@superhiway
2 жыл бұрын
A BRILLIANT resolution to my exact problem. I am now a new subscriber and I will be back soon to probe your brain some more. Thanks so much.!!
@Orestis_Pafiliaris_90
4 жыл бұрын
Hi, I am trying to nest a Randomvalue formula inside an If so the randomvalue can be triggered by a checkbox. Excel shows that I have either put too many or too few arguments for this function. Does anyone have any ideas how to fix this? what am I doing wrong?
@leekuanyew6219
2 жыл бұрын
Thank you so much for the video, May I ask u some question on excel? I am in the Rental market, I need to submit monthly report of Unit that are due for collection. Problem is, contract payment are not standardized, some unit pay monthly, some 2 month once, some quarterly and 6 month. Hence, it take long period of time to work out the monthly collections of we are dealing with over hundred of units. If there a formula that you can advice me to use? Thanks Leila, looking forward to your answer
@stuartc6303
11 ай бұрын
Excellent video as usual but my excel is being difficult, also as usual. When I drag down it throws the same numbers in over and over and only rarely actually extends the sequence.
@jjsmallpiece9234
4 жыл бұрын
Great trick. I agree though, that its not as easy as first thought. But that goes for much of Excel, largely an unfriendly programme, certainly not intuitive about how the formula and functions are used or written. There must be better software available?
@ronaldcastaneda
2 жыл бұрын
I literally started laughing from excitement when I saw the email come through!! Thank you so so much for this video it is going to save me so much time and will definitely reduced my typing errors! Fantastic tip thank you so so so much!!!
@Samriddhi1020
3 жыл бұрын
Hi.. I have one problem. Seeking your advice on this Problem is that we have two in first column name is mentioned and in second column number is mentioned, number and name both are duplicate I wants unique name and number Is there any way to find if without pivot table
Пікірлер: 884