Excel Challenge: Sum Marks per subject - REGEX/GROUPBY

[@IvanCortinas_ES]

You always manage to make the easy thing easy and the difficult thing easy. It always seems easy too. It's magical and brilliant. As always, a real spectacle. Thank you, Victor.

[@ExcelMoments]

Thanks for the kind words. I try my best. If it looks more difficult, then it's probably not worth a video 😀

[@weslleyhenrique2989]

Fantastic! Tks for this content.

[@ExcelMoments]

You are welcome

[@andrevanroy3099]

Excellent

[@ExcelMoments]

Thanks for the feedback

[@williamarthur4801]

I'll have to have a play around with this, I did not know I had Regex , last time I tried to use if was still only in beta. thanks again for all your work.

[@ExcelMoments]

Playing around is a great idea. So many use cases

[@chidinduuwaeziozi7360]

Great video, as always!

[@chidinduuwaeziozi7360]

However, I was thinking differently. Doesn't the question have to do with the Student Data? I was thinking the question is to get the sum of all subjects for each student and sort it by subject. Like having three columns, the first is the student name, the second is the subject and the third is the scores and having the GROUPBY function return a total for each student.

[@ExcelMoments]

@ The question is to get the total score by subject; if I said it differently in the video, that's on me. Looking at the expected solution in columns D&E, you will see what is expected. What you have suggested could be another problem that we could choose to solve

[@hagiasofia6179]

A happy coincidence indeed: a nonviolent way to learn more about REGEX might be to revisit some of the challenges and solve them again by using REGEX. Many thanks for that little push.

[@ExcelMoments]

Sounds like a plan. I have some more REGEX coming up. I trust that they would be useful too

[@hagiasofia6179]

@@ExcelMoments Always!

[@DataDashPro]

You are the best and always love and learn soo much from you :)

[@ExcelMoments]

Thanks for the kind words

[@nadermounir8228]

Excelleny Video Victor :)

[@ExcelMoments]

Thanks Nadie for your feedback

[@JoseAntonioMorato]

Dear Momoh, I request that you make more videos with examples of regular expressions. Thank you. 🤗

[@ExcelMoments]

@JoseAntonioMorato Thank you, sir. I have made a few already. I will be releasing them in the coming days

[@B1897forzajuve]

Amazing function and a great explanation. I am still struggling with lambdas, so maybe you can explain to me why this lambda does not work. =BYROW(B3:B7,LAMBDA(x,REGEXEXTRACT(x,"[a-z]+|[0-9]+",1,1)))

[@ExcelMoments]

When you use BYROW, the result should be one value/one cell/column for each row, so in this case, your answer should be 1 cell result for B3, 1 for B4 and so on. But the challenge is that your REGEX expression will split each row into multiple columns, e.g., English45Spain-23 will be split into 4 cells (English 45 Spain 23), and that means 4 columns on that row. BYROW can't do that, it needs some form of Aggregation, like a SUM, COUNT, AVERAGE, CONCAT/TEXTJOIN in the case of texts to ensure it is producing one value per row. Difficult to describe using text

[@B1897forzajuve]

@@ExcelMoments Thank you, so much. No, that was a great explanation. I will keep that in mind. BYROW and BYCOLUMN should produce a single cell result.

[@ExcelMoments]

@@B1897forzajuve So, to complete the thought, BYROW should return 1 column and the same number of rows as the source (N x 1), while BYCOL should return 1 row and the same number of columns in the source (1 x N)

[@jazzista1967]

Great Example Victor...I have been using groupby and pivotby more often lately . Those regex expressions are pretty powerful . How can you learn how to put the pattern in the formula? Thanks for posting !

[@ExcelMoments]

Do you mean learning more about the REGEX patterns themselves?

[@jazzista1967]

yes sir

[@ExcelMoments]

You probably just need a cheat sheet to start getting up to speed on it. For example, media.datacamp.com/legacy/image/upload/v1665049611/Marketing/Blog/Regular_Expressions_Cheat_Sheet.pdf

[@jazzista1967]

Thanks. I did not know about this site. Great resource.

[@ExcelMoments]

@@jazzista1967 Just one of so many. You can check all over the internet, you will find so many of them