Connect unrelated values

[#1]

Hello,

I have two main data sets: one reporting historical sales and the other reporting sales forecast. I want to take the Customers with some kind of Sales forecast and associate them with potential Partners who could work on the lead and close the deal. Whilst this is easy to do with Customers that have made purchases in the past as I can assign the lead to Partners that have a 'history' with them, I'm kind of stuck with new Customers because I cannot make any association based on history.

What I need to produce is a pivot for the new Customers showing the Partners that have made a sale in the past related to the Product that the Customer is looking to buy - I've placed an image in the second tab to give an idea of the table I'm trying to build.

Do you think there is a way to achieve this?

Thanks,

Linda

[#2]

May be you need to create 2 fields, existing Partner and History Partners for each Customer. Then use History Partners in the Pivot Table,

[#3]

Hi,

Thanks.

I've considered creating new fields but am working on a very complex script and the actual app is already quite heavy. Also that would create problems in all other associations I need to make, which are not shown in the sample.

Linda