Excel Magic Revealed: Unstacking Data Like a Pro! ✨

  Рет қаралды 30,115

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 Жыл бұрын
exactly what I was looking for 😊
@allan459415
@allan459415 Жыл бұрын
You continue to amaze me, thank you for the tips
@chandoo_
@chandoo_ Жыл бұрын
So nice of you
@moisestatis5418
@moisestatis5418 9 ай бұрын
Great and fun video. Thansks
@adnanabbasi9482
@adnanabbasi9482 Жыл бұрын
Very useful trick especially power query ones. list and table rows.
@chandoo_
@chandoo_ Жыл бұрын
I am glad you liked it.
@chrism9037
@chrism9037 Жыл бұрын
Very cool trick Chandoo, thanks!
@chandoo_
@chandoo_ Жыл бұрын
Glad you like it!
@thebhaskarjoshi
@thebhaskarjoshi Жыл бұрын
1:04 I thought, I just got a call from my boss in the dinner time.
@chandoo_
@chandoo_ Жыл бұрын
haha...
@johnvodopija1743
@johnvodopija1743 Жыл бұрын
Great tutorial. Informative and gave me a few laughs along the way. Keep the gold nuggets coming my friend. Cheers JV 👍😎🇦🇺
@chandoo_
@chandoo_ Жыл бұрын
Glad you enjoyed it
@merukran8388
@merukran8388 Жыл бұрын
Superb 🧡
@sakeenasammi1768
@sakeenasammi1768 Жыл бұрын
Amazing work as always @chandoo😀
@chandoo_
@chandoo_ Жыл бұрын
You are welcome :)
@djl8710
@djl8710 Жыл бұрын
Excellent!
@chandoo_
@chandoo_ Жыл бұрын
Glad you liked it!
@txreal2
@txreal2 13 күн бұрын
OK. What if the first 6 rows are column headers? Any tip is appreciated.
@behrad9712
@behrad9712 Жыл бұрын
Thank you!🙂
@chandoo_
@chandoo_ Жыл бұрын
You’re welcome 😊
@taizoondean689
@taizoondean689 Жыл бұрын
Thank You Sir Nice one
@chandoo_
@chandoo_ Жыл бұрын
You are welcome Taizoon.
@Luciano_mp
@Luciano_mp Жыл бұрын
I liked this! Thank you Chandoo...
@chandoo_
@chandoo_ Жыл бұрын
My pleasure 😊
@datascientist6233
@datascientist6233 Жыл бұрын
Thanks chandu.. really great..but i solve similar kind of problem through Google sheets function..
@chandoo_
@chandoo_ Жыл бұрын
AWESOME 😍
@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).
@RobertoPoehlmann
@RobertoPoehlmann Жыл бұрын
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?
@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.
@user-lo2jo2yq1o
@user-lo2jo2yq1o Жыл бұрын
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_ Жыл бұрын
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
@nomanhowlader9651
@nomanhowlader9651 Жыл бұрын
Great Sir
@chandoo_
@chandoo_ Жыл бұрын
You are welcome.
@arbazahmad7177
@arbazahmad7177 Жыл бұрын
Great 👍
@chandoo_
@chandoo_ Жыл бұрын
You are welcome :)
@mohammadalbizri2013
@mohammadalbizri2013 Жыл бұрын
Amazing
@chandoo_
@chandoo_ Жыл бұрын
Thanks
@Vaasu94
@Vaasu94 7 ай бұрын
Hey Chandu sir, I confused when your Microsoft Teams call rang up😁
@fizabasharat2086
@fizabasharat2086 7 ай бұрын
I dont have 6 size, my data has different size in each type what to do now
@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
@balamuruganbala7253
@balamuruganbala7253 Жыл бұрын
I would like to see some chatGPT and AI related videos, bcz your videos are appearing from layman's POV.
@karanpaulchaudhary7681
@karanpaulchaudhary7681 Жыл бұрын
20/6/23 I promise to be a data analyst
@Vikas.Verma06
@Vikas.Verma06 Жыл бұрын
Meet soon 😊
@travelsphere7402
@travelsphere7402 Жыл бұрын
Chatur
@MaharanaPratap69
@MaharanaPratap69 9 ай бұрын
What's rhe update did u become?
@karanpaulchaudhary7681
@karanpaulchaudhary7681 9 ай бұрын
@@MaharanaPratap69 it’s a learning process, working on it day by day
@karanpaulchaudhary7681
@karanpaulchaudhary7681 9 ай бұрын
@@travelsphere7402 thanks bro sounds like it but feeling is different
@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.
@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.
@nisha8416
@nisha8416 Жыл бұрын
Unable to hear
@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 Жыл бұрын
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
Рет қаралды 26 М.
Learn Power Query & Automate Boring Data Tasks in 15 Minutes!
18:45
OMG🤪 #tiktok #shorts #potapova_blog
00:50
Potapova_blog
Рет қаралды 17 МЛН
Tom & Jerry !! 😂😂
00:59
Tibo InShape
Рет қаралды 55 МЛН
Always be more smart #shorts
00:32
Jin and Hattie
Рет қаралды 34 МЛН
I saw my boss do these 10 things in Excel!
7:36
Chandoo
Рет қаралды 2,6 МЛН
Data cleaning in Excel - 10 tricks *PROs* use all the time
17:02
10 things my teammate is doing in Excel 😱
7:42
Chandoo
Рет қаралды 273 М.
Excel Dynamic Arrays and How to use them...
10:22
Chandoo
Рет қаралды 258 М.
3 Essential Excel skills for the data analyst
18:02
Access Analytic
Рет қаралды 1,5 МЛН
Unstack Uneven Rows into Separate Columns
16:24
Goodly
Рет қаралды 16 М.
Unstack a Million Uneven Rows to Columns in Power Query
10:50
Hisense Official Flagship Store Hisense is the champion What is going on?
0:11
Special Effects Funny 44
Рет қаралды 2,1 МЛН
iPhone 12 socket cleaning #fixit
0:30
Tamar DB (mt)
Рет қаралды 52 МЛН
cute mini iphone
0:34
승비니 Seungbini
Рет қаралды 5 МЛН
Will the battery emit smoke if it rotates rapidly?
0:11
Meaningful Cartoons 183
Рет қаралды 32 МЛН