Sorting, Filtering, and Counting Question

[excelnoobaugust2017]

Hi all,

I recently started using Excel and have been trying to solve a problem involve sorting and counting. I'm sure it's been encountered many times before, but am having trouble figuring it out. I tried searching for answers on the forums, but was having trouble putting my question into searches that yielded relevant results, so I thought I'd try posting my own question here.

Essentially I have a spreadsheet like the one below (albeit much longer!):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Unique ID[/TD]
[TD]Person[/TD]
[TD]Food Bought[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Dylan[/TD]
[TD]Burger[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Dylan[/TD]
[TD]Fries[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Bill[/TD]
[TD]Burger[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Bill[/TD]
[TD]Ice Cream[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Simon[/TD]
[TD]Burger[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]Simon[/TD]
[TD]Burger[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]Matt[/TD]
[TD]Fries[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to sort/filter the different people and count the "food" items they bought so that the data can be organized in a spreadsheet similar to the one below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Person[/TD]
[TD]Burger[/TD]
[TD]Fries[/TD]
[TD]Ice Cream[/TD]
[/TR]
[TR]
[TD]Dylan[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Simon[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

If anyone has any tips/hints/suggestions for how I should go about approaching the problem, it would be much appreciated!

Thanks for the help!
ExcelNoobAugust2017

 

[Peter_SSs]

Welcome to the MrExcel board!

Excel has a built-in feature (Pivot Table) to do exactly that sort of thing. You'll find it on the 'Insert' ribbon tab. Do some research on that and see how you go, but post back with any specific problems you have.
Here is the data and Pivot Table for your sample data

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Unique ID	Person	Food Bought			Count of Person	Food Bought
2	A	Dylan	Burger			Person	Burger	Fries	Ice Cream	Grand Total
3	B	Dylan	Fries			Bill	1		1	2
4	C	Bill	Burger			Dylan	1	1		2
5	D	Bill	Ice Cream			Matt		1		1
6	E	Simon	Burger			Simon	2			2
7	F	Simon	Burger			Grand Total	4	2	1	7
8	G	Matt	Fries
PT

 

[excelnoobaugust2017]

Hi Pete_SSs,

Thank you for the help, this was exactly what I was looking for to help point me in the right direction!

Best,
ExcelNoobAugust2017

 

[Peter_SSs]

You are very welcome.