Compare two sets of data and join together

I have two data sheets that I need to compare. Sheet one contains agent IDs and all their customers for each state. The second sheet contains the registrations the agent holds for each state (registrations can be at the city level, state level, or no registration at all)

The first step is to consolidate the first data sheet. For example, there may be 10 line items for one agent. Of the 10 line items, 4 customers reside in NY, 3 reside in NJ, and 3 reside in CT. This would be consolidated into 3 line items, which would be agent ID, agent name, the state, and number of clients in each state:

Agent ID.....Name......State......Clients
123456.......Joe............NY.............4
123456.......Joe............NJ.............3
123456.......Joe............CT.............3

The second, and last step, is to determine the registration level the agent has for each state, if any. So the final output may be as follows:

Agent ID.....Name......State......Clients.........Registration
123456.......Joe............NY.............4...................STATE
123456.......Joe............NJ.............3....................CITY
123456.......Joe............CT.............3....................CITY

Attached is sample data along with desired output. Thanks for any comments.