Transpose One Column Into Multiple Columns with Excel Macro
Vložit
- čas přidán 4. 08. 2024
- Say you’ve got a file from someone that is an address list. Name, address, city etc. But it’s all in one column. It’s not separated into different columns to easily do any analysis or mail merge. You need to put it into a table with a name field or address field. This also can apply to other types of one column data or databases.
There’s a bunch of ways to transform this type of data, and in this video, I’ll show a quick way to do it by recording a macro. It’s not as scary as it sounds. Heck I’m not VBA expert and I could even do it. And if I can do it, so can you.
0:00 Intro
1:18 Enable Developer Tab
1:40 Start Macro Recorder
2:30 Review & Explain Recorded Macro
3:50 Add VBA to Loop
4:41 Add Another Recorded Macro to Existing
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to
www.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
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!
You're very welcome!
Hi Doug, thank you so much man.
This is what i was looking for a long time.
You are welcome!
Very well explained!
Glad you think so!
This was super helpful - thank you!
Glad it was helpful!
Thanks! thats was so quick, i was writing a super long code for that, i didnt know relative references...
Glad I could help!
Thank you so much sir, because of you my work got so easier.
You’re welcome, glad it helped!
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.
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!
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.
Hi JG EnterprisesNew, thanks for the comment!
Thanks for this idea.
You’re welcome!
Thanks a lot, I appreciate it
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!!
I had always wanted to learn more about VBA but with PQ it’s almost moot 😅
@@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
wow...you ARE a wiz! 😁
@@DougHExcel Thanks! Maybe someday.. still learning one lesson at a time. Having fun with it :)) Thumbs up!!
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.
Genius!
Hi Its all about Home, thanks for the comment!
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...
Awesome, subscribed, cheese
Thanks for the sub!!
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 😞
try power query
czcams.com/video/mvahAXHQvTE/video.html