Thanks Chris, this seems like a good idea. Would you aid me in how to do this please? At the moment the model I created above works with automation process. So a secondary Model can be created to link this data with an external Lookup?
How would I go about doing this in automator?
Many thanks for your help
Another way would be to use Data Prep Studio of Monarch Complete.
Assuming that your sample data is one group, we can open it in Data Prep Studio and pivot on AssayName and ConcentrationRaw as the value. Then group by LotNumber. Once this is set we can then combine with the original data set and you can now create a calculated field using KFree and LFree.
Hope this helps.
Am I correct to assume that the rows are really multiple data lines (and therefore fields in effect) within the same record - Lot Number?
If so one might trap them as such - but as I recall your "reports" can present with some specific challenges that might make that tricky.
In Monarch Classic the parallel approach to Mela's Data Prep Studio approach (assuming that all records are NOT in the same "record" once they have been extracted) would be to "Pivot" using a Summary. That could get your calculation but you would then need to Export the result and run a lookup to a new calculated field ("Concentration Processed"?) to bring the result into the table.
If, however, all of the value fields are in the same row as a single Detail record you can calculate away with them as much as you like!
If the data in your table is starting out that as you show it and not in a "report" style then the Pivoting, by whichever means you prefer, and a subsequent lookup is as good an approach as any to a "2 stage" process.
An alternative would be to move the columns in the table around a bit and create a "report" file as text. Then output to a text file which you can read in as a "Report" and so organise things to get all of the fields into a single record by Lot Number. Once in that for the model is very simple and you can calculate directly in the table just as you are currently trying to do.
That may sound rather complicated and roundabout as a method but in fact it should be very easy and result in simple and easy to maintain process.
Chris's suggestion follows the same principle. Whether you choose to output to an Excel file (actually if it is just for lookup purposes you might find an Access file type better in order to avoid the sometimes unusual results one can get with Excel versions) or a flat text file, probably doesn't make a lot of difference. Go with what you feel is the most appropriate approach.