How to Transform Multiple Row Data into A Single Row

[@rootsmanuel]

This video is worth thousands of hours = millions of euros. Thank you very much.

[@ExcelExciting]

do you know that you can even achieve this with a formula !! have you tried it ?

[@rootsmanuel]

@@ExcelExciting yeah, I did! It worked perfectly. Thank you!

[@Prometeo59]

This is pure gold. Absolute and total gold. Thank you so much! This has saved not only my sanity, but also days of work, and probably failure

[@yasmeennajeebi]

this is absolutely amazing. you're my hero and you're doing god's work. thank you Faraz!

[@ExcelExciting]

Thank You. Glad to hear you like it 😃

[@rajender721]

it worked for me and solved my problem. Good bless you Faraz.

[@dawnlamothe2480]

Extremely helpful! I was able to adapt your instructions to my school's data of combining parent first names from different rows onto one row. Thank you so much for the time, effort and expertise in providing this video!!!

[@ExcelExciting]

Fantastic! Glad to hear that it was helpful, do you know that there is formula version to get the same results without using Power Query.

[@GhospelSoulz]

Actually had to create a KZbin account just to comment that you’re the King!

[@ExcelExciting]

Thank You.. stay connected for more exciting tips on Excel

[@jameskurgat9614]

I have never seen such genius way of manipulating Excel. You are the guru!

[@sibautube]

Buddy, you are a gem!! You saved my 4 day's work time in only a few seconds. I am thankful to you a lot. I don't have any word to mention here.. I have subscribed ur channel and liked it for the best video.

[@ExcelExciting]

Thank You Siba.. glad to hear you find it helpful, also feel free to check the latest video requested for formula version working with Excel 2016 & 2019 or Excel Online. kzbin.info/www/bejne/iaTQYZWYa7uKq7s

[@suryatej7865]

Awesome help man, thanks for the video.

[@ExcelExciting]

Welcome dear

[@naveedkingdom]

Thanks brother, you have saved my time

[@aditisplayworld5853]

Thank you for the video..I was searching for a solution and yours is a magic..Saved lot of my time...

[@ExcelExciting]

You're welcome! glad to hear you find it helpful 😃

[@ashok1981]

I thank you very much for this awesome solution!! I was working on a huge data as such and it almost took me 5 to 6 days, but now after you providing the solution I was able to complete in few minutes.. Thank you again!! Really appreciate, :)

[@ExcelExciting]

Great to hear it was helpful. how more this can be helpful for you ?

[@tilodenys7534]

Awesome video. After >3 hours looking how to get all the tags related to a company in multiple rows on one row to import my clients database, you finally solved my case at 11 PM. HERO!

[@ExcelExciting]

Thanks Tilo, glad to hear its was help... stay connected to catch up more amazing tips & tricks.

[@khalidmajeed2886]

Mashallah .typical problem but explanatiion in a very simple technique..........hal min mazeeed

[@josealtamirano9585]

This has saved me hours!!!!!!!

[@tipiditytips6422]

Thank you so much for sharing your expertise🎉

[@SSSJS320]

I’ve done this in SQL,and it is not easy to do. This is much easier!!!! Thanks!!

[@ExcelExciting]

You're welcome! Glad to hear you find it helpful =)

[@venke75utube]

You saved my time to explore this. Thanks a lot

[@ExcelExciting]

Glad to hear it! if you are looking for formula version then check this video link kzbin.info/www/bejne/iaTQYZWYa7uKq7s

[@garylhaas2005]

amazing - this has got me pondering about similar "hacks" to standard code

[@nowshadulalam6569]

BOOM...extremely good

[@ExcelExciting]

💥 yeah !!! Have you tried doing with formula ?

[@JoseGarciaV7]

You are amazing! Thanks a lot :)

[@ExcelExciting]

Glad to hear you find this helpful thank you very much

[@rajkiran226]

Thanks is the small word for this video…❤

[@TimWatsonitstim]

Well done! What a great solution!

[@ExcelExciting]

Thank You

[@gudboy9675]

Thanks for saving so much time, great work.

[@ExcelExciting]

Glad it helped! have you seen the formula version ? if not feel free to check this link kzbin.info/www/bejne/iaTQYZWYa7uKq7s

[@adinathtepugade4404]

Outstanding .....Thank you man

[@ExcelExciting]

Welcome buddy.. what was dataset about so you need to do this ?

[@nmalhome20]

Thank you so much! Your video helped me a lot and saved hours of work

[@ExcelExciting]

You're welcome! glad to know you find it helpful...

[@kmadhuvenu]

Thanks man, really it's very helpful and saved lot of time

[@ExcelExciting]

Awesome 👏🏻!!! Now you know how to deal with crap data.. you are a Excel ninja 🥷!!

[@tipiditytips6422]

Wow thats awesome🎉❤

[@shashikantshimpi9532]

Thank you for very easy solution

[@ExcelExciting]

Welcome, what your challenge with your data ?

[@k5kensails]

WOW! Amazing. Thank you my friend.

[@ExcelExciting]

Welcome buddy 😃

[@omzy980]

Thanks alot. this trick really solved my problems and saved huge time and made my life easier. Brilliant !!!!

[@ExcelExciting]

Glad it helped! Awesome !!!!

[@pragatiingale5440]

Thank you so much🙂 you saved my time

[@ExcelExciting]

Welcome 😃

[@pianobeginnersofficial2734]

You are superb dude really expert

[@ExcelExciting]

Thank You 😊

[@mysticalmatterss]

Looking for this since long time, Thanks a lot bro

[@ExcelExciting]

Welcome !! Glad to hear you like it

[@prajwalshetty2047]

Nice Video 👍

[@ExcelExciting]

Thank you.. was your query resolved?

[@prajwalshetty2047]

@@ExcelExciting No Sir, When I convert PDF Bank statement into Excel in Excel 2013. I get particulars of single transaction in more than one rows and there are more than one transaction in single date so I can not use the down fill option in power query. Some time I get more than one transactions for various date in one cell.

[@sp3820]

Hi this is great I need to put details in multiple rows into multiple columns for example I have pay and allowance data relating to employees in multiple rows but I want all the pay and data in columns with each column labelled as the allowance name can u pls help. So for example to put make and weight etc in independent

[@najibullahbaig]

Thank you so much sir for sharing such a wonderful trick and solution, can we have a excel formula to text in one line, in office I have a 2010 version excel where power query is not working.

[@lenkapolakovicova2749]

THANK YOU!!!

[@bannochang]

hi Faraz , Liked your Videos ,Do u teach Excel pls let know ,want to learn the high end techniques in excel , working in Excel application extensively tricks like this saves lot of time .for Eg till now I have been doing this function by copying the rows in notepad and from notepad to Excel cells . Need some help in knowing the high end tricks and techniques

[@ExcelExciting]

Sure, I do provided training on Power Query..

[@zahidmajid8508]

great video.Mashallah.Allah increase your knowledge.more more more

[@ExcelExciting]

Jazakallah :) Glad You like it.. thank you..stay connected for more amazing tips

[@FAYR71]

Hello Faraz, i would like to learn on how to trasnform mutilple row data (under product description) into a single row for a bank statement. Bare in mind the bank statement will contains 4 coulumns made of (date , description, debit, credit).

[@TTHN2012]

Is there a way to do this in Office 365 on a Mac?

[@ExcelExciting]

Hi David, at the moment it is not possible. But work around is to build you solution in windows and you can refresh the Power Query on mac. Excel for Mac support Power Query refresh. Microsoft Engineers are working hard to get it for mac and they already have UI first phase >> check video for more details kzbin.info/www/bejne/hYu0l56sqpyAiZI When all Mac Power Query feature generally available to all the public is unknown

[@aryanbagri2090]

Thank you very much sir its very very important I have also same problem

[@ExcelExciting]

Glad to hear you find the video helpful 😃

[@aparnapassi1489]

thank you so so much

[@ExcelExciting]

Welcome.. glad to hear you find it helpful

[@preciousoneon1]

woow.. awesome

[@ExcelExciting]

Thanks a lot 😊

[@jameskurgat9614]

How will you combine cells that have texts with dates within them?? It doesn't work here because the Text.Combine code looks like it works on texts only and not dates. I tried to format the dates to texts but it doesn't work

[@ExcelExciting]

That is interesting one, I got your query. Could you please share OneDrive or google drive of your same data.. this will help me to better understand..

[@mohammedismail9844]

Good thanks

[@ExcelExciting]

Welcome

[@Sekhar_Home]

can we get the result based on excel formula. i mean without POWER QUERY

[@ExcelExciting]

This might be helpful for you kzbin.info/www/bejne/iaTQYZWYa7uKq7ssi=rbP0B1PWe1coT-Nw

[@andrevevle]

this video solved a problem with a crossreference list of 250.000 lines of products. it was cleaned down to 5500 lines after the job was done :-) THX! :-)

[@ExcelExciting]

WOOW!!! That’s sound awesome 👏🏻 Glad to hear your job was done ✅

[@sp3820]

@@ExcelExciting Hi this is great I need to put details in multiple rows into multiple columns for example I have pay and allowance data relating to employees in multiple rows but I want all the pay and data in columns with each column labelled as the allowance name can u pls help. So for example to put make and weight etc in independent

[@RowenSpeed]

For whatever reason when i do this, It groups, but all the combined text is the same for every entry below. Not just the top entry. So all customers would have the same info as the first combined text. I don't know why this happens. I can't find any information either.

[@JNYC-gb1pp]

What if you had an excel spreadsheet with many customers, some of whom have multiple products with multiple order numbers and price. You want to send them a letter in a word doc that has a table that shows each product with price, and order number. Currently I'm assigning the fields in Words mail merge but I get either multiple letters to the same person with a table full of the same product or when i use some of the rules in Word, I end up with products that don't correspond with the customer. I'm having a problem figuring out how to denote in Word that I want them to fill out the first row in the table with the same customer info, and then use the next row for the next item and so on. How do I get columns B, C, D for client name in Column A into a word doc table where I get a letter for Clients in column A and puts the info in columns B, C, & D

[@ramawisnu1106]

thank u very much!! but how to make each point with - ..... -...... to parahgrapgh like: - ...... - ...... in one row?

[@ExcelExciting]

That’s a good one.. could share me sample dataset ?

[@D-cn5lw]

Awasome bhai jaan, got some data sorting issue is there any way to help me ?

[@ExcelExciting]

Sure please visit my website forums and feel free to post your query www.ExcelExciting.com/forums

[@prajwalshetty2047]

Sir I used separate Power query to append Gross sales of various Branches to find total Gross sales and Sales return of various Branches to find total sales return in two different sheet, in one sheet total Gross sales and in other sheet total sales return. To find Total Net sales, I manually reduce sales return from gross sales. Sir is there any way to do this all calculation in power query itself. In Microsoft office professional Plus 2013.

[@vivek5255]

Nice 👍

[@ExcelExciting]

Thank you! Cheers!

[@rajeshraval1233]

Hello sir can we create fields from one array fields suppose i have one fields in that i get data like {"name" : "abc";"title":"xyz"} i have to create name as field and abc is value of that field same for title

[@muhammadarif8006]

Excellent thanks a lot Sir 🇵🇰

[@zumarali5301]

Hi Sir, This is precious! But i am trying to find the solution for the vice versa procedure, from single row with long data to multiple row. A reversal process of what you did in this video. Can you help me with this?

[@ExcelExciting]

Good question, you can split by delimiter into row from the advance setting in PQ.. I will soon upload a video.

[@bhagyasenadheera1297]

@@ExcelExciting Hi sir, Can you explain this?or did you do a video on this?

[@michaelfebrian9070]

extremely helpfull !! thank you so much, but i want to ask how do i make separators become "enter" so it can be easier to read ? can anyone help ?

[@michaelfebrian9070]

nevermind i got the answer.. thanks if you guys wondering the answer is using this query Text.Combine([Description],Character.FromNumber(10)) cheers !

[@rukmanmecca]

Great video Sir! But I am not able to find group advanced option in my office 365.

[@ExcelExciting]

It will be on your home ribbon.

[@vrindasarkar4545]

Freakin mind blowing.

[@ExcelExciting]

Thank you Vrinda :)

[@sjayanand]

What if you had multiple recordset for the same product code and you wanted them to appear in different rows?

[@MiyIndia]

Thanks a alot bro:)

[@ExcelExciting]

Welcome

[@krishnadasalamgottil91]

Is it possible to combine numbers like this by separating comma

[@ExcelExciting]

Yes.. this is possible you need to convert the numbers to text & then combine it.

[@jyothikulkarni2961]

Sir how to do if it give vice versa

[@rickyleekakit8773]

Great music

[@trusharpatel337]

can i do this in excel????

[@safashaikh4089]

Excellent good ❤️👍

[@kinahhalim8482]

Thank you, it really helps me. But is it possible for me to list down the data downward in a same row and not by using " - " Example NO : Book - Pencil - Eraser - Case YES : Book Pencil Eraser Case

[@ExcelExciting]

Glad to hear it helped you.. Yes.. use "#(lf)" in place of -

[@muhammedasifkhurshid1156]

v.nice, thanks and regard.

[@ExcelExciting]

Thanks Muhammad

[@Su_Desh]

These options are not available in my excel

[@alinaflorence3091]

any option to do this in Azure Data Fatory?

[@ExcelExciting]

This article from Microsoft will be helpful for you docs.microsoft.com/en-us/azure/data-factory/control-flow-power-query-activity

[@asifpatel8205]

Good job

[@wenyinglim5946]

wow! what if i want to combine them into 1 cell but they are in new line for each?

[@ExcelExciting]

this could be possible.. important part is the grouping of the data

[@wakeelshah]

Is it possible to filter a product number and multiple rows of product description appear with it, if so kindly help

[@ExcelExciting]

Good Question & answer is yes, it possible. first to change the column data type to Text & perform grouping action. The function Text.Combine() allows only to combine only text values.

[@excelemployeeleavetracker1274]

This was is solid video UNTIL you played music...then you went into GOAT Mode (Greatest Of All Time) ...Slow Clap my Friend...Well Done!!

[@ExcelExciting]

Thanks, I like it GOAT mode 😃

[@RohitSingh-ym1ho]

how can one combine numbers instead of adding them up?

[@ExcelExciting]

Yes, we can also do that..

[@RohitSingh-ym1ho]

@@ExcelExciting HOW THOUGH?

[@MrBumbum2812]

Thank you machan

[@ExcelExciting]

Welcome 👍

[@zahidmajid8508]

exactly the same problem i have,but i can't solve it according your direction.how can i send y a problem.Jazakallah.

[@ExcelExciting]

you can connect me via facebook

[@shuhabgujar8372]

Nice video

[@ExcelExciting]

thanks