cut and paste calculation

Hey everyone, i'm new to the forum and i have an interesting problem that i'm hoping to get a little help with! So basically i have an excel file with multiple sheets. I have all of this information in excel sheets because i am transferring the information from one database to another. I have some information in one column of my "ingredients" sheet that i want cut and copied into another column of my "compound" sheet. There is an ID number in the "compound" sheet that links each rows information to a row in the "ingredients" sheet. So based on these ID numbers i want the information from the column in the "ingredient" sheet to be cut and copied into a column of the "compound" sheet. Also, as i don't need all cells of the column to be cut out i have the ones i need marked with red font in order to filter them.

If my description of the problem isn't clear i will gladly clarify any ambiguities.

I greatly appreciate any help or advice!

Hey dilipandey, thanks for the reply!

Due to the confidential nature of the information i cannot post the exact excel file i'm working on, but i've created a very simple example file that demonstrates what i need help with. What i'm looking to do is cut out Ingredients #2,4,9 and paste them into the "notes" column based on their CompoundID. So for example this means that Ingredient #2 would be pasted into the notes column of compound A because they share the common CompoundID #1.

Hopefully the example file helps clarify what i'm looking for and i appreciate any further advice!

cheers

DiliPandey, It has nothing to do with the "amounts" "strengths" Quantity of prepare" columns, rather i was hoping that marking ingredient #2,4,9 with red would be enough for a filtered portal to separate those fields out so i could selectively apply a calculation to cut and paste those to the notes section of their parent compound. If this will not work, i appreciate any other options that will accomplish what i'm looking to do.

cheers

Thanks for your responses ChemistB & DiliPandey! The formula provided by Dilipandey did the trick! The other problem this leaves me with is as i have to import this information back into another database it doesn't do well with #N/A values in any of the cells. As i applied that formula to the "notes" column in the compound sheet it leaves the rest of the cells with this #N/A value. Is there a way for me to add a modifier into the array formula that will cause the cells that aren't being references to be left blank instead of displaying the #N/A value?

Thank you for your continued support!

cheers

You guys are awesome, thank you so much for your help!