I'm trying to figure out how to arrange a dataset I have. Based on the problem, I think that the transpose or cross tab tools (or possibly a combination of both) may provide the solution, but I'm not entirely sure. Here is a sample :
| Name | Type | Province | Reports to | System |
| AIA | Office | BC | Abbot | ICS |
| AIA | Office | | Surrey | ICES |
| AIA | | BC | Pac | GCMS |
| Herd | Port | NL | | ORA |
I'm trying to rearrange the data so that any common entries in the 'Name' column will be collapsed into a single row. Whatever exists in the other columns for the common entries in the 'Name' field would be combined in the row under that column with some sort of delimiter. I'm thinking it would look something like this:
| Name | Type | Province | Reports to | System |
| AIA | Office, Office, | BC, ,BC | Abbot,Surrey,Pac | ICS, ICES, GCMS |
| Herd | Port | NL | | ORA |
