Excel Trick#56 : How To Transpose Multiple Columns And Rows Into Single Column || dptutorials
ฝัง
- เผยแพร่เมื่อ 26 ก.ย. 2018
- In this tutorial, let us learn How To Transpose Multiple Columns And Rows Into Single Column
DON'T CLICK THIS: ➡️ bit.ly/3sPIZvD
Link to download the exercise file: ➡️ bit.ly/377FnuD
You can support me by: ➡️ www.buymeacoffee.com/dptutorials
Best Laptops to use for better speed:
1️⃣ amzn.to/3lf8zYU
2️⃣ amzn.to/3xejpAW
3️⃣ amzn.to/379OqeL
Best Equipment & Tools for TH-cam Channel : ➡️ bit.ly/3inKa1P
Here in this example, I have the data range in A3:C6, and let us try to transpose this data table into a single column.
Now, I enter the formula in E3 as =INDEX($A$3:$C$6,1+INT((ROW(A1)-1)/COLUMNS($A$3:$C$6)),MOD(ROW(A1)-1+COLUMNS($A$3:$C$6),COLUMNS($A$3:$C$6))+1)
and press Enter.
You will get the result as the value of first cell i.e A, drag this formula till you get an error message #REF!
You can delete the #REF! now you can clearly see that you have got the desired result of transposing multiple column data into a single column data.
Our Recommendations
***************************************************************
Oracle Primavera Tutorials : bit.ly/3fn9PFH
Microsoft Excel Tutorials : bit.ly/2V5de5l
Microsoft Project Tutorials : bit.ly/37guNl7
For Personalized detail learning, write to dptutorials15@gmail.com
If you found this video valuable, give it a like. If you know someone who needs to see it, share it. Leave a comment below with your thoughts. Add it to a playlist if you want to watch it later.
***********************************************
★ My Online Tutorials ► www.dptutorials.com
⚡️LEARNING RESOURCES I Recommend: www.dptutorials.com/resources
⚡️Subscribe for more Awesome Tutorials: goo.gl/NyAtg2
⚡️Support the Channel via shopping: amzn.to/2ZRfTOZ ift.tt/2jH38PR
⚡️Tools for TH-cam vlogging:
***********************************************
• Laptop: amzn.to/2CaLFxJ
• Canon 200D Camera: amzn.to/3d7jDR4
• Benro Tripod: amzn.to/3exQoax
• Microphone: amzn.to/3c5lEvS
• Collar Microphone: amzn.to/2X8DWrS
• Screen recorder: techsmith.pxf.io/2BMjA
• Boom Arm Stand: amzn.to/3extb87
• Zoom H1 Audio Recorder: amzn.to/2TNdHFj
• Harison Softbox Studio Lights: amzn.to/3caGbzg
• Chroma Key Green Screen: amzn.to/2M60mn4
• Background Support Stand: amzn.to/3dbACSv
• Acoustic Foam Background: amzn.to/3gzFtyC
• USB RGB LED Strip: amzn.to/36BpCLF
• Wireless Mouse: amzn.to/2TPIrW7
***********************************************
⚡️You Can Connect with Me at:
***********************************************
TH-cam: / dptutorials
Instagram: / dptutorials
G+: ift.tt/2kAOpa6
Twitter: / dptutorials15
Facebook: ift.tt/2kfRnDi
BlogSpot: ift.tt/2kB14dh
Websites: www.dptutorials.com & www.askplanner.blogspot.com
#dptutorials #Exceltraining #ExcelTricks #ExcelTips #ExcelFreeTraining #ExcelFreeLearning
⚡️Tags: -
excel formulas in English, excel in English, excel tutorial in English,ms excel in English,ms excel tutorial in English, learn excel in English,vlookup in excel in English, learn ms excel in English, excel training, excel tutorial, Microsoft Excel 2007, learn excel,tutorial excel, ms excel tutorial, excel tutorials,ms excel 2007,Microsoft Excel training,learn excel online,learning excel,free excel training,online excel training,advanced excel tutorial,excel tutorial, excel formulas and functions, excel formulas, excel tutorial in Hindi, excel formulas and functions in Hindi, excel tricks, excel in Hindi, excel shortcut keys, excel vlookup, excel formulas in Hindi, excel for beginners, excel for accounting, excel formulas and functions tutorial, Excel Sum Formula, Sum Formula series, excel attendance sheet, excel salary sheet, excel stock maintain, excel data entry, advanced excel tutorial,excel formulas,excel tutorial,vlookup excel,excel accounting,excel for beginners,excel shortcut keys,excel sum formula,excel training,excel training online,excel tricks,free excel training,learn excel,learn excel online,microsoft excel training,attendance sheet in excel,excel data entry,excel formulas and functions,microsoft excel 2007, ms excel tutorial, excel formulas, and functions in hindi, excel formulas and functions tutorial, excel formulas in english, excel formulas in hindi, excel in english, excel in hindi, excel tutorial in english, excel tutorial in hindi, ms excel 2007,ms excel in english, salary sheet in excel, tutorial excel, excel stock maintain, learn excel in english, learn ms excel in english, ms excel tutorial in english, sum formula series,vlookup in excel in english
Thanks a lot. It worked. I've been looking into dozens of websites without getting the right solution. You helped me gather over 5000 values in 40 columns and 128 rows together!. Keep up the good work, and thanks again.
Thanks a lot Mustafa, this is so good of you and it is seriously motivating me to publish more.
@@dptutorials I absolutely subscribe to what Mustafa wrote. Your tutorial here saved my last night when I had to transpose values out of 8-12 columns & 60-80 rows, several tables impacted. I would not have succeeded even if using the Kutools, at least was not able to install it on my company laptop (due to access rights' restrictions, which is fair enough). So one hour later (around 10PM) found your tutorial => pressure released & me relieved. I do not know if a sincere Thank you would reward your time but allow me to Thank you very much. Each problem has at least one solution. It is just great when one is able to find that solution => one less problem on the surface of Earth 🙂 🎉 (the 910th thumbs up is mine, more are just welcome)
@@dptutorials Can anyone help me, i am facing an issue while transforming the table into a single column. there are 11 columns and 1133 rows which i need to transform into a single column, everything is working fine but after the first 11 data is entered , a zero comes after it and then the sequence starts, can anyone help me understand how can i remove the
zero after every 11 data is entered.
You have no idea how much you have saved me. I had over 300 sheets to do this on. Thank you very very much!
Glad I could help!
Honestly, I can't say that I understand your formula, but you have saved me SO MUCH time! Thank you so much!
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos.
While seeing you typing that formula, I smiled and thought to myself. It must be a gentleman from India; only they can have this meticulous math approach to solving problems. Missing you guys!!
Thanks, Keep Smiling. No probs.
THANK YOU! I have been looking for this for a while!
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos.
THANK YOU kind and generous soul. I'm grateful to you and your family
You are so welcome
Using this formula on 16-03-2023, it worked and saved my time ten times. Thanks a billion
You really helped me thank you very much!
Thanks a lot for the feedback.
I welcome you to subscribe this channel to enjoy more interesting videos.
Thanks a lot!!!
I needed this scenario. Thank you
Thank you ! This is extremely helpful
Thank you so much!, this was a time-saver, It will be great if you explain the formula in detail.
Welcome a lot...will try to explain the formula in upcoming videos, just could not explain as it would take long time.
This worked like magic. Thank you so much. I saved tons of hours because of this.
You're welcome!
WOW SAVED LOT OF MY TIME.. KEEP POSTING...
Thanks a lot for the feedback.
I welcome you to subscribe this channel to enjoy more interesting videos.
Thank you so much.... this tutorial help me a lot 🙏🏽
It worked well. Thank you. I would have loved an explanation of why the formula was formed this way to better understand it.
You're welcome!, Sure will explain from upcoming videos.
Seriously thank you, saved me hours of work.
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos.
Hi , thank you for sharing this formula , it saved a lot of time in my case. Keep sharing such videos. 👍
My pleasure 😊
Hi, Could you please advise how you would modify your formula to deal with 4 columns.
This works great, although my sheet has a lot of blank cells which I do not want in the new single column. Can this be amended to allow that?
Excellent video.
Thank you for your work.
Have a great day
Thanks, you too!
Thank you for useful tricks. thanks alot
Massive thanks, dptutorials, saved my skin definitely.
Great to hear!, It is so nice of you.
Sir , I am happy to see this . Really great and excellent
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos
It works. Thanks a lot !
It's awesome. Great Job.
Thank you! Cheers!
Thanks man, you're a life saver!
Happy to help!
Thank you a lot 🙏
Amazing! exactly what I needed !!!!!!
Glad it helped!
Thank you so much. I sound like everyone else but this has saved me hours of work.
Thanks a lot Ryan...
how do you arrange the formula to go down then across instead of across and then down?
Thanks a lot , this formula saved a lot of time. Really appreciate your good work.
Most welcome, please subscribe for more videos.
Fantastic mate!
Thanks a lot
Thanks a lot sir... Can u please tell which formula needs to be used if I want to combine different columns spread across the excel into one column where in the data appears one below the other
Hi Bharat, I din't get your question completely. This is what the video that you are asking.
Superb trick --- you sir are amazing! Thank you very very much --- saved me hours of copying data!!!!
Glad it helped!
Very good and efficient explanation indeed
Glad you liked it
this is awesome. Wow.
Thanks a lot
Thankssssss a lottttt!! After a long search, i stumbled upon this wonderful formula. But i wish there was a provision in the formula to skip blank cells.
I had about 4500 entries spread across 10 columns and 2600 rows. When I used this formula, it also included the blank cells and the length went onto 2600x10=26000 cells!!! I had to filter the blank cells and delete them. But it took a while even for my Galaxy to delete them.
But anyway, the formula was a great relief for me! Thanks a lot again!!!
Thanks a lot for the comment. I will certainly try to find the solution for this in the near future.
I welcome you to subscribe to this channel to enjoy more interesting videos.
@@dptutorials Hello Sir did you find the solution for blank cell or how to remove zero .
please reply
Thanks a lot. It worked
Worked like a magic. Thanks. Subscribed as well.
Thanks for the sub!
THANK YOU! Saves lot of time
You're welcome!
A BIG THANKYOU SIR! You saved half of my my life time.
Great job....thank you...this what something i am looking for long time..very useful tip...i also would be grateful if you could explain this formula to us...
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos.
This is amazing it solved my big problem but with this, I would like to get the numbers in a column like 1,1,1 for A,B,C AND 2,2,2 for D,E,F...can you let me know how to do this
Thanks to you.... You saved me some manual work....!
Glad I could help
Thanks!
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos.
Last few days to im looking for this week type formula, your work are really appreciate thank u sir
You are most welcome
Thank you sir, It helped me in getting stocks OHLC data into single column data. Thank you very much
You are most welcome
Hi, is there a way to complete the same process excluding blanks
Great job.. it worked.. thank you very much
You're welcome!
Thanks a lot my teacher
You are welcome
Hello, thanks a lot. I liked your method. Good job.
Glad it was helpful!
Thanks a lot sir
Most welcome
It's working, Thanks a lot.
Most welcome
It is working with numbers too. Thank You very much. I subscribed your channel for updates
Thank you too...👍
Thank you so much, u saved me
thank you so much
You're welcome!
Thanks alot for the help, this trick work wonders.
You're welcome!
Is there a possibility to use this formula, but in the case of an empty cell, the formula will not consider (will skip the cell) and move forward to the next one with a real number?
Thank you so much for the video, it works.
Glad it helped
thanks dude!
Happy to help!
Is there a way to do this for every Nth row in the range and not all?, for example if I want output to be:
a
b
c
g
h
i
Thank you so much for this. It helped alllllooooooooooot!
Most welcome
Thnx. Nice
welcome
You are very smart. thank you very much :)
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos.
This is so helpful for me today!..
Most welcome
@@dptutorialsSeriously I was struggling on how to go about doing this!. I tried so many methods like adding empty cells and inserting data but nothing worked!. The formula you gave in the Video worked like Charm and saved hours of manual work!. Thanks is very least thing I can say for this amazing helping hand video!..
God bless you
Thank you
You're welcome
You rule!
Wow sir awesome
Thanks a lot
You've just saved thousand of minutes for me.
He is a hero
Thanks a lot for the feedback.This is so motivating
I welcome you to subscribe to this channel to enjoy more interesting videos.
Thanks a lot for the comment Lil Rallo :)
Do you know how to transpose using array formula so we dont have to drag the formula down?
thank you.
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos
Thanks for the video sir.. Saved good amount of time for me...
Glad to hear that
thank you
You're welcome
Sound is terrible, but it magically works. Thanks a lot ! :)
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos.
You are amazing
Thanks a lot Sumit.
Thank You. Here in 2021 and You just saved my fingers 🙏👐👐
Its so special, Welcome.
Thank you, could you advise on how to do this but put it all into one row instead of a column. Thank you!
sure
So useful for my study.
Most welcome
Thank you so much
I had an error then used ; instead of , and worked
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos.
How to transpose or convert singel columm to multiple colums and rows..tks and please
This works for me, except it does not retain the hyperlinks I have in one of the original columns and it also does not retain the formatting, i.e. bold, in other columns. Is there something I can add to the formula to retain the hyperlinks and the formatting?
Thanks a lot for the feedback.
I welcome you to subscribe this channel to enjoy more interesting videos.
Thank you so much, This formula helps me lot
Most welcome please.
Most welcome.
Most welcome.
Most welcome. Please subscribe for more videos
You are an angel sent from God. I love you. (Also if it doesn’t work, use a Mac computer instead of PC. It worked for my Mac, not PC computer)
Thanks a lot..
🌻Thank you very much🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻
You're welcome 😊
Hi, this works great, but how would you increase for more colums, like 5 columns?
Just, we have to tweak the code line.
Thank you for you best video, if from row to column please
Sir Thanks what if there are more A or Gs then how to count besides the list
Thanks
Hi it is awesome video , and do you have a formula to select every nth row and transpose (not to select the next row )
if have idea please help me
Thanks in advance
Regards
SUDARSAN
Sorry, no
Thanks
Welcome
I'm gna try this, well let you know how it goes
Thank you.
my columns have some empty cells .it is storing 0 in all of them .when i delete tham i will show refrence error.and i am unable to move my data to some where else .
kindly help
In this case if A,B,C are headers and i want it to get repeated with every value in the left cell after transpose. Is this possible please ?
this!! would love to know
Any chance you could post a video showing how to do the opposite?
This helped a lot, however if we need the data in row instead of colums then can you please share the formula?
Can you explain reverse order this alignment
Hi this tutorial helps me a lot , but i hope you will help me also continuing the procedure , after this tutorial ,,how can i add the same name like on ABC If it is already done transpose
Hi,
Sorry I did not get your doubt clearly. can you please write it to dptutorials15@gmail.com, so that I can help you in detail.
@@dptutorials i put the details in your fb page . thank you.
Hello , I have a question. My 600 records is in one column & data in 5 rows make one record in a table. I want to transpose all data, in which first 5 rows makes 1 record & next 5 rows as another record. How t do it? Please help.
sir, please help me with how to do the reverse of it?
I mean 10,000 values in one column to 100 rows and 100 columns?
Thanks a lot for the question, I have already posted a video on this, you can check at th-cam.com/video/cSFJi6McI-Q/w-d-xo.html
I welcome you to subscribe to this channel to enjoy more interesting videos.