this is exactly what i was struggling with at work. sql has an easy solution but with excel i couldnt think of a way now i know how to do whats essentially called a cross join
Fabulous, I need some help please. I have two columns ie price and quantity but don’t want extra column of value. So I did =sum(a1:a3*b1:b3) which is ok but once I filter the total value remains same. Subtotal doesn’t have this option, is there a solution for it Thanks
Dear Sumit, Unique formula for combining and splitting the combination. =LET(_combinations,TOCOL(TOCOL(A2:A6&"|"&TRANSPOSE(C2:C6))&"|"&TRANSPOSE(E2:E4)), _divided,REDUCE({"Coffee","Milk","Size"},_combinations,LAMBDA(a,v,VSTACK(a,TEXTSPLIT(v,"|",,1)))), _divided) I hope you like it 🤗
I have an excel sheet consisting of Term1 columns from Class I to VIII as shown below. Adm# Name Eng Hindi Lang Maths Science SocialScience, etc I want to share this single file among 16 persons (Class Teachers) for marks entry subject to condition that a class teacher can do marks entry only for his/her class and could not view or alter the marks of other classes. Once the data is entered in that single Excel sheet then by all 16 persons then I want to dowoad it and generate template specific Report Cards for all the students from class I to VIII? How to accomplish this task?
Very useful - thank you!
Very useful . sir, pls create more videos on power query function, i need to learn more
this is exactly what i was struggling with at work. sql has an easy solution but with excel i couldnt think of a way now i know how to do whats essentially called a cross join
Hi Bro, your all tricks are useful... ❤ hats off
Thank you 😊
I like the Power Query solution. Great video as usual. Thanks.
Great video, Sumit. As you said, I also think Power Query is the easiest way to solve the problem. Thanks for sharing the solutions.
Toujours aussi formidable !😁
Thanks Sir, for sharing such a valuable information. Amazing and awesome
Glad you found the video helpful Azhar 🙂
Happy to see your videos after long years sir! Hope you are doing well! May god bless your teaching journey!
Thank you for the kind words Vignesh 🙂
Need my coffee now! Also I’d love to buy you one. Think I had seen a link somewhere? Can you share 😊
Links available in description section!!!
@ thank you
Wow🎉
A blank as a field in each will give not 3 places but holder of 1- a or b or c
2 placeholder -ab or ac or bc
And your solution of abc
May be byrow will help to solve the problem
I tried BYROW, didn't work
@@trumpexcel is it possible to use recursive lambda
Maybe, haven't tried. Recursive LAMBDA's get complicated very quickly. I have an easier solution to this though. Will share in the next video
@trumpexcel thanks
Use BYROW with TEXTSPLIT to spill in columns and rows at the same time.
Tried it... Doesn't work
Fabulous,
I need some help please.
I have two columns ie price and quantity but don’t want extra column of value.
So I did =sum(a1:a3*b1:b3) which is ok but once I filter the total value remains same.
Subtotal doesn’t have this option, is there a solution for it
Thanks
Can you please let me know how to do this
Dear Sumit,
Unique formula for combining and splitting the combination.
=LET(_combinations,TOCOL(TOCOL(A2:A6&"|"&TRANSPOSE(C2:C6))&"|"&TRANSPOSE(E2:E4)),
_divided,REDUCE({"Coffee","Milk","Size"},_combinations,LAMBDA(a,v,VSTACK(a,TEXTSPLIT(v,"|",,1)))),
_divided)
I hope you like it 🤗
Thanks Jose... The solution I have for this is very similar to yours. Will show that in the video next week.
I have an excel sheet consisting of Term1 columns from Class I to VIII as shown below.
Adm# Name Eng Hindi Lang Maths Science SocialScience, etc
I want to share this single file among 16 persons (Class Teachers) for marks entry subject to condition that a class teacher can do marks entry only for his/her class and could not view or alter the marks of other classes. Once the data is entered in that single Excel sheet then by all 16 persons then I want to dowoad it and generate template specific Report Cards for all the students from class I to VIII?
How to accomplish this task?