Excel Magic Revealed: Unstacking Data Like a Pro! ✨

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 🤣

[@Andy-fd5fg]

Straight to the point, with a little bit of humour chucked in..... you do some good videos

[@chandoo_]

Thank you :)

[@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_]

Those are some great additions @Excelambda

[@moisestatis5418]

GREAT video, thank you Chandoo. The symplest unstacked I have seen with PQ

[@Motivational-Deep]

exactly what I was looking for 😊

[@allan459415]

You continue to amaze me, thank you for the tips

[@chandoo_]

So nice of you

[@moisestatis5418]

Great and fun video. Thansks

[@adnanabbasi9482]

Very useful trick especially power query ones. list and table rows.

[@chandoo_]

I am glad you liked it.

[@chrism9037]

Very cool trick Chandoo, thanks!

[@chandoo_]

Glad you like it!

[@thebhaskarjoshi]

1:04 I thought, I just got a call from my boss in the dinner time.

[@chandoo_]

haha...

[@johnvodopija1743]

Great tutorial. Informative and gave me a few laughs along the way. Keep the gold nuggets coming my friend. Cheers JV 👍😎🇦🇺

[@chandoo_]

Glad you enjoyed it

[@merukran8388]

Superb 🧡

[@sakeenasammi1768]

Amazing work as always @chandoo😀

[@chandoo_]

You are welcome :)

[@djl8710]

Excellent!

[@chandoo_]

Glad you liked it!

[@txreal2]

OK. What if the first 6 rows are column headers? Any tip is appreciated.

[@behrad9712]

Thank you!🙂

[@chandoo_]

You’re welcome 😊

[@taizoondean689]

Thank You Sir Nice one

[@chandoo_]

You are welcome Taizoon.

[@Luciano_mp]

I liked this! Thank you Chandoo...

[@chandoo_]

My pleasure 😊

[@datascientist6233]

Thanks chandu.. really great..but i solve similar kind of problem through Google sheets function..

[@chandoo_]

AWESOME 😍

[@abhinav10x]

This function not available in excel 2016, but learning from you is great

[@chandoo_]

You can use the Power Query option in 2016 (or 2013 too).

[@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]

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_]

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]

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_]

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]

Great Sir

[@chandoo_]

You are welcome.

[@arbazahmad7177]

Great 👍

[@chandoo_]

You are welcome :)

[@mohammadalbizri2013]

Amazing

[@chandoo_]

Thanks

[@Vaasu94]

Hey Chandu sir, I confused when your Microsoft Teams call rang up😁

[@fizabasharat2086]

I dont have 6 size, my data has different size in each type what to do now

[@amansrivastava8111]

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]

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]

I would like to see some chatGPT and AI related videos, bcz your videos are appearing from layman's POV.

[@karanpaulchaudhary7681]

20/6/23 I promise to be a data analyst

[@Vikas.Verma06]

Meet soon 😊

[@travelsphere7402]

Chatur

[@MaharanaPratap69]

What's rhe update did u become?

[@karanpaulchaudhary7681]

@@MaharanaPratap69 it’s a learning process, working on it day by day

[@karanpaulchaudhary7681]

@@travelsphere7402 thanks bro sounds like it but feeling is different

[@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_]

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]

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]

If you have a mac, you cannot use powerquery like this.

[@zoha3728]

@@Sekoleyte android ha

[@chandoo_]

You can use Power Query steps. You just need to enable the "Formula bar" in view ribbon in Power Query editor.

[@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_]

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]

Unable to hear

[@MPSinghKushwaha]

Sir Your videos are nice but in English we want in Hindi

[@chandoo_]

Thank you. I don't know enough Hindi to make videos. Do watch with Hindi sub-titles (KZbin sometimes autogenerates these)

[@nisha8416]

Not sure, y I am not able to hear your voice

[@chandoo_]

Sorry to hear that (excuse the pun). Try again with a different device.