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:

contract	amount	category
contract 1	10000	cat 1
contract 1	10000	cat 1
contract 1	10000	cat 1
contract 2	2000	cat 1,cat 4, cat 8
contract 2	2000	cat 1, cat 4, cat 8
contract 3	900000	cat 8
contract 3	900000	cat 8
contract 4	50	cat 3

<tbody>
</tbody>

I need a final list of the category type and count

list	count
cat 1	2
cat 2	0
cat 3	1
cat 4	1
cat 5	0
cat 6	0
cat 7	0
cat 8	2

<tbody>
</tbody>

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