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.
CodePudding user response：
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!
CodePudding user response：
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.
CodePudding user response：
(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)