Forum Discussion
Solved
In Search of Advanced Automated Formula to Condense rows while applying Sum
I have several monthly reports I must submit that require very similar processes to be done to the excel data I generate from software I use. I have to print out the pounds of food sold to customers ...
Check out the attached sheet.
I'd be happy to demonstrate this with a more complete copy, if you are able without violating confidentiality, to post a copy of your actual worksheet. In the absence of that, the following steps were all that I needed to take.
- I used one formula that converted your Quantity to just the values.
=VALUE(LEFT([@[QUANTITY SOLD]],FIND(" ",[@[QUANTITY SOLD]])))
- Then I let Excel do the crunch work by means of the Pivot Table, which is one of the most popular (and powerful) tools built into Excel for summarizing the kind of data you have.
If you can post a more extensive example of your raw data, do so either here or on OneDrive or GoogleDrive, pasting a link here in the latter two instances that grants access.
At the risk of becoming something of a 'one trick pony', since Lambda came out, there are now no calculations that cannot be performed by worksheet formulas. In this case I used
= SumByAttributeλ(quantitySold, attributes)The smaller tables to the right, using the following formulas,
= SumByAttributeλ(quantitySold, product)
= SumByAttributeλ(quantitySold, customer)are just for the sake of showing that the Lambda function can be reused. The functions I defined are
SumByAttributeλ
= LET(
distinct, UNIQUE(attributes),
quantities, VALUE(TEXTBEFORE(quantitySold, " ")),
summed, BYROW(distinct, SumIfsλ(quantities, attributes)),
HSTACK(distinct, summed & " lbs")
)
SumIfsλ
= LAMBDA(distinct,
LET(
ANDλ, LAMBDA(x, AND(x)),
SUM(FILTER(qty, BYROW(attr = distinct, ANDλ)))
)
)Of course, because something can be done, it doesn't necessarily follow that it should be done!
Of course, because something can be done, it doesn't necessarily follow that it should be done!
Amen! [smiley face]
