Forum Discussion
In Search of Advanced Automated Formula to Condense rows while applying Sum
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 the lovely response. I'm trying to use this on my end and I have a follow-up question based off this code. For the Source, you're assigning it the Table 'report', but does that imply that I need to convert my whole excel file to a Table prior to all of this compiling? The data from my software program exports as rows of data, but not pre-assigned in Table form. Thank you.
For the PivotTable you may simply select all data and Insert->PivotTable->From table/Range. Not necessary to name it or convert to structured table.
- Yes, I have now figured out how to refine the data in a concise manner via PivotTable. I struggled a bit to utilize Power Query Editor, but I think at this point I might have been attempting to reinvent the wheel with that route. I was able to create a PivotTable and assign Columns "CUSTOMER" and "PRODUCT" in the "Rows" field box, and then set the Values field box to "Sum of QUANTITY SOLD". I truly appreciate the help and support you provided, along with other community members' support; this makes my monthly reporting of data much easier due to a more streamlined process. Have a wonderful day!
Glad we could help. Cosmetic addition to mathetes solution. In general you may work without the formula, instead select entire QUANITY SOLD column, on ribbon Data->Text to Columns, select Space as delimiter on second space and Finish.
It splits column on two, one with numbers
Select first three columns, insert PivotTable without adding data to data model
Drag fields in PivotTable pane and you are ready
The rest is cosmetic.
Uncheck blanks for the labels filter
When you stay on PivotTable on ribbon Design section has 4 drop-down menus in the layout
From left to right select:
Subtotals->Do Not Show Subtotals
Grand Totals ->Off for Rows and Columns
Report layout->Show in Tabular Form
Report layout->Repeat All Item labels
Blank Rows->Insert Blank Line after Each Item
In another section here, PivotTable Styles, select one which you prefer.
Here unselect these two buttons
On ribbon View->Show uncheck Gridlines button.
Finally we have
All together is few minutes job (after you will repeat the same several times). That's quite simple but not optimal solution. All depends on goals. Each solution has pros and cons.