Portfolio to Individual relationship and/or dependencies

Hello Excel experts,

I don't have a sample for this, as I do not know how to create it.

Basically, I have list of portfolio, say PF1, PF2, PF3, ..., PF10 and I have list of individuals who are assigned to the portfolio, say David, Mark and John.

The biggest hurdle of this project is that portfolio can be assign to any individual at any given time.

For example, INITIALLY portfolio 1 (PF1) was assigned to David on Jan 01,2015. On Jun 10, 2015, for whatever reason, PF1 is now assigned to Mark. And in the future months, say Dec 18, 2015, it was assigned BACK to David again.

Initially, I thought of having the portfolio going down the row, and the names of individuals going across the columns, with TRUE or FALSE assign to each of them. But given the 'volatility' the portfolio being assigned to an individual, AND the chances that it'll be assigned to the same individual again in the future months, makes my logic and creativity out of the window.

So would anyone have any advice on how to handle this? For example, how many tables do I need to have? What formula would I be using to determine their relationship at a given point in time.

I hope I am clear enough to display my problem, if you have any question, do not hesitate to ask. After all, I am asking for help, and you're trying to help me.

Thank you in advanced!

Thanks for the reply shirleyxls.

I'll need to see if your suggestion will work with what I'll need to corporate to the 'dashboard'

By the look of it, yours would be the solution, although the pivot table may grow very long, depending on how many 'transactions' there'll be.

Thanks once again! And reps up to you for helping out.

Also, will have this closed as solved if I didn't hear any feedback from others within 2 days.