Excel Magic Revealed: Unstacking Data Like a Pro! ✨

  Рет қаралды 29,895

Chandoo

Chandoo

Күн бұрын

Unstack your data quickly and efficiently with this trick. Watch the full thing for surprises!
⏱ IN THE VIDEO
==============
0:00 - Unstacking with WRAP functions
1:03 - Unstacking data when it is from a TEXT file
3:02 - Unstacking a folder full of files
📺 WATCH NEXT
==============
Combine a folder of Excel (or Text) files - • Powerful trick to comb...
Get data from PDFs - • How to Combine PDFs to...
Combine data from all sheets - • AWESOME Excel trick to...
Combine data when headers don't match - • AWESOME Excel trick to...
SAMPLE FILE 📂
=============
Use this file to practice the unstack concepts - chandoo.org/wp/wp-content/upl...
~
What does the data analyst call a loaf of bread? 🍞
bUNSTACK 🤣

Пікірлер: 69
@Andy-fd5fg
@Andy-fd5fg Жыл бұрын
Straight to the point, with a little bit of humour chucked in..... you do some good videos
@chandoo_
@chandoo_ Жыл бұрын
Thank you :)
@Excelambda
@Excelambda Жыл бұрын
Great video!!✌ Reasoning exercise to know which one to use next time 😀, (the opposite rule) : - what display of rows will get if we wrap vector "v" into "k" columns => WRAPROWS(v,k) - what display of columns will get if we wrap vector "v" into "k" rows => WRAPCOLS(v,k) Fun fact : WRAPROWS(v,k)=TRANSPOSE(WRAPCOLS(v,k)) and viceversa 😉 Easy form. alternative for our case If you do not have WRAPROWS (for vert. vector "v", "k" columns ) =INDEX(v,SEQUENCE(ROWS(v)/k,k)) - for WRAPCOLS equivalent we transpose the previous result Unfortunately WRAP...functions deal only with vectors (1D arrays) so I managed to create simple custom-made functions to stack or unstack 2D arrays also. 😉
@chandoo_
@chandoo_ Жыл бұрын
Those are some great additions @Excelambda
@moisestatis5418
@moisestatis5418 4 ай бұрын
GREAT video, thank you Chandoo. The symplest unstacked I have seen with PQ
@Motivational-Deep
@Motivational-Deep 11 ай бұрын
exactly what I was looking for 😊
@moisestatis5418
@moisestatis5418 9 ай бұрын
Great and fun video. Thansks
@allan459415
@allan459415 Жыл бұрын
You continue to amaze me, thank you for the tips
@chandoo_
@chandoo_ Жыл бұрын
So nice of you
@chrism9037
@chrism9037 Жыл бұрын
Very cool trick Chandoo, thanks!
@chandoo_
@chandoo_ Жыл бұрын
Glad you like it!
@merukran8388
@merukran8388 Жыл бұрын
Superb 🧡
@johnvodopija1743
@johnvodopija1743 11 ай бұрын
Great tutorial. Informative and gave me a few laughs along the way. Keep the gold nuggets coming my friend. Cheers JV 👍😎🇦🇺
@chandoo_
@chandoo_ 11 ай бұрын
Glad you enjoyed it
@adnanabbasi9482
@adnanabbasi9482 Жыл бұрын
Very useful trick especially power query ones. list and table rows.
@chandoo_
@chandoo_ Жыл бұрын
I am glad you liked it.
@sakeenasammi1768
@sakeenasammi1768 Жыл бұрын
Amazing work as always @chandoo😀
@chandoo_
@chandoo_ Жыл бұрын
You are welcome :)
@djl8710
@djl8710 Жыл бұрын
Excellent!
@chandoo_
@chandoo_ Жыл бұрын
Glad you liked it!
@thebhaskarjoshi
@thebhaskarjoshi Жыл бұрын
1:04 I thought, I just got a call from my boss in the dinner time.
@chandoo_
@chandoo_ Жыл бұрын
haha...
@taizoondean689
@taizoondean689 Жыл бұрын
Thank You Sir Nice one
@chandoo_
@chandoo_ Жыл бұрын
You are welcome Taizoon.
@behrad9712
@behrad9712 Жыл бұрын
Thank you!🙂
@chandoo_
@chandoo_ Жыл бұрын
You’re welcome 😊
@Luciano_mp
@Luciano_mp Жыл бұрын
I liked this! Thank you Chandoo...
@chandoo_
@chandoo_ Жыл бұрын
My pleasure 😊
@nomanhowlader9651
@nomanhowlader9651 Жыл бұрын
Great Sir
@chandoo_
@chandoo_ Жыл бұрын
You are welcome.
@mohammadalbizri2013
@mohammadalbizri2013 Жыл бұрын
Amazing
@chandoo_
@chandoo_ Жыл бұрын
Thanks
@arbazahmad7177
@arbazahmad7177 Жыл бұрын
Great 👍
@chandoo_
@chandoo_ Жыл бұрын
You are welcome :)
@datascientist6233
@datascientist6233 Жыл бұрын
Thanks chandu.. really great..but i solve similar kind of problem through Google sheets function..
@chandoo_
@chandoo_ Жыл бұрын
AWESOME 😍
@txreal2
@txreal2 18 сағат бұрын
OK. What if the first 6 rows are column headers? Any tip is appreciated.
@abhinav10x
@abhinav10x Жыл бұрын
This function not available in excel 2016, but learning from you is great
@chandoo_
@chandoo_ Жыл бұрын
You can use the Power Query option in 2016 (or 2013 too).
@RamKumar-vb4et
@RamKumar-vb4et 10 ай бұрын
Excellent video, as always. A query: If I have multiple column unstacking problem (example, data is in multiple rows with each row having a different combination of id, course, grade, university). Is it possible to unstack it and have data reorganized with columns headings id, course, grade, and university). Thanks,
@chandoo_
@chandoo_ 10 ай бұрын
If your data has a pattern, you can use either a formula or PQ to unstack the data. If you have just a few columns, apply the wrap functions, one at a time and finally VSTACK the data.
@RobertoPoehlmann
@RobertoPoehlmann 11 ай бұрын
What if i have two types of group rows like one group with vendor data (3 rows) and then 5 rows with each invoice information?
@user-lo2jo2yq1o
@user-lo2jo2yq1o 11 ай бұрын
hi chanddo can make us the tutorial how to use BA or DA or SQL with help of Ai tools ... It would be great from you to add-on these topic as your explanation is very understandable... waiting for videos
@chandoo_
@chandoo_ 11 ай бұрын
Thanks for this suggestion. I am in the process of adding few more AI videos. Here is one I did few weeks ago. You should definitely watch it - kzbin.info/www/bejne/rnjUeZKVnbGJmbc
@Vaasu94
@Vaasu94 6 ай бұрын
Hey Chandu sir, I confused when your Microsoft Teams call rang up😁
@amansrivastava8111
@amansrivastava8111 10 ай бұрын
Hi Chandoo, I have a problem very similar to this that I have been experiencing from last few weeks. How can I seek your help for that?
@amansrivastava8111
@amansrivastava8111 10 ай бұрын
It is related to the data format I get when I download ingredients data from Euromonitor. Pls let me know how can I share my problem in detail wi th you
@fizabasharat2086
@fizabasharat2086 6 ай бұрын
I dont have 6 size, my data has different size in each type what to do now
@balamuruganbala7253
@balamuruganbala7253 11 ай бұрын
I would like to see some chatGPT and AI related videos, bcz your videos are appearing from layman's POV.
@Vikas.Verma06
@Vikas.Verma06 Жыл бұрын
hi chandu bhaiya i downloaded a badly structured sample file from internet i have its answer also but i don't understand how to format it. How can you give solution ...
@chandoo_
@chandoo_ Жыл бұрын
Hmm.. it is hard for me to provide 1:1 help like this. I suggest posting about this in a forum. You can solve a lot of data problems with Power Query. If you have time, learn it. Here is a detailed video - kzbin.info/www/bejne/hpqpcpSVn8-Ue6s
@zoha3728
@zoha3728 Жыл бұрын
Sir I have Microsoft office 2021 version plz guide me power query step bcz I put a lot effort but still not solved this problem it's not working when I follow yours steps sir plz reply the previous video
@Sekoleyte
@Sekoleyte Жыл бұрын
If you have a mac, you cannot use powerquery like this.
@zoha3728
@zoha3728 Жыл бұрын
@@Sekoleyte android ha
@chandoo_
@chandoo_ Жыл бұрын
You can use Power Query steps. You just need to enable the "Formula bar" in view ribbon in Power Query editor.
@karanpaulchaudhary7681
@karanpaulchaudhary7681 Жыл бұрын
20/6/23 I promise to be a data analyst
@Vikas.Verma06
@Vikas.Verma06 Жыл бұрын
Meet soon 😊
@travelsphere7402
@travelsphere7402 Жыл бұрын
Chatur
@gorkhali6363
@gorkhali6363 8 ай бұрын
What's rhe update did u become?
@karanpaulchaudhary7681
@karanpaulchaudhary7681 8 ай бұрын
@@gorkhali6363 it’s a learning process, working on it day by day
@karanpaulchaudhary7681
@karanpaulchaudhary7681 8 ай бұрын
@@travelsphere7402 thanks bro sounds like it but feeling is different
@denismazzucato114
@denismazzucato114 Жыл бұрын
=IF(MOD(ROW(C5);6)=5;TEXTSPLIT(TEXTJOIN(";";;C5:C10);";");"") this gives you all data unstacked in 6 columns. You just have to delete empty rows.
@chandoo_
@chandoo_ Жыл бұрын
That is a cool function. But if I have TEXTSPLIT() in my Excel, I will also have WRAP functions. So I prefer to use those.
@MPSinghKushwaha
@MPSinghKushwaha Жыл бұрын
Sir Your videos are nice but in English we want in Hindi
@chandoo_
@chandoo_ Жыл бұрын
Thank you. I don't know enough Hindi to make videos. Do watch with Hindi sub-titles (KZbin sometimes autogenerates these)
@nisha8416
@nisha8416 Жыл бұрын
Unable to hear
@nisha8416
@nisha8416 Жыл бұрын
Not sure, y I am not able to hear your voice
@chandoo_
@chandoo_ Жыл бұрын
Sorry to hear that (excuse the pun). Try again with a different device.
UNPIVOT: Data Cleaning Power Tricks 💡
4:57
Chandoo
Рет қаралды 25 М.
Learn Power Query & Automate Boring Data Tasks in 15 Minutes!
18:45
ТАМАЕВ vs ВЕНГАЛБИ. Самая Быстрая BMW M5 vs CLS 63
1:15:39
Асхаб Тамаев
Рет қаралды 3,8 МЛН
Which one is the best? #katebrush #shorts
00:12
Kate Brush
Рет қаралды 20 МЛН
ПООСТЕРЕГИСЬ🙊🙊🙊
00:39
Chapitosiki
Рет қаралды 67 МЛН
ИРИНА КАЙРАТОВНА - АЙДАХАР (БЕКА) [MV]
02:51
ГОСТ ENTERTAINMENT
Рет қаралды 1,2 МЛН
Excel Dynamic Arrays and How to use them...
10:22
Chandoo
Рет қаралды 258 М.
10 things my teammate is doing in Excel 😱
7:42
Chandoo
Рет қаралды 272 М.
Quick Excel Trick to Unstack Data from one Column to Multiple Columns
5:04
I saw my boss do these 10 things in Excel!
7:36
Chandoo
Рет қаралды 2,6 МЛН
Power Query - Faster & Easier Parameters
13:38
BCTI
Рет қаралды 32 М.
Unstack Uneven Rows into Separate Columns
16:24
Goodly
Рет қаралды 16 М.
10x your productivity with these AI tools in Excel 😲
18:42
5 НЕЛЕГАЛЬНЫХ гаджетов, за которые вас посадят
0:59
Кибер Андерсон
Рет қаралды 1,6 МЛН
Main filter..
0:15
CikoYt
Рет қаралды 4,5 МЛН
Девушка и AirPods Max 😳
0:59
ОТЛИЧНИКИ
Рет қаралды 16 М.