I am struggling to find the correct tool to do what I want and I feel I am overlooking something simple... I am working with census blocks and internet providers in those census blocks. I have a spreadsheet showing the census block ID number and the provider in that census block. However, in most cases there are multiple providers for each census block ID. So, I get a table with duplicate Census Block IDs in their own row but unique providers. Ideally, I would like to remove the duplicate rows, but maintain the unique provider list only in a new column instead of a row:
I would need this to be at least semi-automated since I will be dealing with 500k+ lines of data per example. If I do a many-to-one join in ArcGIS it only takes the first row. I can create multiple features with identical geometries using the 'Make Query Table' function, but this map will ultimately be used by a layperson and they want to click on a census block and have a list of providers and not cycle through all the layers.
It makes no difference to me if this step is done in Excel, ArcGIS (with ET Geowizards Plugin), QGIS, or another program that may do it best??