I have data in the format below where each transaction per ID is captured. I am trying to get a count of the transactions an ID was in prior to being rejected.

So for XYZ it would be:
Acknowledge Receipt – 1
Pending Approval – 1

And overall the counts would look like:
Acknowledge Receipt – 1
Pending Approval – 3
Approve Request – 1

I’ve tried adding a rank to offset the data but it’s made complicated by ID’s that have been rejected multiple times. Then adding on to that, I also wanted to see how long something sat in a rejection status prior to moving to the next step, or if it’s still in rejection status. But I wanted to focus on one problem first. I’m a little stumped by how to solve this.

enter image description here