I've searched and found other ways of doing this, but your approach and delivery was super-simple. Thanks so much for this very useful video.
@ExcelOffTheGrid
2 жыл бұрын
There are many ways to achieve the same result in Excel, so I'm glad you found this method useful. 👍
@AlvinRyellPrada
3 жыл бұрын
OMG THIS IS EXACTLY WHAT I NEED!! you are very straight to the point without any unnecessary remarks and I literally get what I want after following your instructions first time! thanks for making the complex simple for me :)
@ExcelOffTheGrid
3 жыл бұрын
Great news. Glad I could help 😀
@user-yq3ib7nr2e
6 ай бұрын
May almighty Allah Bless You. I have been trying to solve this for three consecutive days now. Thank you so much.
@ck2610
2 жыл бұрын
Dear collegue, this is not what is described in the video titel, instead of combine "rows", it should be named in combine "columns", by the way. Cause I was looking for something which combines 2 rows and I'am getting this :)
@ExcelOffTheGrid
2 жыл бұрын
Sorry, I disagree. This technique is combining rows. So I believe it is the right description. ‘Merge columns’ is a different feature which already exists.
@jhntravel
Жыл бұрын
Amazing good. It took me hours untill I spotted your youtube post. Thank you for posting. Absolutely SPOT ON ! In Dutch: Combineer regels in een enkele cel -> gebruik "Groeperen op"
@ExcelOffTheGrid
Жыл бұрын
Great news, glad I could help.
@dtxgaming9061
3 жыл бұрын
You are my savior. Your method is simple but very powerful.
@ExcelOffTheGrid
3 жыл бұрын
Glad to help :-)
@shankariyer62
2 жыл бұрын
Saved my precious time..God bless you... You don't have an idea how much of a value this video is in my workplace.
@ExcelOffTheGrid
2 жыл бұрын
Time saved … woop woop, target achieved ✅
@kitty1977
3 жыл бұрын
What you just illustrated is power query pivot table basically. So cool.
@ExcelOffTheGrid
3 жыл бұрын
Yes, you could think of it that way. Thanks :-)
@alib4859
Жыл бұрын
Thank you so much 🙏 this is EXACTLY what i needed in my current job i’m so happy i learned something valuable …i couldnt find the solution anywhere on the internet i just found your video by coincidence i subscribe !
@ExcelOffTheGrid
Жыл бұрын
Glad I could help. 😀
@thiagohobi
3 ай бұрын
I've been searching to do this for weeks. Thanks a lot!!!!!!!!!!!!!!!!!!!!!
@ExcelOffTheGrid
3 ай бұрын
Glad I could help! 😁
@nataliamiteva5460
2 жыл бұрын
Have been trying to achieve this for the past hour and couldn't get it to work until I came across this video. Works perfectly! Thanks for sharing.
@ExcelOffTheGrid
2 жыл бұрын
Thanks Natalia - I’m glad I could help.
@user-vz5dx2nz1v
2 жыл бұрын
私がやりたかったことはコレです! 英語は分かりませんが、とても理解しました。ありがとうございます。
@ExcelOffTheGrid
2 жыл бұрын
Even though you don’t understand English, I’m glad you were able to follow the video 😀
@paulskelton8799
2 жыл бұрын
Holy smoke.... I've been looking for this for hours due to issues I'm experiencing pulling data from an old MS Access Db within which some muppet has created LOOKUP fields in a table. Thanks so much!
@ExcelOffTheGrid
2 жыл бұрын
You’re welcome, glad it helped. 😀
@garyjones6397
Жыл бұрын
Superbly clear explanation. Just what I was looking for.
@ExcelOffTheGrid
Жыл бұрын
Glad you liked it!
@brockstonebrink9312
2 жыл бұрын
Very nice! This is much better than grouping all rows then extracting the list. Thank you
@ExcelOffTheGrid
2 жыл бұрын
Thanks, I’m glad you found it useful 👍
@eshmeets
2 жыл бұрын
You were such a savior, I was trying exactly same thing for last few days. Thanks a lot!!!
@ExcelOffTheGrid
2 жыл бұрын
You’re welcome 😀
@natiasurmava3438
Жыл бұрын
Thank you for this video! it was a tremendous help and exactly what I needed. Would be awesome if you will make more videos using Power Query.
@ExcelOffTheGrid
Жыл бұрын
We’ve got lots of Power Query content on our channel, it’s out main focus. So make sure you check it out.
@jamesharper2980
Жыл бұрын
Very helpful for Power BI manipulations as well!
@ExcelOffTheGrid
Жыл бұрын
Absolutely! I'm glad you found it useful. 👍
@traffichazard
Жыл бұрын
A concise explanation of exactly what I needed - very helpful! 🙂
@ExcelOffTheGrid
Жыл бұрын
Glad it was helpful!
@jacknoble491
4 жыл бұрын
Thanks so much, this is perfect solution for the challenge I was facing! Such a plot twist when he was like "we're going to select "sum", knowing full well it will result in an error"
@ExcelOffTheGrid
4 жыл бұрын
Ha ha ha - I like to keep you all on you toes :-)
@kendalllewis3992
3 жыл бұрын
you all probably dont care but does someone know a way to log back into an Instagram account? I was dumb forgot my login password. I love any tricks you can give me!
@kiangus3491
3 жыл бұрын
@Kendall Lewis instablaster =)
@roguenealer
2 жыл бұрын
I want to give you money! You are a life saver!
@ExcelOffTheGrid
2 жыл бұрын
And I'll happy take your money 😀 I'm glad it helped.
@whyskyd
3 жыл бұрын
Was trying to do this in a Power BI transform... this is exactly what I needed. Thank you very much!!
@ExcelOffTheGrid
3 жыл бұрын
That the great thing about Power Query and Power Pivot, while not exactly the same, 99.9% works on both Excel and Power BI.
@whyskyd
3 жыл бұрын
@@ExcelOffTheGrid You should start a Power BI (M) channel 😁
@ExcelOffTheGrid
3 жыл бұрын
@@whyskyd Maybe a Power BI channel is one for the future. I’m not too bad with M and DAX, but the visuals always confuse me. 😀
@servespirit1991
Ай бұрын
Worked perfectly. Thank you :3
@giaca69
5 ай бұрын
Thanks so much, this really help me a lots. Best
@planxlsm
Жыл бұрын
1:15 Merge 2:00 Group By
@kirinde
2 жыл бұрын
Holy shit dude this is freaking awesome. You are a lifesaver. You've got yourself a subscriber over here. THANKS SO MUCH. Short and sweet directly to the point. LOVE IT!!
@ExcelOffTheGrid
2 жыл бұрын
Thanks - glad I could help. 😀
@qungu7251
2 жыл бұрын
That's the easiest solution, thank you!
@ExcelOffTheGrid
2 жыл бұрын
"Easiest solution"... great news !
@thadpanik8933
2 жыл бұрын
I agree with Alvin! This saved me countless hours. Very good video.
@rpopecpa
Жыл бұрын
Great video with an excellent sollution!! I love your technique for combining text using the group by feature. Thanks for sharing.
@ExcelOffTheGrid
Жыл бұрын
Glad you found it useful 👍
@onfootonwheels8758
2 жыл бұрын
Thanks for this. How to do the reverse?
@ExcelOffTheGrid
2 жыл бұрын
Check out this video: kzitem.info/news/bejne/xnmVrYeDe4yIg3o
@mjones53
2 жыл бұрын
Thank you very much!
@ExcelOffTheGrid
2 жыл бұрын
No problem, glad I could help.
@spilledgraphics
2 жыл бұрын
Awesome video and explanation, the backward version of your first one! haha!
@ExcelOffTheGrid
2 жыл бұрын
Exactly. The solution is frustrating because it requires editing the M code to apply a function which should be part of the UI.
@spilledgraphics
2 жыл бұрын
@@ExcelOffTheGrid I agree, it should be 😁🙏
@sumardjo
2 жыл бұрын
thanks for complete to learn because simple file is ready and KZitem to god for practice thanks you very much
@ExcelOffTheGrid
2 жыл бұрын
Good stuff, Thank You.
@muhammadrizky317
3 жыл бұрын
this is what i've been looking for, thankyou very much sir
@ExcelOffTheGrid
3 жыл бұрын
You are most welcome 😊
@umesh2403
6 ай бұрын
Man you saved my day big time
@ExcelOffTheGrid
5 ай бұрын
Thanks - glad I could help.
@ashishshiriskar123
2 жыл бұрын
Superb Sir 👍👍👍👍
@ExcelOffTheGrid
2 жыл бұрын
Thanks 😊
@user-zq3tn9st5k
7 ай бұрын
Thank you. Brilliant real life application
@thrishithravi1976
2 жыл бұрын
Awesome, short and crisp! exactly what I needed
@ExcelOffTheGrid
2 жыл бұрын
Great news, glad I could help. 👍
@elecirlisboa9155
3 жыл бұрын
Excellent, thanks from Brazil!!
@ExcelOffTheGrid
3 жыл бұрын
You’re welcome 😀
@vij2cob
3 жыл бұрын
Great... Effective thing learnt today on excel..
@ExcelOffTheGrid
3 жыл бұрын
Excellent. Glad I could help with that :-)
@StalinSergey
Жыл бұрын
Thank you!!!
@ExcelOffTheGrid
Жыл бұрын
You're welcome!
@wayneedmondson1065
3 жыл бұрын
Hi Mark. Awesome example! Thanks for sharing how to do this.. will come in handy in the future :)) Thumbs up!!
@ExcelOffTheGrid
3 жыл бұрын
Thanks Wayne. 😀
@pharmacyholland3133
2 жыл бұрын
Super! What was I'm searching for. Thanks a lot.
@ExcelOffTheGrid
2 жыл бұрын
Great news - I’m glad you could put it to good use.
@JHallo182
2 жыл бұрын
Thank you.
@ExcelOffTheGrid
2 жыл бұрын
You’re welcome.
@HatecraftPaladin
2 жыл бұрын
Nice, solved a problem for me :)
@ExcelOffTheGrid
2 жыл бұрын
Yay!! Great stuff! Glad I could help.
@louism.4980
Жыл бұрын
Thank you so much! :)
@ExcelOffTheGrid
Жыл бұрын
You're welcome!
@miller2640
2 жыл бұрын
Well done, Mark!
@austinstubetv
Жыл бұрын
Nice one!
@ExcelOffTheGrid
Жыл бұрын
Thank you! Cheers!
@smeems7320
Жыл бұрын
This is brilliant! Thank you so much for posting this
@ExcelOffTheGrid
Жыл бұрын
You’re welcome. I’m glad you found it useful.
@clarealexander7802
Жыл бұрын
Fantastic - great solution
@ExcelOffTheGrid
Жыл бұрын
Thanks - it’s a pretty nice solution once it’s set up. 👍
@TheBayBeeTJ01
2 жыл бұрын
Exactly what I needed! Thank you very much!
@ExcelOffTheGrid
2 жыл бұрын
No problem. Thank you 👍
@Aussiepeoplestats
Жыл бұрын
only 1k likes? this video is a GEM
@ExcelOffTheGrid
Жыл бұрын
Thankfully it’s not about likes, but helping people 😀
@dennismoreno2808
Жыл бұрын
Brilliant. Thank you!
@ExcelOffTheGrid
Жыл бұрын
You’re welcome.
@peterpferdproductions1043
2 жыл бұрын
Brilliant! Thank you very much!😀
@ExcelOffTheGrid
2 жыл бұрын
You’re welcome.
@ironsideo
Жыл бұрын
Wow, he got it....Amazing.
@ExcelOffTheGrid
Жыл бұрын
Hopefully you’ve been able to put the techniques to good use.
@prajwalshetty2047
2 жыл бұрын
Nice Video 👍
@ExcelOffTheGrid
2 жыл бұрын
Thank you 👍
@Rundtj45
2 жыл бұрын
Thanks for the amazing tutorial. How we can do opposite what you did?
@ExcelOffTheGrid
2 жыл бұрын
You mean like this: kzitem.info/news/bejne/xnmVrYeDe4yIg3o
@engahmedelbendary1566
2 жыл бұрын
Realy fantastic
@ExcelOffTheGrid
2 жыл бұрын
Thank you! Cheers!
@lenzypetty9371
3 жыл бұрын
Awesome excellent method and presentation thanks so much!!!!
@ExcelOffTheGrid
3 жыл бұрын
Thanks - I’m glad you found it useful. 👍
@krishck8552
3 жыл бұрын
The best thing I learned today
@ExcelOffTheGrid
3 жыл бұрын
Great news :-)
@PeratchThitarom
Ай бұрын
Thank you so much
@ericulry4743
5 ай бұрын
Now how do you make the separator a "tab" so have two rows in one text box?
@behzadghah
Жыл бұрын
It was fantastic. Thank you
@ExcelOffTheGrid
Жыл бұрын
Thanks 😀
@pminamdar
Жыл бұрын
Thank you so much for your help
@ExcelOffTheGrid
Жыл бұрын
No worries, I’m glad you can use it.
@CarlosJimenez-pb6dq
2 жыл бұрын
Wow thanks a lot!!! Very useful!!
@ExcelOffTheGrid
2 жыл бұрын
Glad it was helpful!
@aaronorelup4024
2 жыл бұрын
Awesome, this was super helpful!
@ExcelOffTheGrid
2 жыл бұрын
Great news 😀
@bartomiejzab6636
2 жыл бұрын
Thanks man!
@chiny-h6445
2 жыл бұрын
I downloaded yr example but my excel doesn't have the "from table range" option. :(
@ExcelOffTheGrid
2 жыл бұрын
What version of Excel do you have? Microsoft recently decided to rename the button From Sheet
@angel29031980
2 жыл бұрын
I want combine only single cell which have different value, if cells have same value, it pass? the cell results do not show duplicate values
@ExcelOffTheGrid
2 жыл бұрын
Using the scenario from the video… create a concatenated column with the First Name, Last Name, Equipment and Equipment Attribute columns. Remove duplicates to create a list of unique items. Then follow the rest of the video.
@joshbrooks2492
Жыл бұрын
How do you do the exact same thing, but with numbers instead of text? I want to combine in one cell a series of number values for a person, separated by a comma and space.
@joshbrooks2492
Жыл бұрын
Doing it this way, it sums the numbers (which I don't want) and making the changes in the code like you have done gives me an error.
@ExcelOffTheGrid
Жыл бұрын
Change the data type to text before applying the Group By step.
@Tat-cx1lr
Жыл бұрын
Merci et bravo
@ExcelOffTheGrid
Жыл бұрын
Le suis content que vous l'ayez trouvé utile.
@benhalicki9749
3 жыл бұрын
Awesome solution!
@ExcelOffTheGrid
3 жыл бұрын
Thanks Ben 😀
@sankalulu
2 жыл бұрын
EXACTLY WHAT I NEED!! Thanks
@JhustynEstevez
2 жыл бұрын
Thank you so much!!! Could you please do it using R o Python?
@ExcelOffTheGrid
2 жыл бұрын
Sorry, Python and R aren’t my thing.
@MrDhunpagla
3 жыл бұрын
Wow.... It's magic & thanks for sharing. Eager for more 😇
@ExcelOffTheGrid
3 жыл бұрын
It is all about knowing the right tricks, so maybe it is magic. 😀
@Tat-cx1lr
11 ай бұрын
Bravo et merci
@ExcelOffTheGrid
11 ай бұрын
Thanks you 😀
@michelledextre7976
2 жыл бұрын
Thank you so much! 💜💜
@ExcelOffTheGrid
2 жыл бұрын
You’re welcome, glad I could help.
@azizdabaz
Жыл бұрын
Amazing! Thanks!
@ExcelOffTheGrid
Жыл бұрын
My please, I hope you can put it to good use.
@seshakv
3 жыл бұрын
Hi Mark, thank you this is super. I have subscribed to your channel.
@ExcelOffTheGrid
3 жыл бұрын
Thanks Venkata - I appreciate your support 😀
@thiagomenezes6968
2 жыл бұрын
excellent 👍👍
@bhanvicom.2014
3 жыл бұрын
Its really awesome, but could you do the same things with dates also? its not working
@ExcelOffTheGrid
2 жыл бұрын
I think you would need to convert the dates to text. It uses a Text.Combine() function, so by definition it would need to be text.
@sergiomira741
2 жыл бұрын
Hi Mark greetengs from Colombia... I have a question.. If I Want concat all values in a cell and after to creat a variable it's posible? For example I have a column named DATA 1 2 3 4 and I want to build a variable named Data_number with all number and to add simple quotes... I would need this result... Data_number ('1','2','3','4')
@kebincui
3 жыл бұрын
thank you very much👍🏻👍🏻
@ExcelOffTheGrid
3 жыл бұрын
No problem, glad I could help 😀
@salvi2606
3 жыл бұрын
Was looking for this.. Will try it soon
@ExcelOffTheGrid
3 жыл бұрын
Great news! Hopefully it worked for you 😀
@salvi2606
3 жыл бұрын
@@ExcelOffTheGrid no I don't have the latest version of excel.. 😢😢 I have 2007 😭😭😭..
@salvi2606
3 жыл бұрын
@@ExcelOffTheGrid OMG this worked.. Uve saved me so much time with this hack.. Can't thank you enough.. I suddenly feel like a genius
@ExcelOffTheGrid
3 жыл бұрын
@@salvi2606 Great news - enjoy the extra time you’ve saved. 😀
@salvi2606
3 жыл бұрын
@@ExcelOffTheGrid yes going to browse through ur other lessons to learn more..
@syednayyar
6 күн бұрын
what cannot we use the text.combine in the first place ? 3:38
@mrkzmusic
Жыл бұрын
Brillaint!
@mjones53
2 жыл бұрын
Thanks for the video, i'm trying to use this method with Power Bi, but, after this when I split the equipment column by the delimiter into their own columns and the data is not equal as in one user doesn't have the same amount of equipment as the other it's messing up the report with invalid data in some columns, any ideas? thanks
@johnosullivan827
4 жыл бұрын
Excellent video and content
@ExcelOffTheGrid
4 жыл бұрын
Thanks John :-)
@saranraj3012
2 жыл бұрын
How to group it same example with numbers
@ExcelOffTheGrid
2 жыл бұрын
I'm guessing that converting the numbers to a text data type first should work.
@anchorpoints5026
3 жыл бұрын
simple and easy
@ExcelOffTheGrid
3 жыл бұрын
Thank you 😊
@the_slidemaster
3 жыл бұрын
Great video dude..! 💐💐👏
@ExcelOffTheGrid
3 жыл бұрын
Thanks. I'm glad it helped you :-)
@cbhiii
2 жыл бұрын
Thank you! Very helpful!
@ExcelOffTheGrid
2 жыл бұрын
You're welcome! Glad I could help 😀
@babs_w2504
2 жыл бұрын
Thank you Mark. That's a great help! What would you do if you get more than 10K lines consolidated in your new sheet? How to show all lines or split into another sheet? Many thanks
@ExcelOffTheGrid
2 жыл бұрын
Hi - I'm not really sure I understand the question. Can you give a few more details about the scenario.
@mowburnt
2 жыл бұрын
I have an address book in an excel sheet that has got a little messed up and wondered if this could help? The table has 15 columns (first name, last name, full name, company etc). The full name column is unique to each person but unfortunately there are duplicate rows for this and even worse, some of those rows have differing data from each other. If there was some way to group all rows with the same "full name" into a single row, and then have each column in that row contain all the unique values seperated by a delimiter such as ";" that would make my job of untangling this unholy mess so much easier. Hope that makes sense? Any help massively welcomed!
@mowburnt
2 жыл бұрын
Essentially what you've done here but repeated across multiple columns
@mowburnt
2 жыл бұрын
Not to worry - with a lot of trial and error i figured it out! What a truly awesome tool! Thanks for the videos!
@ExcelOffTheGrid
2 жыл бұрын
Great work for sorting it out yourself. Broken data is much worse that messy data, as it doesn't always follow any logical structure. Well done.
@Sri-Nivas
3 жыл бұрын
Challenge for you.. Can you do per the below? Row 1 - low - 10 Row 2 - low - 15 Row 3 - low - 20 Need to display like this in a same cell. Row 1 - low - 10 15 20 And not as single line 10, 15, 20.
@ExcelOffTheGrid
3 жыл бұрын
You can use "#(lf)" instead of ";" in the Text.Combine. However this does lead to an issue when loaded into Excel. To resolve this follow the instructions in this post: www.thebiccountant.com/2016/04/16/how-to-display-power-query-results-with-line-feed/
@josephsleiman41
3 жыл бұрын
Can we make the combination go into separate column but same row? If "justin" bought an apple, a laptop and a mouse, I dont want all these to be in one column but to become three different column titled "item 1," "item 2", "item 3". Is that possible?
@ExcelOffTheGrid
3 жыл бұрын
In Power Query, you can either (a) Pivot the data in or (b) enter into a single cell, then Split using a delimiter. Both provide different layouts and may provide what you need :-)
@josephsleiman41
3 жыл бұрын
@@ExcelOffTheGrid Thank you!
@dishitapatel1323
3 жыл бұрын
as a input I get duplicate values also not a unique rows only
@alexdugasse9215
2 жыл бұрын
An error occurred in the ‘Transform File’ query. Expression.Error: The key didn't match any rows in the table. is the error i keep getting
@terryamartey8457
Жыл бұрын
Great
@ExcelOffTheGrid
Жыл бұрын
Thanks 😁
@drakestamatis6322
2 ай бұрын
What if it is a list of numbers, not text? I get an error that the value type cannot be stored as text, since it is a number
Пікірлер: 220