I have a large data set (???) that needs tidying before being 'uploaded' to stats program.
As this data set will be updated on a bi annual basis, I am looking for a solution to a main data tidying exercise.
In one field of data, names of authors, there can be a single author or up to 25 authors in a single cell.
What I need to do is to insert duplicate rows for each of these authors. That is if there are 10 authors, I need to insert 9 rows BUT each row will only contain one name.
My solution for this round was to insert rows and then manually remove names so that each author had one row.
(All other data is repeated for each author)
In addition, where there is an * after the name I need to add Int to column D and where there is a # I need to add Ext to column D
See attached example
Any ideas or thoughts would be much appreciated as I don't necessarily want to be copying and pasting and deleting for two weeks again to get data in usable format.