Transpose One Column Into Multiple Columns with Excel Macro

Подписаться 78 тыс.

Просмотров 12 тыс.

50% 1

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

Опубликовано:

14 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 35

@QuizzerBox Месяц назад

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

@paulbanville4923 2 года назад

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!

@tanveerbarmare3946 6 месяцев назад

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

@DougHExcel 6 месяцев назад

You are welcome!

@bernardabraminobibas3222 Год назад

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

@DougHExcel Год назад

Glad I could help!

@eds.2237 Год назад

This was super helpful - thank you!

@DougHExcel Год назад

Glad it was helpful!

@nischalsingh1068 2 года назад

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

@DougHExcel 2 года назад

You’re welcome, glad it helped!

@user-rz3jr8qv7b 8 месяцев назад

Very well explained!

@DougHExcel 8 месяцев назад

Glad you think so!

@isi12345 2 года назад

Thanks a lot, I appreciate it

@DougHExcel 2 года назад

You’re welcome!

@pak-mcqs2857 Год назад

Thanks for this idea.

@DougHExcel Год назад

You’re welcome!

@jgenterprisesnew9552 2 года назад

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!

@wayneedmondson1065 2 года назад

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!!

@itsallabout5823 9 месяцев назад

Genius!

@DougHExcel 9 месяцев назад

Hi Its all about Home, thanks for the comment!

@tristanhejny9452 Год назад

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!

@SaniGarba 2 года назад

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!

@user-bn6il2qm6u 3 месяца назад

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.

@forcabarca5319 2 года назад

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...

@makubexho Год назад

Awesome, subscribed, cheese

@DougHExcel Год назад

Thanks for the sub!!

@java2architect 9 месяцев назад

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 9 месяцев назад

try power query ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-mvahAXHQvTE.html