Dataset reshape- restructure.

Hello guys,

These are my variables:
GranteeID
Funding= Funding type there are 4 funding types (a,b,c,d)
ProviderID=provider of the services
Services= type of services (there can be 5 different type of services).

In this dataset the services is the unit of analysis- that is one row per service. Basically, I want the provider to be the unit of analysis so one provider per row. However, this can be tricky since a provider provide more than one service, can be ralted to more than one grantee and can be funded for more one funding part (a,b,c,d). Providers needs to be associate to only one grantee. We chose the grantee based on a hierarchy. This is how my final dataset should look like.

Provider: only one provider per line
GranteeMain: This grantee should be chosen based on the hierarchy.
FundingMain: This funding type should be the funding type of the grantee selected.
Funding A: YEs or no
Funding B: YEs or no
Funding C: YEs or no
Funding D: YEs or no
Service1 : YEs or no
Service2 : YEs or no
Service3: YEs or no
Service4: YEs or no
Service5 : YEs or no

The grantee_main hierarchy is as follow. If providerID equals granteesID this grantee should be chosen as grantee_main. Follow by, grantees that have funding part C, D, B, A in that order. For example, for provider 33, grantee 33 should be chosen as grantee_main. For provider 88 grantee 80 should be chosen as grantee_main since C comes before A in the hierarchy.

GranteeID Funding ProviderID Service
33 Part A 33 1
33 Part A 33 2
78 Part B 33 2
14 Part A 82 3
80 Part B 82 4
80 Part C 82 5
80 Part B 82 5
80 Part B 82 2
80 Part B 82 3


I really need some help with this. I am think and rethinking how to do this but I cant figure a way. Hope somebody can help me. I am attching my actual dataset as well as the out I would like to see (tab3)

Best,
Marvin