Create additional rows based on a comma separated field?

[AaronKoelker]

I have a table of proposals which includes a field for counties that the proposal will affect. Unfortunately, the COUNTY field has multiple counties in it, separated by a comma. (Pic is from an excel attempt).

What I want to do is find a way to create a duplicate rows so that each only has one county in the county field. (The end result is that I need to have a map of counties that will be symbolized by the number of proposals affecting them.)

So the highlighted Proposal 4 above would turn into something like this:

 

Then I can join to a county shapefile and easily get a count. It's simple enough in Excel to split a cell into columns and then transpose them into rows, however I need to pull the additional data from the other fields as well. There are 1500 rows, so I need an automated way to do this either in ArcMap or through Excel. 

Or perhaps I'm going about this all wrong and there is a way to relate the table to a county shapefile based on a comma separated field?

Any help would be appreciated!

[DanPatterson_Retired]

tough one, but if your intent is to get summaries and the like, you might want to consider that queries can parse substrings from a string.  Consider your Counties field, if you wanted to get all the records that are associated with Bay county, your query would be 

Counties Like '%Bay%'‍

or variants such as

Counties = 'Bay' (explicit check)

(Counties = 'Bay') Or (Counties Like '%Bay,')    (as above, but check for an immediately following comma)

Setting up queries to run through a Summarize might be easier that trying to reinvent/reformat a database structure which is being a bit stretched beyond its original purpose/vision.

[JoshuaBixby]

I thought a very similar question was recently answered on GeoNet, but I can't find it at the moment.  In the meantime, a similar question has been answered on StackExchange:  arcgis desktop - Splitting strings into new rows, retaining geometry - Geographic Information System... . 

I will post back if I find the GeoNet question I am thinking about, or if I come up with my own solution.

[AaronKoelker]

That looks about what I'm trying to do. When I get a chance to try it out, I will post an update here. Thanks!