Text to value in column

[cramnij]

Hi, my name is Marc and I hope somebody can help me creating a VBA for my problem. I have included a file with the problem.
See the Sheet Example.
So I have a Sheet with 2 columns the first column contains the movie_id number, the second column contains the corresponding genres of the movie as text in a single cell that is separated by a comma (,).
Both columns always have the same length but the length of both columns, in this case row 119, is a variable.
The text, separated by a comma, in the second column corresponds with the text in the sheet tbl_genres. Every text part is corresponding with a value (genre_id) in the Sheet tbl_genre. For example: action = 1; adventure =2; animation=3.

So the first row in the example:
4029 action, drama, history, thriller, war

can be translated to:
4029 1,8,12,18,19

But in fact every genre_id in that row corresponds with the same movie_id number. So for the first row, 4 new rows (total of 5 rows for the corresponding 5 genre_id’s) must be created to make a relation between the movie_id and the 5 genre's.
4029 1
4029 8
4029 12
4029 18
4029 19

Sometimes a cell in the second column is empty (no corresponding text/genre), In that case the row (and corresponding movie_id number) must be deleted.

In the example file I have included the sheet Result with the result for the first 7 movie_id numbers.
Hope my explanation is clear, if not feel free to contact me so I can explain some more.
Thanks in advance for all your help.
Marc

[gfranco]

Hello.
See if this is what you want:

Please Login or Register  to view this content.

[mjr veverka]

And another variant ...

Please Login or Register  to view this content.

[Kenneth Hobson]

Always test in a backup copy but especially so for overwrite macros like this.

Please Login or Register  to view this content.

[cramnij]

Gfranco,
Your the BEST!
Exactly what i wanted!
Thanks so much!!

porucha vevrku and Kenneth Hobson also thanks for your work, haven't tested this yet, but it's much apreciated!
Marc