Forum Discussion
Data Aggregation base on dates
You didn't mention what version of Excel you use. I only develop solutions for Excel 365 which is a very different application from its predecessors (though it is backwards compatible, so I could develop old style formulas if I ever thought that was a good idea!).
= LET(
date, UNIQUE(FILTER(Purchse_date, Product=@distinctProduct#)),
qty, SUMIFS(Quantiy, Product, @distinctProduct#, Purchse_date, date),
n, MIN(ROWS(date), 3),
k, SEQUENCE(1,2*n,0),
idx, 1+QUOTIENT(k, 2),
choice, MOD(k,2),
IF(choice, INDEX(qty,idx), INDEX(date,idx))
)The local name 'date' is a list of dates associated with a single product. 'qty' is the aggregated quantity associated with the product for a given date. The rest of the formula is simply rearranging these arrays into an alternating pattern as required.
I'm bit confused how to use this though.
This should at least demonstrate that the formula does work, even though how it works may remain a mystery. For me the good news is that Excel 365 is a very different app that enables completely different solution strategies. For everyone else, the good news may be that it is backward compatible, so you can keep on churning out the old familiar stuff!
- Thanks for the info ! What I am not clear is , what formula did you write to find the purchase date 1, date 2 , date3 and what formula was used to find respective quantities.
Sorry, I meant to attach the file. The formulas are to be found in cells F5 and G5.
The key elements of the formula are the terms
UNIQUE(FILTER(PurchaseDate, Product=@distinctProduct#)) and SUMIFS(Quantiy, Product, @distinctProduct#, PurchaseDate, date)which return the dates associated with a given product and the change for each product and date respectively. The rest is just to satisfy your formatting requirements.