Logic Help: Transforming data to pivot

[rando123]

Hello


I am needing some help or ideas of how to easily transform original data into a simplified list, ideally just a pivot, but because my original data has tons of columns and due to the circumstances.. I am giving up hope that there is not an easier way to do this..


Background:


1. It is a list of contracts.
2. There can be multiple rows for 1 contract.
3. I need to manually assign necessary text category or categories for each contract. This column is something I manually add in and can be edited, but ideally there is only 1 column for category

Simplified table of original data:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]contract[/TD]
[TD]amount[/TD]
[TD]category[/TD]
[/TR]
[TR]
[TD]contract 1[/TD]
[TD]10000[/TD]
[TD]cat 1[/TD]
[/TR]
[TR]
[TD]contract 1[/TD]
[TD]10000[/TD]
[TD]cat 1[/TD]
[/TR]
[TR]
[TD]contract 1[/TD]
[TD]10000[/TD]
[TD]cat 1[/TD]
[/TR]
[TR]
[TD]contract 2[/TD]
[TD]2000[/TD]
[TD]cat 1,cat 4, cat 8[/TD]
[/TR]
[TR]
[TD]contract 2[/TD]
[TD]2000[/TD]
[TD]cat 1, cat 4, cat 8[/TD]
[/TR]
[TR]
[TD]contract 3[/TD]
[TD]900000[/TD]
[TD]cat 8[/TD]
[/TR]
[TR]
[TD]contract 3[/TD]
[TD]900000[/TD]
[TD]cat 8[/TD]
[/TR]
[TR]
[TD]contract 4[/TD]
[TD]50[/TD]
[TD]cat 3[/TD]
[/TR]
</tbody>[/TABLE]




I need a final list of the category type and count

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]list[/TD]
[TD]count[/TD]
[/TR]
[TR]
[TD]cat 1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]cat 2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]cat 3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]cat 4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]cat 5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]cat 6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]cat 7[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]cat 8[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]




if there is more background info i can provide. the data i am working with is pretty large so i cannot post original data.