Hello all,
I have a hard time figuring out the following, and I was hoping that one of you might be able to help me out.
I have two workbooks (but I could place them in a sheet if that will be easier), they both contain a lot of data (about 21500 rows).
I am trying to combine both of these workbooks (/sheets) into one.
They are set up like this.
Workbook 1:
Film_______ Starring_______
Book of Eli__ mabcd, mdefg, mhijklm
Avatar_____ ertg, ujnkl, mabdd
Friends_____ qwer, uiopp
Workbook 2:
Actor_____ FilmsID_____
Johnny____ mabcd, ertg
Madonna__ qwer, mdefg, ujnkl
Bella______ uiopp, mhijklm
I hope your seeing the logic here. Workbook one contains all films with and ID of actors that are starring in that movie. Workbook two contains all the actors, with an ID that matches up with the movie they play in.
Thus in the above example:
Johnny would be in the Book of Eli, and Avatar.
Madonna would be in Friends, the Book of Eli, and Avatar.
Bella would be in Friends, and the Book of Eli.
This is thus how it should look like:
Film_______ Starring_______
Book of Eli__ Johnny, Madonna, Bella
Avatar_____ Johnny, Madonna, mabdd
Friends_____ Madonna, Bella
I hope this makes sense.
There are thus multiple actors/ID's in one column, but I could split these with "Text to Columns" if this will make it easier to solve.
I am not really sure where to start, I been playing around with INDEX, MATCH, and LOOKUP, but I can't seem to figure it out.
Also, maybe acces (with SQL statements) might be a better solution here? Any thoughts on that?
Thanks in advanced, I appreciated all the help.
Bookmarks