I'm not sure exactly how to explain this, but I want to combine rows in Excel while condensing the column data. I'm looking to take a spreadsheet like this:
| Name | Coffee | Bananas | Chocolate Ice Cream |
|---|---|---|---|
| Tom | 4 | ||
| Tom | 2 | ||
| Tom | 2 | ||
| Jerry | 1 | ||
| Jerry | 4 | ||
| Amanda | 3 | ||
| Amanda | 2 |
and turn it into this:
| Name | Coffee | Bananas | Chocolate Ice Cream |
|---|---|---|---|
| Tom | 4 | 2 | 2 |
| Jerry | 1 | 4 | |
| Amanda | 3 | 2 |
Any tips would be super helpful.
You can setup a pivot table for this.
Select your table > Insert Pivot Table, then follow this screenshot:
Drag Name colum in Rows
Drag Coffee, Bananas, Chocolate ice creams in Values and you're set!
If you want the data transformed and cleansed, you can always use PowerQuery.
Go to Data -> Get Data -> From File -> whatever source your data is in.
In there, use the Group By function to suppress the rows.
From there, you'll have a new set of data which can then be loaded into the destination worksheet.
If the usage of PowerQuery is new to you, a quick Google search will help you through this.
Naturally, this assumes you have a version of Excel that has PQ built in.
Here/screenshot(s) refer:
Condensed table comprises 3 sections (colour coded respectively):
note: included an extra '1' for Tom + Chocolate IC in 1st row for testing...
1] Row labels
=UNIQUE(D7:D13)
(array function - requires Office 365 compatible version of Excel)
2] Col labels
=E6:G6
(array function - enter in single cell if using Office 365 compatible version of Excel, otherwise highlight relevant area and enter function with shift + alt + enter)
3] Values
=SUMIFS(INDEX($E$7:$G$13,0,MATCH(J$6,$E$6:$G$6,0)),$D$7:$D$13,$I7)
(can drag this function across values / yellow section)