by
lmh » Wed Jan 22, 2020 5:01 pm
since this has been floating around since December and no one has solved the problem, I'll bravely post a crude work-around. If anyone knows an official, proper way to do it, please, please post!
If you're not in a regulated environment, so you're OK about creating the table in Excel, there are various methods. Here's a very simple one:
(1) Copy your TargetLynx data all groups summary to a worksheet. You now have successive blocks of data, each for one compound, with rows for each sample.
(2) Add, somewhere to the right, a formula that looks to see if the current row has a compound name in column A, and if it does, picks it up; if it doesn't, it picks up the row above. This, taken from cell O9, will do the job:
=IF(LEFT(A9, 4) = "Comp", A9, O8)
You now have a compound name on every single row of your table.
(3) Now add something concatenating the compound name with the sample name. For example, in P9 we have:
=O9&D9
(My sample names happened to be in column D; this depends on the layout of columns you've chosen in TargetLynx).
(4) Set up a summary table with the sample names in a column and the rows headed by the compound names. If you want to use a simplified form ("Caffeine") rather than TargetLynx's long-winded "Compound 1: Caffeine", then sometimes it's easiest to have a second, hidden row with the full versions.
(5) For convenience, select the whole block of raw data and give it the name "all_data", and select the column in which you've constructed a unique compound-name-sample-name key "all_keys". You don't have to do this, but it saves you selecting the ranges and adding $-signs in the next step.
(6) In the whole block of the table, enter a formula such as the following:
=INDEX(all_data, MATCH(C$6&$B10, all_keys, 0), 12)
Column B contains the sample names, and Row 6 contains the compound names in TargetLynx's format. C$6&B$10 therefore creates a concatenation identical to the keys you made in the raw data at stage (3). The dollar signs mean that as you copy it to fill the whole block, the key is always built from the correct column and row. "MATCH" finds the row-number of the key for the correct combination of compound and sample. "INDEX" returns data from that row, from column 12 in this case. Change "12" to indicate which column contains your final results.
This procedure breaks down if you run the same sample multiple times (because it will have the same key every time; you'd need to add something to the key or the sample name to reflect which replicate run it is).
The procedure does, however, cope with bracketing, because it will keep looking until it finds the sample name, whatever bracket it's in.
