I have a spreadsheet with a large # of rows. Individual (unique) account numbers are in column A and multiple lines of notes pertaining to the accounts are in column AW (column 49)
The typical note in a cell in column AW will contain between 5-20 lines of text separated by carriage returns Chr(13) + new line Chr(10).
What I would like to do is insert a new row in the spreadsheet for each line of text in the note in column AQ while repeating/copying the account# to the new rows.
Two rows of current data looks like:forum.ozgrid.com/index.php?attachment/49491/
Column A ......Column AW
#12345678 1234 Main St
..........789 High St.
..........147 Simon St
#9876543..2321 Shuster St.
..........246 Bow Street
..........1111 Hunter Lane
Note: the note lines (addresses) in the examples are in a single cell (Column AW)
Desired output looks like:
Column A .....Column AW
#12345678 1234 Main st
#12345678 789 High St.
#98765432 147 Simon St
#98765432 321 Shuster St.
#98765432 246 Bow Street
#98765432 1111 Hunter Lane
I would greatly appreciate any assistance in getting this done. I have attached a sample spreadsheet as well.