Excellent!! Was breaking my brain trying to figure this out -- great solution!

Thank you for sharing the macro, you saved my tons of time, liked and subscribed !!

Mate you are a absolute legend. Keep doing what you're doing!

Thank you for sharing this macro. It saved me so much time. I'm so glad I found it. Thank you!

Thank you! I learned how to do this in college and could not remember how to program in VBA for the life of me. I really needed to do this again and your video helped so much.

An absolute game changer this is!! Your method/macro has been the only one that has solved the issue every other method was presenting for me! As an Excel noob, I had no idea how to fix the issue I was having myself so I was having to rely on looking up other people’s videos on the topic of transposing. Each time I tried their suggested method, the data in the column I wanted to transpose did not properly transfer over. As in my original data all had underlying hyperlinks/urls that opened the web pages once clicked upon even though the data was in friendly name format (if that makes sense). Each time I transposed I would lose the hyperlinks and things transposed over as plain black text without the urls in them. This has been the only way my data has been able to be transposed fully unaltered from its original state! Hope I made sense there, I’ve no idea what proper terminology is used to describe what I’m trying to explain, haha. Thanks a billion! This made my job a whole lot more efficient!! Wonderful content, keep up the great work!!

Thank you , Thank you - kept looking on how to do this type of transpose! Great job.

Absolutelly great tutorial. My fingers appreciate so much not having to copy/delete transpose several tens of lines. Great!!!!!😀🤝

Hello Jon, Thanks a lot. It's really good.

Seriously I was searching this since yesterday now I got it... Thanks a lot... :)

Hi there! Great video! You made it quite simple to understand! Thanks. Question: what if the 'X Number' of rows to copy and transpose varies and is conditional? In other words, instead of copying at every 3rd or 5th row, you need to copy and transpose rows, until you hit a row that says in the cell "go to this site" which repeats going down the data set in different places?

Super helpful! Thanks.

Nice clean solution. Thanks for the insights.

Thank you for this excel-lent work!

thanks. It solved my problem. Great job

Really liked the tutorial. Will like to know to apply this to multiple columns without having to select it one after the other to have it transposed.

I freaking love you dude.

Thank you very much this helped a lot

thank you for the columns to rows example

Can we do vice versa like transpose columns to X number of rows

Hello, so how would you do the exact opposite? from the second table to the first long list?

Your video is very useful but I have an query you had run macro in 5 coloum but if I have different different type data like 2,3,4 any other when what we do?

Excellent thanks

Thank you so much for your nice job. I have only a request if possible please. How to make this macro work in the oposit direction from right to left and thank you again !!

Very Powerful and useful trick revealed with well explained vba flexible code, Thanks a Lot Sir !!!

@TeachExcel

4 жыл бұрын

You're very welcome! I'm glad I could help:)

How to do this with Google Sheet or Numbers for Mac?

how to remove white space along with this code you have written in your video help me soon

Hi thank you for this video really amazing, I’m trying to do exactly the opposite of what you have done. So my info is in a row and I want to put it in a column. Name and four dates. I need the name to appear next to each date and when I transpose I only get every fourth row so I guess it’s got to do with the loop.

can anyone help me with VBA code written here ?? unable to download the file and code is not completely visible else i would have written it

Great video, how do i access/download the macro used for this? I can't find it on the website- is it something i need to pay for?

can i used for after blank go to the next row ?

How to do if it us from rows to one column then count as well? Thanks

Hello :) Thanks so much for your explanation. I have another issue I was hoping you could help with. In addition to adjusting the number of rows to transpose, I'd like to also set the number of columns. Is there a modification that can be applied to the macro to accomplish this? For my data specifically, I need every 36 rows transposed to columns (which your video has helped me to do), but each of those 36 rows consists of 3 columns of data. Hopefully that makes sense. Thank you in advance for your expertise and willingness to share it :)

Can we make the stepvalue = to a specific string ?? Or ( word)

Great tutorial.. love the explanations of the logic in VBA. Good teaching and learning! Thumbs up! PS - By the way here is a formula I wrote to unstack the data in your Example 2, starting in cell D1: =OFFSET($A$1,(COLUMNS($D$1:D1)-1+(ROWS($D$1:D1)-1)*5),0). If you write this formula in D1 and copy it down and right to column H, it will do the same as your macro. If you have data items 6, 7, 8, etc. then modify the "5" at the end of the formula to adjust for more columns. That constant could be made to be dynamic in several ways, but most of the time when unstacking data, you know how many columns you need and so just as easy to hard code it into the formula. This is just another way to solve the problem.. I totally dig the VBA method too and Power Query could also do it. I like to try to solve things with formulas first, as some clients are paranoid and don't allow macro enabled workbooks onto their networks and PQ requires the extra step of refreshing. Each method has its own merits and appropriate use profiles. Thanks again for the inspiration to work on this problem!

@TeachExcel

5 жыл бұрын

Thanks for the tip! It's very nice to have multiple ways to do it and input! I was not sure if I should cover the formula in this tutorial or not, but I chose not to for fear that it would confuse people. I was thinking to include formula solutions in another tutorial as well, but I'm not sure if people will want that. I am very interested in showing the Power Query version of this though. On a side note, its kind of funny, but, now that I know macros pretty well, I, for my own work, will end up making a macro to do something instead of a formula sometimes, especially if it deals with array formulas; funny how that is.

@TeachExcel

5 жыл бұрын

And I'm glad you liked the VBA explanation! I worry that I talk too much on that topic, but I always hated it when someone would give me some code and just assume that I understood everything haha

@wayneedmondson1065

5 жыл бұрын

@@TeachExcel Thanks.. I have another formula design that uses INDEX instead of OFFSET. INDEX is actually better to use, since it is not volatile. On smaller data sets, it doesn't matter. I've actually studied the Power Query method. The steps are: 1) Load the data set to PQ; 2) Add an Index Column; 3) Add a Standard Column - Divide by Integer and specify the integer that matches the number of data points in each record; 4) Transform the Index Column using Standard-Modulo and again specify the integer that matches the number of data points in each record; 5) Select both the Index Column and the Integer-Divide Column and Pivot specifying the original data column and Don't Aggregate; 6) Remove un-needed columns from the pivoted data and rename the remaining columns appropriately; 7) Close and load to a new worksheet or existing worksheet; 8) Add new data and refresh as needed. For very large data sets, PQ is the way to go.. or your VBA method. For small data sets, I like the formula method.. simple and direct. Thanks again.. learning a lot from your channel and web site.. glad I found them. Thumbs up!

@ebonygonzalez842

4 жыл бұрын

@@wayneedmondson1065 None of your formulas work

@wayneedmondson1065

4 жыл бұрын

@@ebonygonzalez842 Hi Ebony.. so sorry it is not working for you. Not sure why. I just re-created the data set in A1 through A20 and then copied my formula from above into cell D1 and then copied down and over to H4 and it worked perfectly.. same result as the macro example in the tutorial. So.. not sure why it won't work for you. If you want to send me your email address, I'll email you my example. Otherwise.. maybe try again on your own. Good luck!

Thanks a lot

wonderful

How can we do it other way around?

Hi Sir, In the above transpose data script, could you please change the stepvalue to specific text instead of a number with wildcard operator so that row range should transpose the data to that specific text value it should then move to next row one that text value recognized....pls give the code

How would you transpose based on a word in a column?

Can you do this for three columns of data? Transpose won't work because I have too many rows.

Totally enjoyed this lesson; I played it multiple times. I do have an important question though, and it is time sensitive - and I'm not sure if you are around to get back to me. If you are, please let me know - either way, great video!

Hi thank you for this video ii's been excellent training for me, but I am interested in Copy and Transpose data from workbook "A" to another workbook "B" with both workbooks open, I would like to use a Macro for this, can you help please. Regards, Don

@TeachExcel

5 жыл бұрын

Hi there, go ahead and ask in our forum and upload your sample files and we can take a look at it :) www.teachexcel.com/talk/microsoft-office?src=yt

I saw you added a command to give a pop up box to enter the number of rows to include each time but the vba has an embed number value for how many columns to move over to insert the transposed data. Can you share the code and notes to get a similar popup box to enter a number value in how many columns over to enter the transposed data. Also does it matter where you insert this code within

@TeachExcel

5 жыл бұрын

Just ask this in the forum on TeachExcel and include the macro from this sample file and either me or someone else can show you show; it's a pretty easy addition to the code but it doesn't work well to share macros here. Here's the forum: www.teachexcel.com/talk/microsoft-office?src=yt

this seems to be the best solution for my issue but i have failed to get the downloadable file for this very lesson. Anyone to help me🙏🙏🙏🙏🙏🙏

Can you link a video that will show someone how to measure exponential growth on Excel. For example if you start with 10,000, and have growth of 7 a day oh, and then add that 7 to the ten thousand, two then start off the next day with 10,000 and 7 and experience the same percentage of growth with the new base number. Is there any way to measure growth on that scale where Excel will automatically add the products of that growth to the base number and continually grow as in a snowball effect? Or do you need to manually continuously enter the numbers? I'm I'm pretty sure I used to know how to do it but I forgot how

@TeachExcel

5 жыл бұрын

I'm not sure I will be putting that into a tutorial here any time soon, but if you ask on the forum we can figure out a solution that works well for you. If you upload a sample file there, it will be easier to help :) www.teachexcel.com/talk/microsoft-office?src=yt

For lay people like me in relation to VBA, I use Power Query. Thanks for the example.

@TeachExcel

5 жыл бұрын

Glad to help! I'll look into making a tutorial for Power Query to do this as well)

@josephfianko8954

3 жыл бұрын

@Luciano how do you use power query to do it or can you point me to a tutorial. Thanks

@Luciano_mp

3 жыл бұрын

@@josephfianko8954 kzread.info/dash/bejne/Z4N6k5SBfdu8pc4.html

@Luciano_mp

3 жыл бұрын

@@josephfianko8954 kzread.info/dash/bejne/Z4N6k5SBfdu8pc4.html

@josephfianko8954

3 жыл бұрын

@@Luciano_mp thanks

How do we do that vice versa? transpose columns to rows? Subscribed!

@TeachExcel

3 жыл бұрын

Hi Bec, and thanks for the sub :) I don't have code to paste here in the comment, so I'd say to ask this in our forum and provide a description of exactly what you are trying to do and a sample of the data and include the code from this tutorial if you want to use that as a model or base, and it will be easier to get you a solution :) Excel Forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment

i added your code ,didnt not work ,maybe its not complete ,but i would love to see all the code you have in this video

I get an error 1004 despite following the exact code. How can I solve this?

@kylesmyth1867

2 жыл бұрын

Same here, did you get a fix?

Sub Transpose_N_Rows() Application.ScreenUpdating = False xRow = Selection.Rows.Count XCol = Selection.Column nextRow = 1 stepValue = InputBox("How many rows should be grouped together?") For i = 1 To xRow Step stepValue Cells(i, XCol).Resize(stepValue).Copy Cells(1, XCol).Offset(nextRow, 3).PasteSpecial Paste:=x1PasteAll, Transpose:=True nextRow = nextRow + 1 Next Application.ScreenUpdating = True End Sub I'm inputting this and I'm getting an error Run-time error '1004': PasteSpecial method of Range class failed

@ellengunnarsdottir9109

2 ай бұрын

Same here, would love to use this code and get a solution for this error message...

@vladimirsterlin9078

12 күн бұрын

xlPasteAll not x1

Man...I was just about to sign up at your site for a "free" account...until you asked for billing information. You do have a lot of cool stuff at your site, though.

@TeachExcel

2 жыл бұрын

Gomi, sorry about that, you do NOT have to pay, just navigate away once you get to that page, the programmers just have to add the ability to be re-directed back to the tutorial you came from after you signup - and they haven't done that yet.

@gomisaburo6544

2 жыл бұрын

@@TeachExcel ... that's actually just what I did. Thanks. Again...lots of great stuff on your site. By the way, do you do free lance programming for web-based applications?

@gomisaburo6544

2 жыл бұрын

Is Excel your primary skillset? Or...do you have any other programming prowess?

Thank you very much for sharing, easy and fun to watch. Doesn't help me though with a huge dataset I'm working with that has repeated school names on the first column and the rest of the columns is the ones that need transposing. The extract is here if you can advice on that. Acacia Ridge State School Spelling Year 5 459 Acacia Ridge State School Reading Year 5 465 Acacia Ridge State School Numeracy Year 5 437 Acacia Ridge State School Grammar and Punctuation Year 5 435 Acacia Ridge State School Writing Year 5 424 Acacia Ridge State School Writing Year 3 373 Acacia Ridge State School Grammar and Punctuation Year 3 373 Acacia Ridge State School Reading Year 3 383 Acacia Ridge State School Spelling Year 3 367 Acacia Ridge State School Numeracy Year 3 346 Agnes Water State School Reading Year 5 491 Agnes Water State School Numeracy Year 5 473 Agnes Water State School Grammar and Punctuation Year 5 487 Agnes Water State School Spelling Year 5 452 Agnes Water State School Writing Year 5 434 Agnes Water State School Grammar and Punctuation Year 3 351 Agnes Water State School Reading Year 3 389 Agnes Water State School Writing Year 3 384 Agnes Water State School Spelling Year 3 370 Agnes Water State School Numeracy Year 3 362 Aitkenvale State School Spelling Year 5 436 Aitkenvale State School Reading Year 5 429 Aitkenvale State School Numeracy Year 5 432 Aitkenvale State School Grammar and Punctuation Year 5 418 Aitkenvale State School Writing Year 5 404 Aitkenvale State School Writing Year 3 387 Aitkenvale State School Grammar and Punctuation Year 3 388 Aitkenvale State School Reading Year 3 373 Aitkenvale State School Spelling Year 3 394 Aitkenvale State School Numeracy Year 3 356