splitting multi-line cell into newly created rows

[tranzsport]

I am working on an excel spreadsheet that has data in 17 columns. Some of these columns, say column AJ, contain multiple dates on each line (ALT-Enter) within the cell. Suppose cell A and B contain only one entry, like name and phone number for a person, which are attributed to all lines in AJ.

In other words, I want the code to do the following:

ttt.jpg

Please note that the number of lines (dates) differ across the data so the code needs to figure out how many dates there are and create new rows accordingly.

Any help is greatly appreciated!

[natefarm]

You say some of these columns contain multiple entries. How many, and which ones? Your request could be accomplished if applied to one column, but with a random number up to 17, it gets tricky, especially if more than one cell in a single row has multiple entries. For example, if column X has 2, Y has 5, and Z has 10, then I assume you would want that row expanded to 10 rows, but what would you do with the data in columns X and Y?

[tranzsport]

Thank you natefarm for the quick reply.
Apologies, I should have been more elaborate in my explanation. The number of columns that needs this kind of treatment is fixed (3 columns out of the 17) and I do not mind running the code one column at a time. Each row will contain the same number of entries in the cells that need to be treated so I will not run into the issue you mentioned with a single row having columns that contain different number of entries per cell.
To illustrate, I attached a sample file of what I am talking about.
Columns A, B, and H have cells that need to be split, with the other columns basically replicating their cell entries in the newly created cells. The entries on each line map 1-to-1 across columns A,B and H. Notice how each row does not have a varying number lines.
I hope this makes the problem more manageable.

Thanks again natefarm.

[nilem]

Hi tranzsport,
try it

Please Login or Register  to view this content.

[tranzsport]

Excellent nilem!
Thanks! It worked on the sample file..
can you kindly (very quickly) which part of the code I need to tweak as it seems to break if I had additional columns or if my treatment columns were in different positions/arrangement.
I basically would like to know what to tweak in the code to make it fit a different arrangement or number of treatment columns.
again, thanks! you are awesome

[nilem]

Please Login or Register  to view this content.

[tranzsport]

that did the trick!
Thanks, much appreciated..