If you adjust your data so that there are always 5 lines per record, as per the first 605 rows in the attached, the formulae on Sheet2 will do the job without the need for a macro. Simply copy the forumlae on Sheet2 down as far as you'll need (about another 250 rows).
If you look at Sheet 1, you'll see that '[1402121537] Leonard J Chabert Medical Center Program' has only 4 data rows. On Sheet2, the entry after that has 'VALUE#' in column A. Insert a line above '[1402122146] Ochsner Clinic Foundation Program' on Sheet1 and the Sheet2 output will be healed (the formulae in columns E & F adjust the output for 3 & 4 line addresses). Continue as necessary until all records on Sheet1 have 5 rows.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
|