You Saved Doug. Struggling from 2 days to get This one Thank You Soo much. Appreciate your work.

Thank you Doug. This was my first macros. Unlike so many others, you took it slowly and explained it very well!

@DougHExcel

2 жыл бұрын

You're very welcome!

Very well explained!

@DougHExcel

8 ай бұрын

Glad you think so!

This was super helpful - thank you!

@DougHExcel

Жыл бұрын

Glad it was helpful!

Thanks for this idea.

@DougHExcel

Жыл бұрын

You’re welcome!

Thanks! thats was so quick, i was writing a super long code for that, i didnt know relative references...

@DougHExcel

Жыл бұрын

Glad I could help!

Thank you so much sir, because of you my work got so easier.

@DougHExcel

2 жыл бұрын

You’re welcome, glad it helped!

Hi Doug, thank you so much man. This is what i was looking for a long time.

@DougHExcel

6 ай бұрын

You are welcome!

Thanks a lot, I appreciate it

@DougHExcel

2 жыл бұрын

You’re welcome!

Hi Doug. I love VBA.. but thank goodness for Power Query and worksheet formulas.. right!! Thanks for the code demo. Thumbs up!!

@DougHExcel

2 жыл бұрын

I had always wanted to learn more about VBA but with PQ it’s almost moot 😅

@wayneedmondson1065

2 жыл бұрын

@@DougHExcel Hi Doug. Just for fun, below is a VBA UDF I wrote to unstack a single column of data to a table with dimensions (number of columns) of your choosing. Copy it into a module and then you can call it from the worksheet like any other function. The first argument is the single column range of data you want to unstack. The second argument is the number of columns into which you want to spread or transpose the data. If the range of data is uneven in relation to the number of columns specified, you will get 0s in the cells with no data. Also, requires O365 to SPILL the results, just like a dynamic array function which basically it is, just created through VBA. This was a fun exercise, inspired by your video. I'm no wizard at VBA, but I like to try and solve things as they come up. So, took your video as a challenge. Hope you and or others find it useful. Let's keep on learning :)) Thumbs up!! Function Unstack_Column_To_Table(rg As Range, myCols As Long) As Variant Dim cl As Range Dim i As Long, j As Long, myRows As Long Dim arr As Variant myRows = Application.WorksheetFunction.RoundUp(rg.rows.Count / myCols, 0) ReDim arr(1 To myRows, 1 To myCols) i = 1 j = 1 For Each cl In rg If j > myCols Then j = 1 i = i + 1 End If arr(i, j) = cl.Value j = j + 1 Next cl Unstack_Column_To_Table = arr End Function

@DougHExcel

2 жыл бұрын

wow...you ARE a wiz! 😁

@wayneedmondson1065

2 жыл бұрын

@@DougHExcel Thanks! Maybe someday.. still learning one lesson at a time. Having fun with it :)) Thumbs up!!

Genius!

@DougHExcel

8 ай бұрын

Hi Its all about Home, thanks for the comment!

Good video. I personally use a tool called ASAP which has this transpose function already built-in but I like the macro to clean up the blank cells. I will test it on my next project.

@DougHExcel

2 жыл бұрын

Hi JG EnterprisesNew, thanks for the comment!

I didn't do anything like this becasue I don't think the data selections lined up, but I goofed and backed it up wrong and am going to do it again. I'm trying this time. I guess I'll just add lines for things to line up if I need to.

Hi Doug! Super helpful and informative video, and it has helped me a lot in building my first couple of macros. I had a quick question on how you would code the macro to not stop at an empty cell, but rather have it copy and paste a number of times equal to a number in another cell (i.e. you only want to repeat the process for the first X addresses, so you type X into cell B1 of your excel sheet and the code will repeat that many times). If I’m not very clear I do apologize, just starting with macros and I might be attempting to bite off more than I can chew, but thank you in advance!

Thanks Doug. We appreciate your modesty regarding knowing little of Macro operations🙂. Just one question, if you could look into it, what happens when we add more of the same data to the one-column list and want to run the macro? Thanks.

@DougHExcel

2 жыл бұрын

Thanks for the comment! I tried to add one more record after the 1st run and it will duplicate the records whilst appending it to the last run. Not ideal. So it would be best remove the output records in the next columns, add the new records and run the macro. Sure there's a way to update the VBA to append new data to the existing output but I'm not savvy enough to do it. Still learning!

Awesome, subscribed, cheese

@DougHExcel

Жыл бұрын

Thanks for the sub!!

how to make it if not all data complete, for example sometimes street is missing, or zip code missing etc ... I mean for the first row 4 columns, for the second row 5 columns etc...

I need to do rows to rows AA#BB to AA1BB AA2BB AA3BB Is it possible for me to record macro or something to do this? I have several such rows in table 😞

@DougHExcel

8 ай бұрын

try power query kzread.info/dash/bejne/n6qVyqOReLTchKg.html