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.
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.
- Thank you for your response. I played around with PivotTable more, and I was able to get my table looking very much like yours. I placed the columns "CUSTOMER" and "PRODUCT" in the Rows field box, and I set the Values field box to "Sum of QUANTITY SOLD" and the data has been refined to be concise and without the formatting I was trying to remove. I really appreciate your help and support as this makes my work much easier to perform now. Have a wonderful day!
- You're very welcome. The Pivot Table has for about three decades, maybe more, one of the most useful tools Excel (and Lotus 1-2-3 before) offered. It does the heavy lifting that used to require a lot more work on the part of the user. All you need is a well organized table of data.....and some idea of how you want the data tabulated.
