Hi Experts,
I'm looking for help to create a table that tracks new customer sales.
I have data January 2020 to December 2023, including customer IDs, sales, and dates. I want the table to display:
- Quarter-wise sales totals
- Value of new customers in each quarter (those not present in previous quarters of the selected year)
- Value of new Products in each quarter (those not present in previous quarters of the selected year)
Sample file attached.
Thank you so much.
J5 (and J10). Delete all expected results in those rows.
Formula:
Please Login or Register to view this content.
Why are the first 5 rows not counted in J6... when they have not previously occurred either?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi Glenn,
Thank you so much for your precious time and help. i am sorry i skip these, in actual the first quarter values are all will be in NEW because its started.
after that 2nd, 3rd and 4th customers and products will be looked previously if the product or customer is available in previous quarters that sales will not in New.
If any of the customer or product not available in previous quarters those sales will sum in NEW below the Total. (different 2 tables 1 for customer and other 1 for product).
is it possible to extract as per the above criteria.
I need formulas for both, total sales quarter wise and New sales.
Thank you once again.
Last edited by Waqas Gul; 01-06-2024 at 07:55 AM.
If I have understood you correctly, some of your expected answers are incorrect.
Please check carefully.
Last edited by Glenn Kennedy; 01-06-2024 at 01:20 PM.
Hi Glenn,
Thank you so much for taking the time to help me! It's exactly what I needed, and you truly saved the day. I apologize for the late response, I was caught up in a friend's wedding, but I wanted to make sure to send a heartfelt thank you. I am trying to learn your formula to collect the New Product value sum.
If you allow me can I ask for some other help related to the same file (its for my learning) if it is possible. Or I create a new help post for this. My goal is to follow the rules.
*Which formula will help me to indicate/write "New" or "Repeated" after the Value column. to display which products are repeated and which are new.
Thank you so much.
Traditional way, copied down:
=IF(COUNTIF(C$3:C3,C3)=1,"New","Repeat")
Dynamic array (no copying needed - just delete expected results first):
=LET(a,TOCOL(C3:C1000,1),IF(MMULT((SEQUENCE(ROWS(a))>=SEQUENCE(,ROWS(a)))*(a=TRANSPOSE(a)),SEQUENCE(ROWS(a))^0)=1,"New","Repeat"))
I also changed the formulae for NEW in the two tables, so that only ONE range change is needed, if your rows extend beyond 1000 (see file):
=LET(A,A3:F1000,B,FILTER(A,INDEX(A,,1)<>""),IFERROR(BYCOL(K4:R4,LAMBDA(z,SUM(FILTER(INDEX(B,,6),(MMULT((SEQUENCE(ROWS(B))>=SEQUENCE(,ROWS(B)))*(INDEX(B,,3)=TRANSPOSE(INDEX(B,,3))),SEQUENCE(ROWS(B))^0)=1)*(BYROW(INDEX(B,,4),LAMBDA(z,"Q"&1+INT((MONTH(z)-1)/3)&" "&YEAR(z)))=z))))),0))
WOW!!! Thank you so much Glenn, both of the formulas are perfectly working.
Thank you once again.
if you get some time can you please describe the below formula, if you get some time please for learning.
=LET(a,TOCOL(C3:C1000,1),IF(MMULT((SEQUENCE(ROWS(a))>=SEQUENCE(,ROWS(a)))*(a=TRANSPOSE(a)),SEQUENCE(ROWS(a))^0)=1,"New","Repeat"))
Hi Glenn,
Hope you're getting this message with a good health.
Sorry, the formula is perfectly fine working but when I copy the same formula to my another database I got this error. need your help please where I am doing wrong? I copied the same formula you provided me. but the database is huge. (For information: the database is table and the Ship Date from where Quarter wise I need the New and Repeat are not in the sequence) I mean in the start may be Jan-23 after that Dec-23 and mid Apr-22 etc.
Attachment 855942
Thank you so much.
Last edited by Waqas Gul; 01-16-2024 at 02:35 AM. Reason: Some more information add
I cannot view the attachment (it is invalid) so I have no idea...
How huge is huge?
Sorry Glenn, I attached the picture of the error. and the data is till now 210m rows but it will update on monthly basis. and when I copy your provided formula in this database to extract New and Repeat I am getting below error.
It is also possible to do something with pivot table.
210 million rows???? How, when Excel only has 1 million rows.
Is it ONLY the last formula that is crashing Excel?
@DJunqueira no, there's no any pivot.
@Glenn, Yes the last formula which is for getting New or Repeat in your provided file the header is Dynamic and sorry for the typing mistake 210k.
Try:
=IF(C3:C1000="","",IF(SCAN(0,C3:C1000,LAMBDA(x,y,COUNTIF(C3:y,y)))=1,"New","Repeat"))
Someone else asked a similar Q today. Rorya had a very nice solution. Try it. Let me know if it's any good.
https://www.excelforum.com/excel-for...ml#post5910472
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks