How To Transpose Or Convert A Single Column To Multiple Columns In Excel || dptutorials
HTML-код
- Опубликовано: 25 апр 2019
- In this tutorial, I will show you How To Transpose Or Convert A Single Column To Multiple Columns In Excel
DON'T CLICK THIS: ➡️ bit.ly/3sPIZvD
Link to download the exercise file: ➡️ bit.ly/3j92hay
Best Laptops to use for better speed:
1️⃣ amzn.to/3lf8zYU
2️⃣ amzn.to/3xejpAW
3️⃣ amzn.to/379OqeL
Best Equipment & Tools for RUclips Channel : ➡️ bit.ly/3inKa1P
There can be multiple ways to perform this trick, I will show you some of these in this tutorial now.
So, let us go to the 1st method:
1.Enter the formula in Cell C1 as =INDIRECT(ADDRESS((ROW($A1)-1)*3+COLUMN(A1),1))
2. Now let us see the 2nd way of doing this:
Using OFFSET Function
Enter the formula in Cell C1 as =OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS($1:1)-1)*3,0)
3. Now let us see the 3rd method of doing this by VB Code:
➡️ VBA Code:
Option Explicit
Sub movetocolumns()
Dim i As Integer, iRow As Integer
Dim arrSource As Variant
'Set the first row
iRow = 1
With ActiveWorkbook.Worksheets("Sheet1")
'get the data into an array from the first column
arrSource = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
'parse every value of the array and add the data to the next column
For i = 1 To (UBound(arrSource) - UBound(arrSource) Mod 3) Step 3
.Cells(iRow, 3) = arrSource(i, 1)
.Cells(iRow, 4) = arrSource(i + 1, 1)
.Cells(iRow, 5) = arrSource(i + 2, 1)
iRow = iRow + 1
Next i
'add the remaining values
Select Case UBound(arrSource) Mod 3
Case 1 'one item to add
.Cells(iRow, 3) = arrSource(i, 1)
Case 2 'still two items to add
.Cells(iRow, 3) = arrSource(i, 1)
.Cells(iRow, 4) = arrSource(i + 1, 1)
Case Else 'nothing to add
End Select
End With
End Sub
Now go back to the excel sheet and from the view tab, click on view macros and run the macro called “movetocolumns”
That’s it friends.
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 RUclips 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:
***********************************************
RUclips: / 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: -
1 column,one column,multi column,Into,dptutorials,tutorial,how to convert column to row in excel,leila gharani,Convert A Single Column To Multiple Columns,How To Transpose Or Convert A Single Column To Multiple Columns In Excel,single column to matrix in excel,single column into a table in excel,column into array,transpose single column into multiple rows,convert single column into,one column to multiple columns in excel,single column to multiple columns excel
I want to thankyou from the bottom of my heart for not only making this video but also sharing the formula in the description box of the video. As I have seen other videos where youtubers show the formulas & VBA codes as to be written to run the macro but don't give it in the description box. You have really made my life simpler by sharing the formula in the description & it worked for me. You have saved quite a few hours of mine due to this as I had 336 rows data to be converted to columns width of 10.
Thanks a lot for writing in so detail. In fact this is the longest and best comment that I have received till date. You will definitely be my favorite visitor and subscriber for sure. Happy to connect with people like you to get motivated further.
@@dptutorials Thank you for your kind words. It is your video & knowledge that made me write this to thank for your efforts.
@@ridhimabhatia.bhatia it is only because of people like you I am getting motivated to publish and share some knowledge that I have. I am happy to connect with positive people like you. You can write me at dptutorials15@gmail.com for any query or solution.
@@dptutorials sure. Thanks
@@ridhimabhatia.bhatia Hey can you help me i have got 9Lakhs rows to be done.Please help me.
Thank you so much. You and this tutorial saved our days. I lost almost one day in searching for ways to do this task with no hope. One of my friends accidentally came across this and shared with me. This is just awesome. Thank you!
Great to hear! and glad it helped. Would like to help you more.
BRILLIANT !!!!!! Thanks very much for posting three different options, with such clarity. (The second one worked for me.) I came to this video from your other one which was the reverse. It was very helpful that the two videos were linked. You saved me an enormous amount of time, as I couldn't find this explanation anywhere else.
Glad it helped!
THANK YOU!! I tried other videos and for some reason they didn't work. yours did and was VERY easy to follow. GREAT WORK!! :D
Thanks a lot for the feedback. This is seriously motivating.
I welcome you to subscribe this channel to enjoy more interesting videos.
Thank you so much for this!
Is there any way to tweak the formula so that the values are copied in order from top to bottom instead from left to right??
Thank you it saved a lot of time. Special thanks for sharing the formula in the description box of the video. VBA codes is absolutely perfect.
Glad it helped!
Very good video. THANK YOU, dptutorials! you saved us a LOT of time!
You're welcome!
thank you. the visual basic method was just the thing i needed.
I don't know how to thank you. This was the most helpful video. Man, you saved me!
Glad to hear it!
Thank you sir, for sharing such a Valuable information, this I was looking exactly to distribute single column data to multiple column and rows.
Thanks for Superb explanation.
Brilliant!! Thank you!
This is really helpful!!!
Works 100%. Thank you for the great ideas.
You're welcome!
THANK YOU! This worked for me!
You're welcome!
Thanks, great help!
You're welcome!
Thankfully.. God bless you...
how do a I get the order to be vertical instead of horizontal? I want get 6 columns from the long one I have but keep vertical order of numbers and not horizontal like in the video!! please help
Very helpful thank you 🙏
Hi I like the video tutorial very much. Thank you for showing
You’re welcome 😊
Well met! That was great
you are awesome. thank you!
Hello do tutorials
I have 2 columns with PIN and Serial numbers. I want to combine the two in the form where pin is on top then serial number below but in one cell. I need to do this for like hundreds of cells. How do I do it?
Thank you so much.
Most welcome
thank you so much. This video is really useful.
Glad it was helpful!
btw, very helpful and clearly explained the output.
Glad to hear that!
You are awesome. this saves a lot of time.
Glad it helped!
I am looking for this trick from many days finally I got it.
thank you so much for this !!
You're so welcome!
thanks this video helped me
Glad to hear it!
amazing, nice tutorial
Thank you! Cheers!
Thank you bro,U make my work so easy 🙂
Thanks a lot for your feedback
Thanks a lot, it's really help me doing statistics task
Glad to hear that. You are welcome 😊, Glad it helped you. Please support my channel.
HLO DEAR I HAPPY TO OPEN THIS VIDEO .KNOWING GUD THING IN DETAIL .I M NEW TO USE EXCEL AND M INSEARCH OF SUCH A GUD KNOWLEDGE .U HAVE TOUGHT IN BRIEF,M THANKFULL TO U AND WANT TO KNOW MORE ON THIS TOPIC .I WANT TO KNOW IF I WILL CONVERT THE DATA INTO 9 OR 10 COLOUMNS INSTEAD OF 3.THEN WHAT SHOULD I DO .
BUNDLE OF THANKS FOR THIS
Thank you much really helpful
Most welcome
Thank you for this formula.
I was trying to replicate this if the column with data started from C5 to let’s say C30. I was not able to find a logical change needed in the formula to capture the data from that row. Would it be possible for you to explain the logic of all the variables please? That would be really helpful. Thank you 🙏
You are welcome
really useful!!
Glad it was helpful!, Thank you Pragya kant.
Brilliant Sir G, This was a lifeline for me Thanks Bro again
My pleasure
Thank you, you help me so much
Happy to help!
Indian guy making my life so much easier
Sure, Could you please guide me to make better monetization.
www.buymeacoffee.com/dptutorials
thank you for help me out.
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos.
Thanks .... it is a very helpful to me...
You are most welcome
Thank Sir important formula 🎉
Thanks a lot
Happy to help
Thankyou Somuch Bro..
Most welcome
Thank you for you video, it was a big help. The truth is I found the third way simpler. One question though. If I want the columns to be let's say 10, what should I change in the code?
Will revert post i get a solution.
Sir, very nice. Thanks
Most welcome
Excellent got formula in 3 minutes of video 🎉❤
Great to hear!
Is it possible to do same for single row into multiple rows
Thank you! This solved an issue I was having with converting a long list of clients into a 3-column list. Can you tell me, however, how would I make this list read alphabetically until the end of the page then start the next client in the middle, then the final in the 3rd. I'm looking to do this:
AAA BBB CCC
ABC BBC CCD
ABD BBD CCE
ABE BBE CCF
vs. your tutorial above, which is:
AAA ABC ABD
ABE BBB BBC
BBD BBE CCC
CCD CCE CCF
Thanks a lot, please write to info@dptutorials.com, we can definitely help you on this if we understand it more clearly.
You r fabulous .
Thanks a lot
Thanks
Welcome
Hi, Can you please help me in my query, these 3 function or formulas, return only text values what if that text value have the hyperlink, I need exactly what you are doing here but my data contains text in some rows with hyperlinks but those are getting removed.
Thanks
Vishal GOel
Thanks for writing, Please send me your excel sheet to dptutorials15@gmail, so that I can understand the query in detail.
Thank you. But how to make it 2 under 1 instead of on the right side ? thank you
very very thanks
Most welcome
I have 9 column that contains employee data 3 employee. Like emp1 in 3 column and emp2 in next 3 column and emp3 in next 3 column.
And emp 4 in first 3 column in next row and so on. How to do to mix data so 1 employees data in single row.
Okay, so I have tried using VBA code & In my case data in 5 rows(both numeric & alphabets value) make 1 record. I need to know where to change that number from 3 to 5 in VBA?
Same difficulty
I have query similar in such a way that I transposing from row to column but, my rows are not fix in number. Would please help me to solve my daily routine work , I have my data in rows , I want to transpose into 12 column,(Jan-Dec) my rows does not have fix number of month, -for example, I have Jan sale 38900/, then March 25689
All I want put to value in Jan, Mar. and skip sale value for Feb. Is it possible to have exel formula or VB code so I can put in just like you did for this vedio.
I want to convertits reverse i. e. multiple columns values into single colum.
Pls, how do I convert from multiple rows and columns into two columns
Option Explicit
Sub movetocolumns()
Dim i As Integer, iRow As Integer
Dim arrSource As Variant
'Set the first row
iRow = 1
With ActiveWorkbook.Worksheets("Sheet1")
'get the data into an array from the first column
arrSource = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
'parse every value of the array and add the data to the next column
For i = 1 To (UBound(arrSource) - UBound(arrSource) Mod 7) Step 7
.Cells(iRow, 3) = arrSource(i, 1)
.Cells(iRow, 4) = arrSource(i + 1, 1)
.Cells(iRow, 5) = arrSource(i + 2, 1)
.Cells(iRow, 6) = arrSource(i + 3, 1)
.Cells(iRow, 7) = arrSource(i + 4, 1)
.Cells(iRow, 8) = arrSource(i + 5, 1)
.Cells(iRow, 9) = arrSource(i + 6, 1)
iRow = iRow + 1
Next i
'add the remaining values
Select Case UBound(arrSource) Mod 3
Case 1 'one item to add
.Cells(iRow, 7) = arrSource(i, 1)
Case 2 'still two items to add
.Cells(iRow, 3) = arrSource(i, 1)
.Cells(iRow, 4) = arrSource(i + 1, 1)
.Cells(iRow, 5) = arrSource(i + 2, 1)
.Cells(iRow, 6) = arrSource(i + 3, 1)
.Cells(iRow, 7) = arrSource(i + 4, 1)
.Cells(iRow, 8) = arrSource(i + 5, 1)
.Cells(iRow, 9) = arrSource(i + 6, 1)
Case Else 'nothing to add
End Select
End With
End Sub
How can I use this for any text
Hello and thank you so much. What if I don't want the numbers transposed.
Thanks. Will check and revert
you are a devil, dracula who saved my time you made my day!! long live my dear devil
Thanks a lot..
I learnt a lot
if i have to convert it to multiple columns in another sheet how the reference should be?
INDIRECT(ADDRESS((ROW(Sheet1!$A1)-1)*17+COLUMN(Sheet1!A1),1)) ?
But this did not work for me.
Need to work.
Thank you very much dear 🙏🏻
But what happened with two columns in to multiple columns ?
Let me check
Bro how to do this with criteria or condition
🔥
1:05 , 2:22 : what is the meaning of "locking". i have never heard it before, and seeing how u emphasised it, it seemed crucial. So, any resource to learn more about it?
Any way, nice video. Thanks a lot for sharing this :)
ohhw, got it. locking means when "dragging" the function - the locked thing wont change. ohkay. cool. niice
Its called actually as absolute referencing.
@@dptutorials ahwwkayh. good to know. thanks for sharing :)
Will this formula also work if the colum no is unequal. I.e. like 1,3,7,9,13,55
yes please, you can try.
Good
So nice
Attachment is possible , I will put attachment, any mail id..
How to make the first 10 in column c and the next 10 in column d ?
Sir, suppose I want to transpose the data into 3 columns, the values will be transposed to 3 columns, but if I extend my columns number towards right, the next 3 values are being pasted which are also pasted in the 2nd row. I want a function, where if I choose 3 columns, the data should only be pasted in 3 columns and the columns next to the 3rd column should be either blank or zero. Please help
Hi,
Please share your working file, so that I can understand better what exactly you are saying, @dptutorials15@gmail.com
@@dptutorials Sure, I'm sending you now. Please sir make a formula for this. It will really help me a lot. I have been searching for a solution and finally on your channel found a trick which 90% meets my requirement. If you help me figuring out the small issue, it will make my work much more easier. I am very happy that you addressed this problem. Thank you Sir.
Sir column is changed to row is possible but row to column is not possible why ?
Many days one problem i m face.
Only one column many contact add, how to separate mane column separately accordingly.please .
What if you want the numbers to transpose down the columns instead of across the rows?? PLEASE HELP
sorry, i did not get thequestion right, could you please explain in detail at dptutorials15@gmail.com
@@dptutorials I was just wondering how to get the numbers to count down the columns instead of across the rows.
What you showed:
1 2 3
4 5 6
7 8 9
What I was looking for:
1 4 7
2 5 8
3 6 9
When I delete the original columns the converted rows turn into "O". Help
Because the output columns depend on the original columns, you can't simply delete them. What you can do is copy ALL the output you want, select a place to paste it (I use a separate sheet bc I like to keep the original input for double checking), when you right click to paste(do not use the hotkeys to paste) select "Paste Special" and then pick Values only. You should now have columns that are no longer dependent on original (so deleting them won't effect these). Please note, changes to original will not reflect in this "value only paste", so if you change the original, redo the steps above :D
Help me out...
How to add diffrent units in a single coloumn..?....if data will like
Wheat-in KG
Milk- in Litre
Keyboard jn pcs
Please drop me the excel sheet at info@dptutorials.com
how we used this formula in different sheets
If all the sheets have the data in the same column, then you can select all sheets at once and type the formula in a cell.
Vba code where find?
How to use concatenate formula multiple cell for the whole month
I have a doubt, single column data having different rows (eg. rows a1 to a10 is a one record, this has 10 rows like this some data having 11 rows) which is also available in the single column a1, how to resolve this pls give a solution
Let me check
Worked like a charm for me 2 this one: =INDIRECT(ADDRESS((ROW($A1)-1)*3+COLUMN(A1),1)) I think i should get a tattoo with it on my arm or somewhere else on my skin ... i don't know ... somewhere to be easily seen/found/understood :)) A big (y) from me :D
Thank you.
How to do the other way round. Converting 3 columns into 1 column?
There is already a video on this topic in my channel
dptutorials can you share the link of the video
How to multiple columns convert in multiple row by VBA ??
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos.
How to convert two columns in to single row do u have any idea
Yes, it can be done.
How to make it virtically number 2 is down number 1
Want to thank you first..
But please tell if it is row what will do
Let me do a video on this as well.
There are 20 members in 1 column and there are 10 sir names in 2nd column, 4 are unique, 6 are duplicates, using which formula to put sir names with 20 members?
Error; c, d.e (1) are getting pasted to c. D,e (2)
how about transposing 5 columns into 4 multiple columns?
How to do vice versa
Hi, Please find the video for the same. ruclips.net/video/OG_yNrQNJ6g/видео.html
okay, so, a li'l help to u from my side :) paste the following text *_as it is_* in description, and youtube will pick these up as video chapters. So, will help the viewers :)
---
0:00 Introduction
0:26 1: "Indirect" function
2:11 2: "Offset" function
3:30 3: Using VBA
4:18 Outroduction
Thanks a lot brother.
WHAT IS THE FORMULA IF CONVERT SINGLE COLUMN TO 8 COLUMN & FIRST COLUMN SUPPOSE TO BE DATE COLUMN... PLSE HELP
This is for 1 column. Then how it where it is in 2 columns
Okay, this 20 digit row line , than how in convert.
How to do single colum to multiple row,
Example A coloum 1, 1,1,1 ,2,2,2,3,4
B coloum John, Santosh, Chandru, Shazeeb
C coloum 10, 20, 3,4,5,7
Extra, now I need row Vs coloum
Sir But Isame Font Colour change ho Jata he Red Colour ho to formula Lagane ke Bad Black ho jata he
Only horizontally you've shown, what about vertically.?
imgur.com/a/INTKRJS
how to be like picture with vba ?
How to convert multiple columns