over consolidated data - need dissection and structure help!

I am trying to clean up a report that has a couple of flaws - unfortunately I don't have the luxury of adjusting my data source. I pull this report on a bi-weekly basis, and need to develop a macro that can be run on a report with a growing row count.

My data has 9 columns, but only 6 of them will always have content. 1 of the 3 "Optional" columns is for a team name - which can contain 0-7 comma separated team names (this is a problem).

Can someone help to create new unique row for each individual team that is listed in that combined cell so it can be analyzed. The rest of the row can be duplicated.

Sample Sheet.xlsx

Green = required field & will always have content
Red = not required

I use both Mac & PC, and have nearly zero VBA experience, but won't turn down the chance to learn something useful.

I've attached a sample sheet of what the content looks like right now, and a sheet of how I would like it to look - thanks in advance!

try this: (Alt-F8 and then run SplitTeams)
Sample Sheet.xlsm

try this:
Sample Sheet.xlsm

add just before End Sub: Range("A1").Select

tomorrow I will try to provide you with a quick and dirty explanation

this is the code with the explanation. I hope it makes sense and clarifies some of your questions

Please Login or Register  to view this content.