Create a row for each word?

[elbrujo]

Hi there dear forumers,

I've been trying to apply other Macros, unfortunately I've been unable to achieve what I need, I have a table like this:

Category	Name	Country	ID	Other Number	Year	Type	Lang	Type
Something; another thing; this-other thing	A Name	USA	1	Q1	2011	Random	English	Random 2
Another category; something else	Another Name	USA	2	Q2	2012	Random	English	Random 2
	Other Name	Spain	3	Q2	2012	Random	Spanish	Random 2
Last one; empty one; etc	This Name	Belgium	4	Q3	2013	Random	English	Random 2

And I need each item inside Category (which are separated by semicolons), to be on its own row, but keeping its ID, its Country, its Language, its Type, etc!... All of them. With empty cells inside Category, then do nothing...

To look like this:

Category	Name	Country	ID	Other Number	Year	Type	Lang	Type
Something	A Name	USA	1	Q1	2011	Random	English	Random 2
another thing	A Name	USA	1	Q1	2011	Random	English	Random 2
this-other thing	A Name	USA	1	Q1	2011	Random	English	Random 2
Another category	Another Name	USA	2	Q2	2012	Random	English	Random 2
something else	Another Name	USA	2	Q2	2012	Random	English	Random 2
	Other Name	Spain	3	Q2	2012	Random	Spanish	Random 2
Last one	This Name	Belgium	4	Q3	2013	Random	English	Random 2
Empty one	This Name	Belgium	4	Q3	2013	Random	English	Random 2
etc	This Name	Belgium	4	Q3	2013	Random	English	Random 2

How can I achieve this?
I've attached a demo file containing the actual info that would have to be modified.

Thank you very much for your time,
Regards.

[xladept]

Hi Elbrujo,

This is slow and I'm working on speeding it up - but, for now:

Please Login or Register  to view this content.

[elbrujo]

Thank you very much @xladept, I highly appreciate your help! It is indeed working, taking some moments, but doing what I need

Edit:
Nevermind, I could fix that by using this macro
http://www.extendoffice.com/document...rst-space.html

, but checking the results I can see there's a blank space with several rows, since it goes "a category;(space-here)another category", like "a category; another category" which is causing it to look like this:

Do you think this can be fixed?
Thank you.

[xladept]

Hi Elbrujo,

You're welcome and thanks for the rep!

I should have a quicker version tomorrow

[elbrujo]

@xladept thank you very much, I don't think I'll need a faster one right now, it is going pretty smooth, so you can do it if you have free time! ;-) if you don't, then don't worry!,
Thanks for your help!

[xladept]

Hi Elbrujo,

This is a lot quicker - I put the results on another sheet for testing purposes:

Please Login or Register  to view this content.

[elbrujo]

Thank you very much xladept!, it worked just fine! :D

[xladept]

You're welcome!