No video :(

Transpose a Records from One Column to Multiple Columns

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

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

50% 1

This video shows how to transpose a list of names and addresses that are in one column to to proper table format. There are two ways that are demo'd. One using formulas/functions and the other using Power Query.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 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.buymeacoff...
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

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

20 авг 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

@LotfyKozman 8 лет назад

One of the most favourite and practical videos ever watched in the world of Power Query. Thanks

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

Hi Lotfy Kozman, thanks for the kind words!

@matejarezonja5622 6 лет назад

Great instructional video. Both versions work. If you copy the formula to Excel you have to know what separators your country uses for formulas in Excel. I had to change dots , to semicolon ; and the formula worked perfectly.

@DougHExcel 6 лет назад

Thanks Mateja Rezonja, glad it helped!

@wooster74 5 лет назад

Doug, Took me a while to figure out how the row-column portion of the OFFSET function worked, but once it clicked, it made perfect sense. Got the function to work as advertised. Thank you!

@DougHExcel 5 лет назад

Hi wooster74, glad you liked it, thanks for commenting!

@adrianliauw5941 Год назад

Excellent video. Thank you

@DougHExcel Год назад

Glad you liked it!

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

Exactly what I was looking for.. Except Wat if the column had inconsistent data.. Like say in ur example if for 2 of the entries there was another data like birthday or age.. How do u deal with inconsistent data.. The data I have is product image links.. Some have 5images while some have six.. So how do I add it in one row.. I saw someone use index and match to do this.. Would you be able to explain that

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

did you find any solution for that? i have the same problem :/

@pralhadshetye8863 5 лет назад

Thanks a lot. You saved a lot of time for me.

@DougHExcel 5 лет назад

Thanks pralhad shetye, glad it helped!

@Hairmetal80 6 лет назад

Exactly what I was looking for! Going to have to work more with Power Query!

@DougHExcel 6 лет назад

Hi Hairmetal80, thanks for the comment! Power Query is fantastic!

@troyreese3601 5 лет назад

Really well done. Does exactly what I needed!

@DougHExcel 5 лет назад

Thanks Troy Reese, glad it helped!

@rockguitarist8907 8 лет назад

Good vid! Very cool feature and nice to see two diff methods.

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

Hi rockguitarist8907, thanks for the comment!

@HrobjarturArnason 7 лет назад

Thanks, Very useful, and easy to follow.

@DougHExcel 7 лет назад

Hi Hróbjartur Árnason...you're welcome, glad you liked!

@maspring415 3 года назад

Nicely done.

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

Thank you! Cheers!

@kazimd.mosharrofhossain 7 лет назад

Thank you so much. worth watching. saves a lot of time. Thank a lot Sir

@DougHExcel 7 лет назад

Hi MD MOSHARROF HOSSAIN...you're welcome, glad you liked!

@tungnguyenson1732 6 лет назад

Thank you smch! That exactly what i want, saved my life.

@DougHExcel 6 лет назад

Hi Tùng Nguyễn Sơn, glad you liked it, thanks for commenting!

@kalkun009 7 лет назад

thank so much its really very helpful

@DougHExcel 7 лет назад

Glad it helped!

@MJovad 6 лет назад

What should we do if we have different rows for each entry e.g. if there would be 3 rows for Sally while 5 for Mike, 6 for Puneet etc. We need to show data in front of each name in multiple columns

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

maybe this one can help ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-00ZlGWLAwek.html

@prodanmn 8 лет назад

nice work

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

Hi Prodan Marin, thanks for the comment!

@AniManuSCh 3 года назад

Is there a way to do the viceversa with spill formula on office 365? and with an indirect reference like: a range defined by the user , for example the user in T9 writes E10:F11 then in A1 will be the formula to spill in a single column all the values of E10:F11 that were found.

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

probably with a combination of the dynamic array function, but haven't tried....yet😉

@ganesh13283 5 лет назад

Great instructional video, But I want to Transpose data such has some Heading has 5 rows & some has 10 so How would I do that please help??

@DougHExcel 5 лет назад

more complicated because a pattern needs to be found. You might still be able this but then manually needs to adjust (e.g. cut/paste) those rouge items or find some way to separate the groups of 5 and 10.

@rishabsalian4338 7 лет назад

Hi Doug H, thank you so much, this was what I was looking out for. I used the "demo 1" approach in "Kingsoft Office" which is an alternative to "Microsoft Office Suite" and it works just fine in that software too, so thank you for that. "Kingston Office" is available in the link below for those who might be interested: www.kingsoftstore.com/download-office/index

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

Hi Rishab Salian, you're welcome!

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

My formula is not copying as yours. Its is being copied in other cells as like first cell. Kindly guide me how can I copy it dynamically.

@sktneer 6 лет назад

You could have renamed the column headers in the Power Query Editor itself.

@DougHExcel 6 лет назад

Yep, that's right...something I neglected to do!

@amantin 5 лет назад

davbest!!

@DougHExcel 5 лет назад

Hi Amantin Mehilli - Panos, thanks for the comment!

@kalkun009 7 лет назад

hi doug thanl u for this upload,,, Kindly help with elaboration n demo1. I am trying since last one day on my excel sheet but its not going through... looking forward for ur kind help

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

Hi K U N D H A D I A, sorry I don't do consulting 🙁....but try a post on the mrexcel.com forum!

@rajeshdas78 3 года назад

Is this formula need any special excel version ? Because its not working. Specially the A1 column. Do I need to freeze A1 ?

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

no special excel version needed for the 1st example (formula)

@zendastudio7462 8 лет назад

thanks dough, can u show me how to make combo box with 2 column.

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

Hi Zenda Studio, thanks for the comment!

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

What to do for example if Sally don't have a phone number, Mike don't have an adress etc..

@satyaveerprasad8506 10 месяцев назад

Thanks sir, I want to transpose data like this format Heading A B C Heading1 A B C D Heading2 A B C D Heading3 A B C D E Now need to data below format, that is possible sir, if yes could please help Heading A B C Heading1 A B C D Heading2 A B C D Heading3 A B C D E

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

This one may help ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-PVsfp9sMARY.htmlsi=0v9MVqdibAVJdrDq

@mohamadasyrafhazli7356 5 лет назад

Why did my excel don't have the power query tab ?

@DougHExcel 5 лет назад

If you have Excel for MAC, it's not supported. For Windows, depending on your Excel version, you may need to download from MSFT.

@obito2575 6 лет назад

I want to change a column that has multiple values in each cell. Ex the cell will have Soy, Dairy ,Egg and I want to split those into multiple columns

@DougHExcel 6 лет назад

If there's some delimiters (i.e., period, semicolon) you may be able to separate out with Power Query or Text to Columns feature ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-OjmUbC1XNwc.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-daCclalhOAs.html

@TheChesterfieldBrand_ 6 лет назад

I cannot get I to work. can someone please please send me a file where I can copy the data?

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

Hi The Chesterfield Brand, sorry don't have files to download :-(

@naeemiqbal2327 6 лет назад

Hi, your video is very useful. I want to ask you if you can help me out? I have a worksheet ,which contains 600 names with different type of adress. 1st Row: Company name 2nd Row: First name second name 3rd Row : street address 4th Row: stat and zip code I want to split these addresses into 6 column but the main thing that all address are not containing the same rows;some are contain 4,some 5 and some are in 6 row. kindly advice me how to get rid off?

@DougHExcel 6 лет назад

maybe this video can give some insight ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-tbKL3UFA_eU.html

@kennethvela2784 4 года назад

Can you teach power query?

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

Here's my Power Query playlist ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-NHNnnuOF8kU.html

@jaxpax8054 6 лет назад

to be very honest I had tried multiple time this formula and watched your video too, I am not able to do, please advise me another way. because in this video your already created formula and all stuff in your excel. which is quite not understandable to me.

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

Hi Ashar Khan, thanks for the comment