Comparison challenge!

[#1]

Hi all,

This has been really bugging me and I would really welcome some advice on how to get going with this one. I've tried sets and alternate states and although alternate states gets me close I cant seem to crack it. So advice welcome....

I have a table that looks a little like this :

Compound	Test	Result
A	1	Pos
A	2	Neg
A	3	Pos
A	4	Neg
B	1	Pos
B	3	Pos
B	5	Pos
B	6	Pos
B	7	Neg
C	1	Neg
C	3	Pos
C	7	Neg
D	3	Pos

And what I am trying to do is construct an obect that, when one compound is selected (say A) the object shows the list of the other most similar compounds based on their results in corresponding tests (in this case B first (matches in test 1 and 3 then C and D -they match in test 3) are all positive in . Now not all compounds are tested in all tests. In the first instance I would like to just base the ranking on the positive test results only. But I would like a method that would allow me to rank on the actual test values (there in another column that has a 0-100 value if positive too)

Any ideas?

Thanks

Mark

[Gysbert_Wassenaar · Accepted solution]

See attached example. Perhaps you can use this approach in your setting.

[#3]

Hi Gysbert,

Thanks and so simple - I was trying to make things too complicated.

Moving on to my second question, comparing on the actual value? lets say a positive test was "most strongly positive" at 0.1 and "most weakly positive" at 100 and I bin my compounds by 0-1, 1-10, 10-100. I guess i could use the same approach with a binning step on the load of the initial table.

Cool - thanks for your help!

Mark