Calculations based of Keys of different data sets

[ISW]

Hi all. I was given a task to do at my new job and it is a little out of my knowledge base for the time being. I am halfway through my VBA book but it's just going to take some time.

Anyway, In the picture below I need to use the info from Report 1 and Report 2 to make report 3. I am going to do this on a large scale in the future and I am hoping to not have to do it manually.

Is there a way to do this with just Excel formulas or is it only possible with a Macro? Either for an answer will work. I am just better with the straight formulas at this point.

Basically the only field I am really concerned about is populating "Units". Although, if the others are included in your response I will take that too.

 

[shadow12345]

so the formula for report 3 would need to do something like this

Find total units for CP in report one
Find allocation % in report two for the FG
Divide the result.

is that right?

 

[ISW]

Close. Multiply Units in report one with % Allocation in report 2.

 

[shadow12345]

This formula in f26 would do that.

=VLOOKUP(C26,$C$12:$E$19,3)*VLOOKUP(MID(C26,1,1),$B$5:$C$7,2,FALSE)

then just copy it down

*I am assuming that FG first letter is always the same as the CP code

 

[ISW]

Wow, that was fast. I just about went blind today trying to figure this one out. Now, on to studying what you did there.

Thank you!

 

[shadow12345]

Formulas i can do.... VBA is still a little ropy at times.

If it helps, split it like this.

=VLOOKUP(C26,$C$12:$E$19,3)
c26 is what we are looking for
$C$12:$E$19 is where we are looking
3 is how many columns left we are going to return the value of

* times by what comes next

VLOOKUP(MID(C26,1,1),$B$5:$C$7,2,FALSE)

This vlookup works the same just this bit has been added
MID(C26,1,1)
This mean, look at c26, start at the first character, and show me the first character


That it really, but good luck with it.
*ps It help to make the formula in seperate cells so you can see each result.

 

[ISW]

"*ps It help to make the formula in seperate cells so you can see each result."

Great piece of advice I will rmbr.