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.
@@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.
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!
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.
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
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
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??
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?
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.
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 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?
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
Hi. I dont know if my comment woyld be answerd.i have a coloumn in which there's a name, below it a number, below it a name, below it a number and... How can i seperate numbers from names and put each number in front of the name above?!
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.
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
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 🙏
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?
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
@@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
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?
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
@@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.
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 :)
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
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
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.
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 sharing, u made my life easier, may god bless you and ease everything you do!
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.
thank you. the visual basic method was just the thing i needed.
Works 100%. Thank you for the great ideas.
You're welcome!
Hi I like the video tutorial very much. Thank you for showing
You’re welcome 😊
Very good video. THANK YOU, dptutorials! you saved us a LOT of time!
You're welcome!
I am looking for this trick from many days finally I got it.
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!
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
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.
Excellent got formula in 3 minutes of video 🎉❤
Great to hear!
I don't know how to thank you. This was the most helpful video. Man, you saved me!
Glad to hear it!
Thank you so much.
Most welcome
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 so much. This video is really useful.
Glad it was helpful!
well done and many thanks
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
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??
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!! 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.
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!
Thanks very much
You are welcome
You r fabulous .
Thanks a lot
amazing, nice tutorial
Thank you! Cheers!
Well met! That was great
You are awesome. this saves a lot of time.
Glad it helped!
Thankyou Somuch Bro..
Most welcome
Very helpful thank you 🙏
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.
Brilliant!! Thank you!
you are awesome. thank you!
This is really helpful!!!
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.
thanks this video helped me
Glad to hear it!
Thanks!
Thanks a lot Don Juravin
Thank you. But how to make it 2 under 1 instead of on the right side ? thank you
Brilliant Sir G, This was a lifeline for me Thanks Bro again
My pleasure
THANK YOU!!!
thank you sooo much..
Thank Sir important formula 🎉
Thankfully.. God bless you...
Thanks
Welcome
Thanks .... it is a very helpful to me...
You are most welcome
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
Thank you, you help me so much
Happy to help!
Thank you much really helpful
Most welcome
very very thanks
Most welcome
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
Is it possible to do same for single row into multiple rows
How can I use this for any text
Thanks a lot
Happy to help
Sir, very nice. Thanks
Most welcome
thank you so much for this !!
You're so welcome!
Hello and thank you so much. What if I don't want the numbers transposed.
Thanks. Will check and revert
Bro how to do this with criteria or condition
Hi. I dont know if my comment woyld be answerd.i have a coloumn in which there's a name, below it a number, below it a name, below it a number and... How can i seperate numbers from names and put each number in front of the name above?!
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.
After the offset or indirect command we cannot copy and paste the data to new sheet. it shows #REF!
Thank you very much dear 🙏🏻
But what happened with two columns in to multiple columns ?
Let me check
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.
I want to convertits reverse i. e. multiple columns values into single colum.
Many days one problem i m face.
Only one column many contact add, how to separate mane column separately accordingly.please .
THANK YOU! This worked for me!
You're welcome!
btw, very helpful and clearly explained the output.
Glad to hear that!
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 bro,U make my work so easy 🙂
Thanks a lot for your feedback
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
really useful!!
Glad it was helpful!, Thank you Pragya kant.
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.
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
Thanks, great help!
You're 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.
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.
Good
So nice
How to make the first 10 in column c and the next 10 in column d ?
Vba code where find?
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
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?
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.
Want to thank you first..
But please tell if it is row what will do
Let me do a video on this as well.
How to make it virtically number 2 is down number 1
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.
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 :)
How to convert two columns in to single row do u have any idea
Yes, it can be done.
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
WHAT IS THE FORMULA IF CONVERT SINGLE COLUMN TO 8 COLUMN & FIRST COLUMN SUPPOSE TO BE DATE COLUMN... PLSE HELP
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.
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.
This is for 1 column. Then how it where it is in 2 columns
🔥
Attachment is possible , I will put attachment, any mail id..
Only horizontally you've shown, what about vertically.?
Sir column is changed to row is possible but row to column is not possible why ?
how about transposing 5 columns into 4 multiple columns?
How to use concatenate formula multiple cell for the whole month
Okay, this 20 digit row line , than how in convert.
How to do vice versa
Hi, Please find the video for the same. th-cam.com/video/OG_yNrQNJ6g/w-d-xo.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.