I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with multiple key staff names listed in the same cell, separated by line breaks (ie CHAR(10) ). There is one business per row. The number of lines in the 'key staff' cell varies row-by-row. My initial sheet looks like this:
I need to do the following to optimize this sheet:
- split each multi-line ‘key staff’ cell so each key staff name appears on its own row. This requires that new row/s be inserted beneath the original row.
- duplicate the data from all other cells on the original row (i.e. from columns A & C:E), so that each new row contains the full data for each business
- I need an automated process - I'll have about 1000 businesses to process so can't do this with any manual steps
The sheet should then look like this:
=TRANSPOSE(SPLIT(B1,CHAR(10))) obviously only goes part of the way - it doesn’t insert new rows and doesn’t duplicate the surrounding column entries. All help gratefully accepted!