Set values in a column based on a "master row"?

ljb18

We are a music library company that makes a large amount of our income from the sum of tons of small royalties. Pretty much the only information that comes in from the royalty reports is the track title and amount earned, and even if it does have other information it's not unified among all the different royalty sources and is generally unhelpful.

We have a metadata master sheet, which has information like album name, composer info, key words, instrumentation, etc. All of these analytics would be extremely helpful to us, but it's missing from the royalty reports, which is what actually has the income earned. What I'd like to do is somehow figure out a way to assign values from the metadata to the information coming from the royalty reports based on track title. For example, if the track title is "Howdy Partner" in the metadata and "Howdy Partner" in the royalties, I'd like to somehow assign keywords from the 'Howdy Partner' row in the metadata to the royalty reports. My problem is that I can't figure out a way to assign values based off of rows; I only know how to assign values from one column to another.

For example, the 'case' formula hasn't helped me, because I can only assign values of another column, and for the royalty information those columns are all blank and don't have the information. Could anyone help me figure out how to do this or if it's possible?

RobSomers

It sounds like you're needing to do a join between the royalty reports the metadata. You can use Magic ETL and the Join tile, you can use a left join to attach the metadata to the royalty reports where the track title from the royalty sheet equals the track title from the metadata sheet (assuming track titles are unique). This will just look through your royalty sheet and for each title on it, it will look through the metadata and attach the information from the row with the same track on it.